Both GET_VAR and PREP_PROCESS_VAR_XML allow one read at subset of data from XML VARA object by specifying an Xpath or XQuery expression.
I have discovered that not all valid Xpath expressions will work in these commands. The reason is related to how the expression is inserted into the SQL statement before being executed against the DB.
In the examples below, I used GET_VAR.
This Xpath expression should generate a semicolon-delimited list of attribute names and values.
U00003758 Error in xml-variable:'U00003590 UCUDB - DB error: 'OCIStmtExecute', 'ERROR ', '','ORA-31013: Invalid XPATH expression'
SELECT T.X OVW_VALUEXFROM OVW ,XMLTABLE ('//@*/concat(name(.),'=',.)' passing OVW_ValueX COLUMNS X varchar2(4000) PATH '.') AS TWHERE OVW_OH_Idnr = ?AND OVW_VValue = ?
(Oracle SQL; Newline characters & leading spaces added to improve readability.)
A few things to note here:
I was able to get this particular expression working by replacing the single quotation mark characters in the expression with double quotation mark characters. I.e.,
This worked fine, but stopped working again after I upgraded the system.
Newer versions of the Automation Engine use a different SQL statement for GET_VAR:
select * from (SELECT to_clob(extract(OVW_ValueX,'//@*/concat(name(.),";",.)')) OVW_VALUEX, OVW_OH_Idnr, OVW_VValue, OVW_OH_Client, OVW_VRNameFROM OVWWHERE OVW_OH_Idnr = ?AND OVW_VValue = ? )where rownum < ?
This query should eliminate the aforementioned problem with large XML.
From the trace, I can see that the SQL is being built in multiple stages:
My testing revealed that it’s easy to mess up the resultant SQL by manipulating the Xpath expression. This causes errors like these:
U00003590 UCUDB - DB error: 'OCIStmtExecute', 'ERROR ', '', 'ORA-31013: Invalid XPATH expression'U00003590 UCUDB - DB error: 'OCIBindByPos', 'ERROR ', '', 'ORA-01036: illegal variable name/number'UCUDB - DB error: 'OCIStmtExecute', 'ERROR ', '', 'ORA-00936: missing expression'
Moreover, a great number of valid Xpath expressions simply do not work. I opened a case with CA support about this, but they simply told me that it depends on the back-end database, and that it’s up to me to find a way to get the Xpath expressions to work.
I have not tested whether SQL injection is possible. It is not.
I did a quick test of SQL injection, and the replacement was rejected.
U00029108 UCUDB: SQL_ERROR Database handles DB-HENV: c095c0 DB-HDBC: c7f3f8U00003591 UCUDB - DB error info: OPC: 'OCIBindByPos' Return code: 'ERROR'U00003592 UCUDB - Status: '' Native error: '1036' Msg: 'ORA-01036: illegal variable name/number'
This is good, obviously.