Getting ORA-22993 error with 8.1.7 under OpenVMS when trying to insert an XML Doc into a CLOB column

From: Ivan Samuelson <isamuels_at_columbus.rr.com>
Date: 11 Jul 2001 06:35:32 -0700
Message-ID: <9e3f3c6.0107110535.7b98f1cb@posting.google.com>

I am using the examples in the O'Reilly book, Building Oracle XML Applications. Specifically, I'm using the example in Chapter 5 on page 107.

I have a table defined as follows:

SQL> desc xml_documents

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 DOCNAME                                   NOT NULL VARCHAR2(200)
 XMLDOC                                             CLOB
 TIMESTAMP                                          DATE

I have create a procedure as follows:

CREATE OR REPLACE PROCEDURE insertXmlFile( dir VARCHAR2,

                                          file VARCHAR2,
                                          name VARCHAR2 := NULL) IS

  theBFile BFILE;
  theCLob CLOB;
  theDocName VARCHAR2(200) := NVL(name,file); BEGIN

• (1) Insert a new row into xml_documents with an empty CLOB, and
• (2) Retrieve the empty CLOB into a variable with RETURNING..INTO dbms_output.put_line(theDocName); INSERT INTO xml_documents(docname,xmldoc) VALUES(theDocName,empty_clob()) RETURNING xmldoc INTO theCLob;
• (3) Get a BFile handle to the external file theBFile := BFileName(dir,file);
• (4) Open the file dbms_lob.fileOpen(theBFile);

  dbms_output.put_line(dbms_lob.getLength(theBFile));

• (5) Copy the contents of the BFile into the empty CLOB dbms_lob.loadFromFile(dest_lob => theCLob, src_lob => theBFile, amount => dbms_lob.getLength(theBFile));
• (6) Close the file and commit dbms_lob.fileClose(theBFile); COMMIT; END;

The XML file I want to insert is as follows:

<?xml version = "1.0" encoding = "UTF-8"?>

<!-- claim77804.xml -->
<Claim>

	<ClaimId>77804</ClaimId>  
	<Policy>12345</Policy>  
	<Settlements>    
		<Payment Approver = "JCOX">1000</Payment>    
		<Payment Approver = "PSMITH">1850</Payment>  
	</Settlements>  
	<DamageReport>    The insured's <Vehicle Make =

"Volks">Beetle</Vehicle> broke through the guard rail and plummeted into a ravine.

    The cause was determined to be <Cause>faulty brakes</Cause>.     Amazingly there were no casualties.
  </DamageReport>
</Claim>

The parser and all related files are available to the schema I'm in and everything else is installed as it should be.

I run the following query which shows I can read the file from disk:

SQL> select dbms_lob.getLength(BFileName('XMLFILES','claim77804.xml')) as length

  2 from dual;

    LENGTH

---

       498

SQL> However, when I attempt to finally load the file into my xml_documents table, this is what happens:

SQL> execute insertxmlfile('XMLFILES','claim77805.xml') BEGIN insertxmlfile('XMLFILES','claim77805.xml'); END;

*
ERROR at line 1:

ORA-22993: specified input amount is greater than actual source amount
ORA-06512: at "SYS.DBMS_LOB", line 587
ORA-06512: at "S010792.INSERTXMLFILE", line 23
ORA-06512: at line 1

Any ideas? Is there a problem with loading CLOBs under OpenVMS on an Alpha? We're running OpenVMS 7-2.1 and Oracle 8i 8.1.7 and have applied all patches that Oracle has given us.

Thanks! Received on Wed Jul 11 2001 - 08:35:32 CDT