- OracleXMLSave.insertXML(String xml)
- OracleXMLSave.updateXML(String xml)
In the API those methods only return the number of rows (represented by an int) that have been "touched." Some...
of the Oracle XSU that I create contains several inserts/updates and I would like the opportunity to rollback if just one of those inserts/updates fails, but this seems impossible as I only get the aforementioned int and not exceptions thrown within the API. Do you have a solution to this problem?
When I use DBMS_XMLSAVE, I do get any exceptions that are raised. Here is a sample that shows that:
SQL> declare 2 3 insCtx DBMS_XMLSave.ctxType; 4 rows number; 5 queryCtx DBMS_XMLQuery.ctxType; 6 result CLOB; 7 8 begin 9 10 queryCtx := DBMS_XMLQuery.newContext('select * from emp'); 11 12 DBMS_XMLQuery.setRowTag(queryCtx,'ROW'); 13 DBMS_XMLQuery.setRowSetTag(queryCtx,'ROWSET'); 14 15 result := DBMS_XMLQuery.getXML(queryCtx); 16 17 insCtx := DBMS_XMLSave.newContext('emp'); 18 rows := DBMS_XMLSave.insertXML(insCtx, result ); 19 DBMS_XMLSave.closeContext(insCtx); 20 end; 21 / declare * ERROR at line 1: ORA-29532: Java call terminated by uncaught Java exception: oracle.xml.sql.OracleXMLSQLException: 'oracle.jdbc.driver.OracleSQLException: ORA-00001: unique constraint (SCOTT.PK_EMP) violated ' encountered during processing ROW element 14. All prior XML row changes were rolled back. in the XML document. ORA-06512: at "SYS.DBMS_XMLSAVE", line 115 ORA-06512: at line 18If you aren't getting your exceptions propagated, you might want to check your code for a null exception handler.
Related Q&A from Lewis Cunningham, Senior Oracle DBA
I need a step-by-step procedure for importing text to an Oracle database using a stored procedure.continue reading
How can I tell if a column is a NUMBER or VARCHAR in PL/SQL? Is there a function to do this?continue reading
I need to somehow log the execution time for two procedures every time they are called and save that data (in a table?). Do you know of a way to ...continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.