Insert or update using dbms_xmlsave [message #186953] |
Thu, 10 August 2006 05:13 |
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 |
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 |
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 |
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 #422554 is a reply to message #186953] |
Thu, 17 September 2009 07:04 |
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.
|
|
|