Q

Rolling back Oracle XSU

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?

I have a question regarding the use of the folowing methods:
  • 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?

It looks like you're using the Java API. IT should work the same as the PL/SQL package DBMS_XMLSAVE which is what I use.

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 18
If you aren't getting your exceptions propagated, you might want to check your code for a null exception handler.
This was first published in July 2005

Dig deeper on Oracle Java and J2EE

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close