Thanks a lot, I did missed the default namespace statement and your're right about the relative path of the columns... (forgot to chop the path :-( )
I have replace the file test1.xml with a file of about 28MB with about 100000 records(say casNumbers)
I have created a table with the columns
casnumber,ecnumber, molecularformula, cname, status,creationdate
I use the following plsql anohymous block
select to_date(substr(a.creationdate,1,10),'yyyy-mm-dd') as creationdate
, decode(a.status,'active',1,0) as status
creationDate varchar2(30) path '@creationDate',
status varchar2(20) path '@status',
ecnumber varchar2(20) path 'ecNumber',
casnumber varchar2(20) path 'casNumber',
molecularFormula varchar2(20) path 'molecularFormula',
namelist xmltype path 'ecNames'
XMLNamespaces(default 'http://echa.europa.eu/schemas/ecInventory'), -- added
'ecNames' -- changed
ecname varchar2(50) path '.' -- changed
for r_casnr in c0 loop
insert into csa_cas_nummers
if mod(i,1000) = 0 then
when others then
insert into foute_casnrs
dbms_output.put_line( r_casnr.casnumber||' -> '||sqlerrm);
The strange thing is that it stops without error at +/- 38000 records (when i do select count(1) from cas_nummers and no records in the table foute_casnrs)
I have check some cas_nummers which is in the xml file but not in the tcas_nummers table....
Any idea what is wrong with my code?
Thanks in advance,
How many rows does the SQL in the CURSOR return if you run it by itself?
If you remove the entire exception handling block, does it show an error?
Why are you not doing this as a simple
INSERT INTO csa_cas_nummers (...columns_list...) SELECT ...rest of cursor...;
Any idea what is wrong with my code?
A cursor loop with a COMMIT inside, maybe?
Have you tried a single INSERT SELECT with a single commit at the end?
For some reasons, if you must catch exceptions in the process, use DML Error Logging feature.
Yes I did try without a cursor loop but then I get a sql error: "cannot read more data from socket" .That is why I used a cursor loop with an exception block to try to catch the error.
When I run this on oracle11gr2 it runs without problems and I get the 100000+ records. But I need it to run on oracle10gr2
The xml table cas_nummers is created like CREATE TABLE cas_nummers OF XMLType;
I did read somewhere that it has something to do with oracle in 10g it try to read the xml in memory and in 11g not... The insert in cas_nummers is without problems. My xml file is not that big...
Yes I did try without a cursor loop but then I get a sql error: "cannot read more data from socket" .
You probably hit a bug in that version with XMLTABLE in that version. Try using TABLE(XMLSEQUENCE(EXTRACT,.........))) instead (http://www.liberidu.com/blog/2007/11/27/the-use-of-namespaces-within-xmltable-or-the-tablexmlsequenceextract-constuct/)
I did try with the following query
, extractvalue(value(b), 'ECSubstance/casNumber','xmlns="http://echa.europa.eu/schemas/ecInventory"') as casnumber
, extractvalue(value(b), 'ECSubstance/ecNumber','xmlns="http://echa.europa.eu/schemas/ecInventory"') as ecNumber
, extractvalue(value(b), 'ECSubstance/ecNames/ecName','xmlns="http://echa.europa.eu/schemas/ecInventory"') as ecName
, extractvalue(value(b), 'ECSubstance/molecularFormula','xmlns="http://echa.europa.eu/schemas/ecInventory"') as molecularformula
, to_date(substr(extractvalue(value(b), 'ECSubstance/@creationDate','xmlns="http://echa.europa.eu/schemas/ecInventory"'),1,10), 'yyyy-mm-dd') as creationDate
, decode(extractvalue(value(b), 'ECSubstance/@status','xmlns="http://echa.europa.eu/schemas/ecInventory"'), 'active',1,0) as status
from cas_nummers a
, table(xmlsequence(extract(a.object_value,'/ECSubstanceInventory/ecSubstances/ECSubstance','xmlns="http://echa.europa.eu/schemas/ecInventory"'))) b;
It succesfully insert it without socket error but not everything is inserted...., only 40672 records. It should be more than 100000 records. Query not correct?
Could be that some bits do not have a namespace reference.
By the way, before I forget, which database version do you use regarding 10gR2 (please mention all digits)
version 10.2.0.3 windows 7
No, If I do it in 11gr2 then I get all the records. It seems that 10gr2 xml implementation has some quirks and not reliable.
btw: if i do insert into table select * from xml it takes for hours but when I do it using a cursor (like the example above) it is quite fast just about 1 minute, even in 11gr2!
Is it possible you share one of your "big" test file ?
Or maybe it's OK if we extend the sample you gave to a sufficient amount of records?
Without a test case, we can't really comment on what's really going on.
In Oracle11r2 all the query inserts works (xmltable and xmlsequence) and I have the correct number of results.
I notice it is NOT the insert to the tabel is wrong but the insert in the xml table i.e. cas_numbers. The table cas_numbers was created like create table cas_numbers of xmltype;
The xml file is inserted like:
insert into cas_numbers
VALUES (XMLType(bfilename('CAS_XML', 'substances.xml'), nls_charset_id('AL32UTF8')));
if I query the to a specific casnumber then it return no rows selected. If I do it in Oracle11 then I found it. Logically the bfilename() insert is not completely correct in 10.2.0.3...
I have created a different table cas2_number(xmlcol xmltype) but it gives the same results...
Is there another way to insert xml file in table cas_numbers?
Logically the bfilename() insert is not completely correct in 10.2.0.3...
That's your interpretation.
Again, without a test case, we (well, at least I) cannot say anything for sure.
You can try to load the file in two steps to see if it makes a difference, using :
- INSERT INTO <table> VALUES ( XMLType( lob_var ) )