CODEWITHSUNDEEP
xmloracle-databaseplsql
mlwacosmos
mlwacosmos

Reputation: 4541

parsing xml file with PLSQL

I have a problem parsing one xml file in PLSQL. I want to say that I have no problem with the code with some other xml files but especially with this one. So I am waiting for some leads to what I should look at in this xml file that causes the problem.

Here is the code in PLSQL :

filename := 'name_of_my_file.xml';
myParser := DBMS_XMLPARSER.newParser;
ficContent := DBMS_XSLPROCESSOR.Read2Clob(directoryWhereToFindTheFile, filename , '0');

And here is the line where the problem occurs :

DBMS_XMLPARSER.parseBuffer(myParser,ficContent);

Oracle error :

ORA-06502 : PL/SQL : numeric or value error

Any idea about what I should look at ?

Note : I think I should add something else.

This specific xml file was written on two lines only (that caused some problems with the Read2Clob, the lines were too long). So I formatted it in an IDE to have a good xml file, and I use this one. No more problem reading the file so but this error occuring.

Upvotes: 1

Views: 792

Answers (2)

g00dy
g00dy

Reputation: 6778

Following the comments below the question:

Your code looks like this:

filename := 'name_of_my_file.xml';
myParser := DBMS_XMLPARSER.newParser;
ficContent := DBMS_XSLPROCESSOR.Read2Clob(directoryWhereToFindTheFile, filename , '0');
DBMS_XMLPARSER.parseBuffer(myParser,ficContent);

For the specification of DBMS_XSLPROCESSOR.Read2Clob [LINK]:

Syntax

DBMS_XSLPROCESSOR.READ2CLOB(
   flocation     IN   VARCHAR2,
   fname         IN   VARCHAR2,
   csid          IN   NUMBER:=0)
   RETURN CLOB;

On the other hand DBMS_XMLPARSER.parseBuffer accepts VARCHAR2 as a second argument and you're passing a CLOB there, which is OK (it gets implicitly cast to VARCHAR2), as long as it fits the size of the VARCHAR2 (which could range from 4k to 32k in bytes, depending on your DB version and configuration).

Now the CLOB size is 4 GB - 1 (4 Giga Bytes - 1), which is considerably more than the largest VARCHAR2.

And thus, apparently this solves the problem.

As to when to use parseBuffer or parseClob - I think you got the point. I would guess though, that parseClob calls (internally) parseBuffer for each portion of 4k bytes it splits out of the CLOB it receives, but that's just a guess (I can't imagine they are using different code for the same thing, but hey, it happens with legacy stuff:-) ).

Cheers

Upvotes: 0

user8487380
user8487380

Reputation:

Inside your XML file write the following lines of code after XML code- 

<...XML code...>
BEGIN
FOR r IN (
  SELECT ExtractValue(Value(p),'/row/name/text()') as clo1
      ,ExtractValue(Value(p),'/row/Address/State/text()') as col2
      ,ExtractValue(Value(p),'/row/Address/City/text()') as col3
FROM   TABLE(XMLSequence(Extract(x,'/person/row'))) p
) LOOP
-- do whatever you want 
END LOOP;
END;

Upvotes: 1

Related Questions