Home » SQL & PL/SQL » SQL & PL/SQL » Insert or update using dbms_xmlsave
Insert or update using dbms_xmlsave [message #186953] Thu, 10 August 2006 05:13 Go to next message
dw_mmr
Messages: 12
Registered: July 2006
Junior Member
Hi ,

is there anyway I can do "insert or update" data from XML file to oracle table?

I tried DBMS_XMLSAVE.insertxml ( which inserts records but throws error if file has data which is present in the table);

DBMS_XMLSAVE.updatexml - updates existing records but does not insert new records..

Thanks
Re: Insert or update using dbms_xmlsave [message #187043 is a reply to message #186953] Thu, 10 August 2006 11:21 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
I don't think this can be done in a single operation [Link], but you can use this sort of upsert logic:

1. Call dbms_xmlsave.updatexml.
2. If return value of the above = 0 [=> no rows updated] then call dbms_xmlsave.insertxml.

Re: Insert or update using dbms_xmlsave [message #187126 is a reply to message #186953] Fri, 11 August 2006 00:55 Go to previous messageGo to next message
dw_mmr
Messages: 12
Registered: July 2006
Junior Member
HI hobbes ,

I dint quite get it,
I have a single xml file which contains
both new records and records that have to be updated.

Now step 2
2. If return value of the above = 0 [=> no rows updated]
will always give me some rows updated.

How do I capture which rows to update and which to insert?

thanks


Re: Insert or update using dbms_xmlsave [message #187224 is a reply to message #186953] Fri, 11 August 2006 06:40 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
I had thought that the input XML would either be for insert or update, not a combination. As that is not the case, this approach might work for you:

1. Call dbms_xmlsave.insertxml with an exception handler for DUP_VAL_ON_INDEX that ignores this error. (Use of dbms_xmlsave.propagateOriginalException should help you trap the underlying Oracle error wrapped within the 'ORA-29532 java call terminated by uncaught java exception' raised when insert fails)

2. Now call dbms_xmlsave.updatexml for the same data. This would update all records, but the ones inserted would have no change.

As you can see, there is plenty of redundant processing involved but for the lack of a merge function, one must traverse the data twice. If you can think of a more efficient way to upsert, let us know!

Can't you split your input itself so as to separate out the new and updated records?
Re: Insert or update using dbms_xmlsave [message #187228 is a reply to message #186953] Fri, 11 August 2006 07:03 Go to previous messageGo to next message
dw_mmr
Messages: 12
Registered: July 2006
Junior Member
HI Hobbes,

its working fine ,thanks Razz ...

thanks

icon7.gif  Re: Insert or update using dbms_xmlsave [message #187229 is a reply to message #186953] Fri, 11 August 2006 07:08 Go to previous messageGo to next message
dw_mmr
Messages: 12
Registered: July 2006
Junior Member
oops.. sorry for the wrong smiley..

Smile
Re: Insert or update using dbms_xmlsave [message #422554 is a reply to message #186953] Thu, 17 September 2009 07:04 Go to previous message
ezzz
Messages: 1
Registered: September 2009
Junior Member
Quote:
its working fine ,thanks Razz ...

Can you give me your code ?

Because the problem I have is that no data is insterted if an exception occured !

Here is my code :

declare 
  insCtx DBMS_XMLSave.ctxType;
  v_tablename VARCHAR2(30) := 'test' ;
  v_rows number; 
  v_record_count number; 
  java_uncaught_exception   EXCEPTION;
  PRAGMA EXCEPTION_INIT( java_uncaught_exception , -29532 );
begin
  insctx := DBMS_XMLSave.newcontext(v_tablename); 
  DBMS_XMLSave.setRowTag(insctx,'rowtest'); 
  DBMS_XMLSave.setDateFormat(insctx,'dd/MM/yyyy');
  DBMS_XMLSave.setPreserveWhitespace(insctx);
  DBMS_XMLSave.propagateoriginalexception (insCtx, true);
        v_rows := DBMS_XMLSave.insertxml(insctx,'<?xml version = ''1.0''?><test><rowtest><C1>7</C1>2<C2></C2><C3>02/08/2009</C3></rowtest><rowtest><C1>5</C1><C2>3</C2><C3>01/01/2008</C3></rowtest></test>');
  DBMS_XMLSave.closeContext(insctx);

  v_record_count := v_record_count + 1;
  dbms_output.put_line(v_rows);
  dbms_output.put_line(v_record_count);
EXCEPTION
  WHEN java_uncaught_exception THEN
  DBMS_OUTPUT.put_line (   'Error inserting to table! '
                                     || v_tablename
                                     || ' - '
                                     || SUBSTR (SQLERRM, 1, 175)
                                    );
  WHEN DUP_VAL_ON_INDEX THEN
    NULL;
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (   'Error inserting to table '
                                     || v_tablename
                                     || ' - '
                                     || SUBSTR (SQLERRM, 1, 175)
                                    );
END;


And I never go into the DUP_VAL_ON_INDEX handler whereas I used the DBMS_XMLSave.propagateoriginalexception (insCtx, true) statement...

Can anybody help ?

I'd like that if I have many rows in my XML, the rows who don't exist in database will be created, and the others will be skiped.
Previous Topic: Transpose single column into a row
Next Topic: Fetch and update a single record exclusively
Goto Forum:
  


Current Time: Sun Nov 03 01:39:47 CST 2024