23
XSU for PL/SQL

This chapter contains the following sections:

XSU PL/SQL API

XML SQL Utility (XSU) PL/SQL API reflects the Java API in the generation and storage of XML documents from and to a database. DBMS_XMLQuery and DBMS_XMLSave are the two packages that reflect the functions in the Java classes - OracleXMLQuery and OracleXMLSave. Both of these packages have a context handle associated with them. Create a context by calling one of the constructor-like functions to get the handle and then use the handle in all subsequent calls.

XSU Supports XMLType

From Oracle9i Release 2 (9.2), XSU supports XMLType. Using XSU with XMLType is useful if, for example, you have XMLType columns in objects or tables.

See Also:

Oracle9i XML Database Developer's Guide - Oracle XML DB, in particular, the chapter on Generating XML, for examples on using XSU with XMLType.

Generating XML with DBMS_XMLQuery()

Generating XML results in a CLOB that contains the XML document. To use DBMS_XMLQuery and the XSU generation engine, follow these steps:

Create a context handle by calling the DBMS_XMLQuery.getCtx function and supplying it the query, either as a CLOB or a VARCHAR2.
Bind possible values to the query using the DBMS_XMLQuery.bind function. The binds work by binding a name to the position. For example, the query can be select * from emp where empno = :EMPNO_VAR. Here you are binding the value for the EMPNO_VAR using the setBindValue function.
Set optional arguments like the ROW tag name, the ROWSET tag name, or the number of rows to fetch, and so on.
Fetch the XML as a CLOB using the getXML() functions. getXML() can be called to generate the XML with or without a DTD or schema.
Close the context.

Here are some examples that use the DBMS_XMLQuery PL/SQL package.

XSU Generating XML Example 1: Generating XML from Simple Queries (PL/SQL)

In this example, you select rows from table emp, and obtain an XML document as a CLOB. First get the context handle by passing in a query and then call the getXMLClob routine to get the CLOB value. The document is in the same encoding as the database character set.

declare
  queryCtx DBMS_XMLquery.ctxType;
  result CLOB;
begin

  -- set up the query context...!
  queryCtx := DBMS_XMLQuery.newContext('select * from emp');
 
  -- get the result..!
  result := DBMS_XMLQuery.getXML(queryCtx);
  -- Now you can use the result to put it in tables/send as messages..
  printClobOut(result);
  DBMS_XMLQuery.closeContext(queryCtx);  -- you must close the query handle..
end;
/

XSU Generating XML Example 2: Printing CLOB to Output Buffer

printClobOut() is a simple procedure that prints the CLOB to the output buffer. If you run this PL/SQL code in SQL*Plus, the result of the CLOB is printed to screen. Set the serveroutput to on in order to see the results.

CREATE OR REPLACE PROCEDURE printClobOut(result IN OUT NOCOPY CLOB) is
xmlstr varchar2(32767);
line varchar2(2000);
begin
  xmlstr := dbms_lob.SUBSTR(result,32767);
  loop
    exit when xmlstr is null;
    line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
    dbms_output.put_line('| '||line);
    xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
  end loop;
end;
/

XSU Generating XML Example 3: Changing ROW and ROWSET Tag Names

With the XSU PL/SQL API you can also change the ROW and the ROWSET tag names. These are the default names placed around each row of the result, and round the whole document, respectively. The procedures, setRowTagName and setRowSetTagName accomplish this as shown in the following example:

--Setting the ROW tag names

declare
   queryCtx DBMS_XMLQuery.ctxType;
   result CLOB;
begin
   -- set the query context.
   queryCtx := DBMS_XMLQuery.newContext('select * from emp');
 
   DBMS_XMLQuery.setRowTag(queryCtx,'EMP'); -- sets the row tag name
   DBMS_XMLQuery.setRowSetTag(queryCtx,'EMPSET'); -- sets rowset tag name

   result := DBMS_XMLQuery.getXML(queryCtx); -- get the result

   printClobOut(result);  -- print the result..!
   DBMS_XMLQuery.closeContext(queryCtx);  -- close the query handle;
end;
/

The resulting XML document has an EMPSET document element. Each row is separated using the EMP tag.

XSU Generating XML Example 4: Using setMaxRows() and setSkipRows()

The results from the query generation can be paginated by using:

setMaxRows function. This sets the maximum number of rows to be converted to XML. This is relative to the current row position from which the last result was generated.
setSkipRows function. This specifies the number of rows to skip before converting the row values to XML.

For example, to skip the first 3 rows of the emp table and then print out the rest of the rows 10 at a time, you can set the skipRows to 3 for the first batch of 10 rows and then set skipRows to 0 for the rest of the batches.

As in the case of XML SQL Utility's Java API, call the keepObjectOpen() function to ensure that the state is maintained between fetches. The default behavior is to close the state after a fetch. For multiple fetches, you must determine when there are no more rows to fetch. This can be done by setting the setRaiseNoRowsException(). This causes an exception to be raised if no rows are written to the CLOB. This can be caught and used as the termination condition.

-- Pagination of results

declare
  queryCtx DBMS_XMLquery.ctxType;
  result CLOB;
begin

  -- set up the query context...!
  queryCtx := DBMS_XMLQuery.newContext('select * from emp');
 
  DBMS_XMLQuery.setSkipRows(queryCtx,3); -- set the number of rows to skip
  DBMS_XMLQuery.setMaxRows(queryCtx,10); -- set the max number of rows per fetch

  result := DBMS_XMLQuery.getXML(queryCtx); -- get the first result..!

  printClobOut(result); -- print the result out.. This is you own routine..!
  DBMS_XMLQuery.setSkipRows(queryCtx,0); -- from now don't skip any more rows..!

  DBMS_XMLQuery.setRaiseNoRowsException(queryCtx,true);
                                         -- raise no rows exception..!
  begin
    loop  -- loop forever..!
      result := DBMS_XMLQuery.getXML(queryCtx); -- get the next batch 
      printClobOut(result);             -- print the next batch of 10 rows..!
    end loop;
  exception
    when others then
    -- dbms_output.put_line(sqlerrm);
       null; -- termination condition, nothing to do;
  end;
  DBMS_XMLQuery.closeContext(queryCtx);  -- close the handle..!
end;
/

Setting Stylesheets in XSU (PL/SQL)

The XSU PL/SQL API provides the ability to set stylesheets on the generated XML documents as follows:

Set the stylesheet header in the result XML. To do this, use setStylesheetHeader() procedure, to set the stylesheet header in the result. This simply adds the XML processing instruction to include the stylesheet.
Apply a stylesheet to the result XML document, before generation. This method is a huge performance win since otherwise the XML document has to be generated as a CLOB, sent to the parser again, and then have the stylesheet applied. XSU generates a DOM document, calls the parser, applies the stylesheet and then generates the result. To apply the stylesheet to the resulting XML document, use the useStyleSheet() procedure. This uses the stylesheet to generate the result.

Binding Values in XSU (PL/SQL)

The XSU PL/SQL API provides the ability to bind values to the SQL statement. The SQL statement can contain named bind variables. The variables must be prefixed with a colon (:) to declare that they are bind variables. To use the bind variable follow these steps:

Initialize the query context with the query containing the bind variables. For example, the following statement registers a query to select the rows from the emp table with the where clause containing the bind variables :EMPNO and :ENAME. You will bind the values for employee number and employee name later.
```
queryCtx = DBMS_XMLQuery.getCtx('select * from emp where empno = :EMPNO and 
ename = :ENAME');
```
Set the list of bind values. The clearBindValues() clears all the bind variables set. The setBindValue() sets a single bind variable with a string value. For example, you will set the empno and ename values as shown later:
```
DBMS_XMLQuery.clearBindValues(queryCtx);
DBMS_XMLQuery.setBindValue(queryCtx,'EMPNO',20);
DBMS_XMLQuery.setBindValue(queryCtx,'ENAME','John');
```
Fetch the results. This will apply the bind values to the statement and then get the result corresponding to the predicate empno = 20 and ename = 'John'.
```
DBMS_XMLQuery.getXMLClob(queryCtx);
```
Re-bind values if necessary. For example to change the ENAME alone to scott and reexecute the query,
```
DBMS_XMLQuery.setBindValue(queryCtx,'ENAME','Scott');
```
The rebinding of ENAME will now use Scott instead of John.

XSU Generating XML Example 5: Binding Values to the SQL Statement

The following example illustrates the use of bind variables in the SQL statement:

declare
  queryCtx DBMS_XMLquery.ctxType;
  result CLOB;
begin

queryCtx := DBMS_XMLQuery.newContext(
       'select * from emp where empno = :EMPNO and ename = :ENAME');

--No longer needed:
--DBMS_XMLQuery.clearBindValues(queryCtx);
DBMS_XMLQuery.setBindValue(queryCtx,'EMPNO',7566);
DBMS_XMLQuery.setBindValue(queryCtx,'ENAME','JONES');

result := DBMS_XMLQuery.getXML(queryCtx);

--printClobOut(result); 

DBMS_XMLQuery.setBindValue(queryCtx,'ENAME','Scott');

result := DBMS_XMLQuery.getXML(queryCtx);

--printClobOut(result);
end;
/

Storing XML in the Database Using DBMS_XMLSave

To use DBMS_XMLSave() and XML SQL Utility storage engine, follow these steps:

Create a context handle by calling the DBMS_XMLSave.getCtx function and supplying it the table name to use for the DML operations.
For inserts. You can set the list of columns to insert into using the setUpdateColNames function. The default is to insert values into all the columns.

For updates. The list of key columns must be supplied. Optionally the list of columns to update may also be supplied. In this case, the tags in the XML document matching the key column names will be used in the WHERE clause of the update statement and the tags matching the update column list will be used in the SET clause of the update statement.

For deletes. The default is to create a WHERE clause to match all the tag values present in each ROW element of the document supplied. To override this behavior you can set the list of key columns. In this case only those tag values whose tag names match these columns will be used to identify the rows to delete (in effect used in the WHERE clause of the delete statement).
Supply an XML document to the insertXML, updateXML, or deleteXML functions to insert, update and delete respectively.
You can repeat the last operation any number of times.
Close the context.

Use the same examples as for the Java case, OracleXMLSave class examples.

Insert Processing Using XSU (PL/SQL API)

To insert a document into a table or view, simply supply the table or the view name and then the XML document. XSU parses the XML document (if a string is given) and then creates an INSERT statement, into which it binds all the values. By default, XSU inserts values into all the columns of the table or view and an absent element is treated as a NULL value.

The following code shows how the document generated from the emp table can be put back into it with relative ease.

XSU Inserting XML Example 6: Inserting Values into All Columns (PL/SQL)

This example creates a procedure, insProc, which takes in:

An XML document as a CLOB
A table name to put the document into

and then inserts the XML document into the table:

create or replace procedure insProc(xmlDoc IN CLOB, tableName IN VARCHAR2) is
   insCtx DBMS_XMLSave.ctxType;
   rows number;
 begin
    insCtx := DBMS_XMLSave.newContext(tableName); -- get the context handle
    rows := DBMS_XMLSave.insertXML(insCtx,xmlDoc); -- this inserts the document
    DBMS_XMLSave.closeContext(insCtx);            -- this closes the handle
end;
/

This procedure can now be called with any XML document and a table name. For example, a call of the form:

insProc(xmlDocument, 'scott.emp');

generates an INSERT statement of the form:

insert into scott.emp (EMPNO, ENAME, JOB, MGR, SAL, DEPTNO) VALUES(?,?,?,?,?,?);

and the element tags in the input XML document matching the column names will be matched and their values bound. For the code snippet shown earlier, if you send it the following XML document:

 <?xml version='1.0'?>
<ROWSET>
  <ROW num="1">
    <EMPNO>7369</EMPNO>
    <ENAME>Smith</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7902</MGR>
    <HIREDATE>12/17/1980 0:0:0</HIREDATE>
    <SAL>800</SAL>
    <DEPTNO>20</DEPTNO>
  </ROW>
  <!-- additional rows ... -->
</ROWSET>

you would have a new row in the emp table containing the values (7369, Smith, CLERK, 7902, 12/17/1980,800,20). Any element absent inside the row element would is considered a null value.

XSU Inserting XML Example 7: Inserting Values into Certain Columns (PL/SQL)

In certain cases, you may not want to insert values into all columns. This might be true when the values that you are getting is not the complete set and you need triggers or default values to be used for the rest of the columns. The code that appears later shows how this can be done.

Assume that you are getting the values only for the employee number, name, and job, and that the salary, manager, department number and hiredate fields are filled in automatically. You create a list of column names that you want the insert to work on and then pass it to the DBMS_XMLSave procedure. The setting of these values can be done by calling setUpdateColumnName() procedure repeatedly, passing in a column name to update every time. The column name settings can be cleared using clearUpdateColumnNames().


create or replace procedure testInsert( xmlDoc IN clob) is
  insCtx DBMS_XMLSave.ctxType;
  doc clob;
  rows number;
begin
 
   insCtx := DBMS_XMLSave.newContext('scott.emp'); -- get the save context..!

   DBMS_XMLSave.clearUpdateColumnList(insCtx); -- clear the update settings

   -- set the columns to be updated as a list of values..
   DBMS_XMLSave.setUpdateColumn(insCtx,'EMPNO'); 
   DBMS_XMLSave.setUpdateColumn(insCtx,'ENAME');
   DBMS_XMLSave.setUpdatecolumn(insCtx,'JOB');

   -- Now insert the doc. This will only insert into EMPNO,ENAME and JOB columns
   rows := DBMS_XMLSave.insertXML(insCtx, xmlDoc); 
   DBMS_XMLSave.closeContext(insCtx);

end;
/

If you call the procedure passing in a CLOB as a document, an INSERT statement of the form:

insert into scott.emp (EMPNO, ENAME, JOB) VALUES (?, ?, ?);

is generated. Note that in the earlier example, if the inserted document contains values for the other columns (JOB, HIREDATE, and so on), those are ignored.

Also an insert is performed for each ROW element that is present in the input. These inserts are batched by default.

Update Processing Using XSU (PL/SQL API)

Now that you know how to insert values into the table from XML documents, let us see how to update only certain values. If you get an XML document to update the salary of an employee and also the department that she works in:

<ROWSET>
  <ROW num="1">
    <EMPNO>7369</EMPNO>
    <SAL>1800</SAL>
    <DEPTNO>30</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>2290</EMPNO>
    <SAL>2000</SAL>
    <HIREDATE>12/31/1992</HIREDATE>
  <!-- additional rows ... -->
</ROWSET>

you can call the update processing to update the values. In the case of update, you need to supply XSU with the list of key column names. These form part of the where clause in the update statement. In the emp table shown earlier, the employee number (EMPNO) column forms the key and you use that for updates.

XSU Updating XML Example 8: Updating XML Document Key Columns (PL/SQL)

Consider the PL/SQL procedure:

create or replace procedure testUpdate ( xmlDoc IN clob) is
  updCtx DBMS_XMLSave.ctxType; 
  rows number;
begin
   
   updCtx := DBMS_XMLSave.newContext('scott.emp');  -- get the context
   DBMS_XMLSave.clearUpdateColumnList(updCtx); -- clear the update settings..
  
   DBMS_XMLSave.setKeyColumn(updCtx,'EMPNO'); -- set EMPNO as key column
   rows := DBMS_XMLSave.updateXML(updCtx,xmlDoc);  -- update the table.
   DBMS_XMLSave.closeContext(updCtx);             -- close the context..!

end;
/

In this example, when the procedure is executed with a CLOB value that contains the document described earlier, two update statements would be generated. For the first ROW element, you would generate an UPDATE statement to update the SAL and JOB fields as shown:

UPDATE scott.emp SET SAL = 1800 and DEPTNO = 30 WHERE EMPNO = 7369;

and for the second ROW element,

UPDATE scott.emp SET SAL = 2000 and HIREDATE = 12/31/1992 WHERE EMPNO = 2290;

XSU Updating XML Example 9: Specifying a List of Columns to Update (PL/SQL)

You may want to specify the list of columns to update. This would speed up the processing since the same update statement can be used for all the ROW elements. Also you can ignore other tags which occur in the document. Note that when you specify a list of columns to update, an element corresponding to one of the update columns, if absent, will be treated as NULL.

If you know that all the elements to be updated are the same for all the ROW elements in the XML document, then you can use the setUpdateColumnName() procedure to set the column name to update.

create or replace procedure testUpdate(xmlDoc IN CLOB) is
  updCtx DBMS_XMLSave.ctxType;
  rows number;
begin
  
   updCtx := DBMS_XMLSave.newContext('scott.emp');
   DBMS_XMLSave.setKeyColumn(updCtx,'EMPNO'); -- set EMPNO as key column

   -- set list of columnst to update.
   DBMS_XMLSave.setUpdateColumn(updCtx,'SAL');
   DBMS_XMLSave.setUpdateColumn(updCtx,'JOB');

   rows := DBMS_XMLSave.updateXML(updCtx,xmlDoc); -- update the XML document..!
   DBMS_XMLSave.closeContext(updCtx);   -- close the handle

end;
/

Delete Processing Using XSU (PL/SQL API)

For deletes, you can set the list of key columns. These columns will be put as part of the WHERE clause of the DELETE statement. If the key column names are not supplied, then a new DELETE statement will be created for each ROW element of the XML document where the list of columns in the WHERE clause of the DELETE will match those in the ROW element.

XSU Deleting XML Example 10: Deleting Operations for Each Row (PL/SQL)

Consider the delete example shown here:

create or replace procedure testDelete(xmlDoc IN clob) is
  delCtx DBMS_XMLSave.ctxType;
  rows number;
begin

   delCtx  := DBMS_XMLSave.newContext('scott.emp');
   DBMS_XMLSave.setKeyColumn(delCtx,'EMPNO');

   rows := DBMS_XMLSave.deleteXML(delCtx,xmlDoc);
   DBMS_XMLSave.closeContext(delCtx);
end;
/

If you use the same XML document shown for the update example, you would end up with two DELETE statements,

DELETE FROM scott.emp WHERE empno=7369 and sal=1800 and deptno=30; 
DELETE FROM scott.emp WHERE empno=2200 and sal=2000 and hiredate=12/31/1992;

The DELETE statements were formed based on the tag names present in each ROW element in the XML document.

XSU Example 11: Deleting by Specifying the Key Values (PL/SQL)

If instead you want the delete to only use the key values as predicates, you can use the setKeyColumn function to set this.

create or replace package testDML AS
   saveCtx DBMS_XMLSave.ctxType := null;   -- a single static variable

   procedure insertXML(xmlDoc in clob);
   procedure updateXML(xmlDoc in clob);
   procedure deleteXML(xmlDoc in clob);

 end;
/

create or replace package body testDML AS
  
  rows number;
  
  procedure insertXML(xmlDoc in clob) is
  begin
    rows := DBMS_XMLSave.insertXML(saveCtx,xmlDoc);
  end;
  
  procedure updateXML(xmlDoc in clob) is
  begin
    rows := DBMS_XMLSave.updateXML(saveCtx,xmlDoc);
  end;
  
  procedure deleteXML(xmlDoc in clob) is
  begin
    rows := DBMS_XMLSave.deleteXML(saveCtx,xmlDoc);
  end;

begin
  saveCtx := DBMS_XMLSave.newContext('scott.emp'); -- create the context once..!
  DBMS_XMLSave.setKeyColumn(saveCtx, 'EMPNO');      -- set the key column name.
end;
/

Here a single delete statement of the form,

DELETE FROM scott.emp WHERE EMPNO=?

will be generated and used for all ROW elements in the document.

XSU Deleting XML Example 12: Reusing the Context Handle (PL/SQL)

In all the three cases described earlier, insert, update, and delete, the same context handle can be used to do more than one operation. That is, you can perform more than one insert using the same context provided all of those inserts are going to the same table that was specified when creating the save context. The context can also be used to mix updates, deletes, and inserts.

For example, the following code shows how one can use the same context and settings to insert, delete, or update values depending on the user's input.

The example uses a PL/SQL supplied package static variable to store the context so that the same context can be used for all the function calls.

create or replace package testDML AS
   saveCtx DBMS_XMLSave.ctxType := null;   -- a single static variable

   procedure insert(xmlDoc in clob);
   procedure update(xmlDoc in clob);
   procedure delete(xmlDoc in clob);

 end;
/

create or replace package body testDML AS
  
  procedure insert(xmlDoc in clob) is
  begin
    DBMS_XMLSave.insertXML(saveCtx, xmlDoc);
  end;
  
  procedure update(xmlDoc in clob) is
  begin
    DBMS_XMLSave.updateXML(saveCtx, xmlDoc);
  end;
  
  procedure delete(xmlDoc in clob) is
  begin
    DBMS_XMLSave.deleteXML(saveCtx, xmlDoc);
  end;

  begin
    saveCtx := DBMS_XMLSave.newContext('scott.emp'); -- create the context 
once..!
    DBMS_XMLSave.setKeyColumn(saveCtx, 'EMPNO');   -- set the key column name.
  end;
end;
/

In the earlier package, you create a context once for the whole package (thus the session) and then reuse the same context for performing inserts, updates and deletes.

Note:

The key column EMPNO would be used both for updates and deletes as a way of identifying the row.

Users of this package can now call any of the three routines to update the emp table:

testDML.insert(xmlclob);
testDML.delete(xmlclob);
testDML.update(xmlclob);

All of these calls would use the same context. This would improve the performance of these operations, particularly if these operations are performed frequently.

XSU Exception Handling in PL/SQL

Here is an XSU PL/SQL exception handling example:

declare
  queryCtx DBMS_XMLQuery.ctxType;
  result clob;
  errorNum NUMBER;
  errorMsg VARCHAR2(200);
begin

  queryCtx := DBMS_XMLQuery.newContext('select * from emp where df = dfdf');

  -- set the raise exception to true..
  DBMS_XMLQuery.setRaiseException(queryCtx, true);
  DBMS_XMLQuery.setRaiseNoRowsException(queryCtx, true);

  -- set propagate original exception to true to get the original exception..!
  DBMS_XMLQuery.propagateOriginalException(queryCtx,true);
  result := DBMS_XMLQuery.getXML(queryCtx);

  exception
    when others then
      -- get the original exception
      DBMS_XMLQuery.getExceptionContent(queryCtx,errorNum, errorMsg);
      dbms_output.put_line(' Exception caught ' || TO_CHAR(errorNum)
                   || errorMsg );
end; 
/

Frequently Asked Questions About XML SQL Utility (XSU) for PL/SQL

Here are FAQs about XSU for PL/SQL:

How Can I Use XMLGEN.insertXML with LOBs?

I am trying to use the insertXML procedure from XSU. I have little experience with using LOBS. What is the problem in my script?

I have a table lob_temp:

 SQL> desc lob_temp 
 Name Null? Type 
 ----------------- -------- ------------------  ---------- 
 CHUNK CLOB 

 SQL> set long 100000 
 SQL> select * from lob_temp; 

 CHUNK 
 -----------------------------------------  ---------- 
 <DOCID> 91739.1 </DOCID> 

 <SUBJECT> MTS: ORA-29855, DRG-50704, ORA-12154: on create index using  
Intermedia 
 </SUBJECT> 
 <TYPE> PROBLEM </TYPE> 
 <CONTENT_TYPE> TEXT/PLAIN </CONTENT_TYPE> 
 <STATUS> PUBLISHED </STATUS> 
 <CREATION_DATE> 14-DEC-1999 </CREATION_DATE> 
 <LAST_REVISION_DATE> 05-JUN-2000 </LAST_REVISION_DATE> 
 <LANGUAGE> USAENG </LANGUAGE>

I have another table where I need to insert data from lob_temp:

 SQL> desc metalink_doc 
 Name Null? Type 
 ---------------- -------- ------------------ ---------- 
 DOCID VARCHAR2(10) 
 SUBJECT VARCHAR2(100) 
 TYPE VARCHAR2(20) 
 CONTENT_TYPE VARCHAR2(20) 
 STATUS VARCHAR2(20) 
 CREATION_DATE DATE 
 LAST_REVISION_DATE DATE 
 LANGUAGE VARCHAR2(10)

This is the script. It is supposed to read data from lob_temp and then insert the data, extracted from the XML document, to table metalink_doc:

 declare 
 xmlstr clob := null; 
 amount integer := 255; 
 position integer := 1; 
 charstring varchar2(255); 
 finalstr varchar2(4000) := null; 
 ignore_case constant number := 0; 
 default_date_format constant varchar2(21) := 'DD-MON-YYYY'; 
 default_rowtag constant varchar2(10) := 'MDOC_DATA'; 
 len integer; 
 insrow integer; 
 begin 
 select chunk into xmlstr from lob_temp; 
 dbms_lob.open(xmlstr,dbms_lob.lob_readonly); 
 len := dbms_lob.getlength(xmlstr); 
 while position < len loop 
 dbms_lob.read(xmlstr,amount,position,charstring); 
 if finalstr is not null then 
 finalstr := finalstr||charstring; 
 else 
 finalstr := charstring; 
 end if; 
 position := position + amount; 
 end loop; 
 insrow := xmlgen.insertXML('metalink_doc',finalstr); 
 dbms_output.put_line(insrow); 
 dbms_lob.close(xmlstr); 
 exception 
 when others then 
 dbms_lob.close(xmlstr); 
 dbms_lob.freetemporary(xmlstr); 
 end; 
 /

This is the error received:

ERROR at line 1: 
ORA-22275: invalid LOB locator specified 
ORA-06512: at "SYS.DBMS_LOB", line 485 
ORA-06512: at line 31 
ORA-29532: Java call terminated by uncaught Java exception: 
oracle.xml.sql.OracleXMLSQLException: Expected 'EOF'.

The user I am logged in as owns both tables, and all objects created when I ran oraclexmlsqlload.csh.

Answer: You need to have <ROWSET> and <ROW> tags to insert XML document into a table. I modified your procedure. There is a problem when parsing the DATE format, hence I used VARCHAR2:

drop table lob_temp; 
 create table lob_temp (chunk clob); 
 insert into lob_temp values (' 
 <ROWSET> 
 <ROW> 
 <DOCID> 91739.1 </DOCID> 
 <SUBJECT> MTS: ORA-29855, DRG-50704, ORA-12154: on create index using 
Intermedia </SUBJECT> 
 <TYPE> PROBLEM </TYPE> 
 <CONTENT_TYPE> TEXT/PLAIN </CONTENT_TYPE> 
 <STATUS> PUBLISHED </STATUS> 
 <CREATION_DATE> 14-DEC-1999 </CREATION_DATE> 
 <LAST_REVISION_DATE> 05-JUN-2000 </LAST_REVISION_DATE> 
 <LANGUAGE> USAENG </LANGUAGE> 
 </ROW> 
 </ROWSET> 
 '); 

 drop table metalink_doc; 
 create table metalink_doc ( 
 DOCID VARCHAR2(10), 
 SUBJECT VARCHAR2(100), 
 TYPE VARCHAR2(20), 
 CONTENT_TYPE VARCHAR2(20), 
 STATUS VARCHAR2(20), 
 CREATION_DATE VARCHAR2(50), 
 LAST_REVISION_DATE varchar2(50), 
 LANGUAGE VARCHAR2(10) 
 ); 

 create or replace procedure prtest as 
 xmlstr clob := null; 
 amount integer := 255; 
 position integer := 1; 
 charstring varchar2(255); 
 finalstr varchar2(4000) := null; 
 ignore_case constant number := 0; 
 default_date_format constant varchar2(21) := 'DD-MON-YYYY'; 
 default_rowtag constant varchar2(10) := 'MDOC_DATA'; 
 len integer; 
 insrow integer; 
 begin 

 select chunk into xmlstr from lob_temp; 
 dbms_lob.open(xmlstr,dbms_lob.lob_readonly); 
 len := dbms_lob.getlength(xmlstr); 

 while position < len loop 
 dbms_lob.read(xmlstr,amount,position,charstring); 
 if finalstr is not null then 
 finalstr := finalstr||charstring; 
 else 
 finalstr := charstring; 
 end if; 
 position := position + amount; 
 end loop; 

 insrow := xmlgen.insertXML('metalink_doc',finalstr); 
 dbms_output.put_line(insrow); 

 IF DBMS_LOB.ISOPEN(xmlstr) = 1 THEN 
 dbms_lob.close(xmlstr); 
 END IF; 

 exception 
 when others then 
 IF DBMS_LOB.ISOPEN(xmlstr)=1 THEN 
 dbms_lob.close(xmlstr); 
 END IF; 
 end; 
 / 
 show err

23 XSU for PL/SQL