Home » SQL & PL/SQL » SQL & PL/SQL » XMLTYPE.getClobVal() help 9i vs. 11G (9i. 11G)
XMLTYPE.getClobVal() help 9i vs. 11G [message #483617] Tue, 23 November 2010 09:03 Go to next message
ocdeveloper
Messages: 9
Registered: November 2010
Junior Member
Hi all,

I have some pl/sql I am using to update an xmltype column.

DECLARE
  lob1 CLOB;

BEGIN

  SELECT e.xml_col.getClobVal() INTO lob1 FROM "XML"."XML_TABLE" e WHERE KEY_COL=3 ;
  DBMS_LOB.APPEND(lob1, 'is a test node</test>');

END;



On 11G this works well, but on 9i I get a ORA-22275: invalid LOB locator specified error. I have tried many different approaches but I'm stumped now. Can anyone help please?
Re: XMLTYPE.getClobVal() help 9i vs. 11G [message #483619 is a reply to message #483617] Tue, 23 November 2010 09:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
22275, 00000, "invalid LOB locator specified"
// *Cause:  There are several causes:  (1) the LOB locator was never
//          initialized; (2) the locator is for a BFILE and the routine
//          expects a BLOB/CLOB/NCLOB locator; (3) the locator is for a
//          BLOB/CLOB/NCLOB and the routine expects a BFILE locator;
//          (4) trying to update the LOB in a trigger body -- LOBs in
//          trigger bodies are read only; (5) the locator is for a 
//          BFILE/BLOB and the routine expects a CLOB/NCLOB locator;
//          (6) the locator is for a CLOB/NCLOB and the routine expects 
//          a BFILE/BLOB locator;
// *Action: For (1), initialize the LOB locator by selecting into the locator
//          variable or by setting the LOB locator to empty.  For (2),(3),
//          (5) and (6)pass the correct type of locator into the routine.  
//          For (4), remove the trigger body code that updates the LOB value.
Re: XMLTYPE.getClobVal() help 9i vs. 11G [message #483620 is a reply to message #483619] Tue, 23 November 2010 09:28 Go to previous messageGo to next message
ocdeveloper
Messages: 9
Registered: November 2010
Junior Member
Thanks BlackSwan. I need a bit more help than that please, why/how is the LOB locator invalid on 9i and not on 11G?

After the "SELECT INTO" I can output the value of lob1 and it is as expected, but the DBMS_LOB.APPEND still gives me ORA-22275.
Re: XMLTYPE.getClobVal() help 9i vs. 11G [message #483621 is a reply to message #483620] Tue, 23 November 2010 09:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>why/how is the LOB locator invalid on 9i and not on 11G?

LOBs were new in V9.
I suspect V9 had a bug that was fixed in later releases.
Nothing surprising here.

There is GOOD REASON why V9 is obsoleted & unsupported now.
Re: XMLTYPE.getClobVal() help 9i vs. 11G [message #483622 is a reply to message #483620] Tue, 23 November 2010 09:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Because many things are better in 11g than in 9i.
In 9i, you have to create a temporary lob to fill with the value.

By the way, 11G and 9i are marketing names not versions and we need versions.

Regards
Michel

[Updated on: Tue, 23 November 2010 09:34]

Report message to a moderator

Re: XMLTYPE.getClobVal() help 9i vs. 11G [message #483623 is a reply to message #483622] Tue, 23 November 2010 09:35 Go to previous messageGo to next message
ocdeveloper
Messages: 9
Registered: November 2010
Junior Member
Sorry 9.2.0.1.0 and 11.2.0.1.0
Re: XMLTYPE.getClobVal() help 9i vs. 11G [message #483630 is a reply to message #483623] Tue, 23 November 2010 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
This does not change my answer or rather initialize the lob using empty_clob function.

Use SQL*Plus and copy and paste your session, the whole one including table creation, insert statements and PL/SQL block execution.
Before, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Tue, 23 November 2010 10:59]

Report message to a moderator

Re: XMLTYPE.getClobVal() help 9i vs. 11G [message #483633 is a reply to message #483630] Tue, 23 November 2010 11:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I have no problem with the following in any (current) version:
SQL> @v

Version Oracle : 9.2.0.8.0

SQL> declare 
  2    lob1 clob;
  3    val  xmltype := xmltype('<x>val</x>');
  4  begin
  5    lob1 := val.getClobVal();
  6    DBMS_LOB.APPEND(lob1, 'is a test node</test>');
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> @v

Version Oracle : 10.2.0.4.0

SQL> declare 
  2    lob1 clob;
  3    val  xmltype := xmltype('<x>val</x>');
  4  begin
  5    lob1 := val.getClobVal();
  6    DBMS_LOB.APPEND(lob1, 'is a test node</test>');
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> @v

Version Oracle : 11.2.0.1.0

SQL> declare 
  2    lob1 clob;
  3    val  xmltype := xmltype('<x>val</x>');
  4  begin
  5    lob1 := val.getClobVal();
  6    DBMS_LOB.APPEND(lob1, 'is a test node</test>');
  7  end;
  8  /

PL/SQL procedure successfully completed.

Although the correct code should be:
declare 
  lob1 clob;
  val  xmltype := xmltype('<x>val</x>');
begin
  dbms_lob.createtemporary (lob_loc=>lob1, cache=>true, dur=>dbms_lob.call);
  lob1 := val.getClobVal();
  DBMS_LOB.APPEND(lob1, 'is a test node</test>');
end;
/

Regards
Michel
Re: XMLTYPE.getClobVal() help 9i vs. 11G [message #483636 is a reply to message #483633] Tue, 23 November 2010 11:31 Go to previous messageGo to next message
ocdeveloper
Messages: 9
Registered: November 2010
Junior Member
Thanks Michel,

Those examples also work for me, the problem arises (invalid LOB locator specified) when I init lob1 like so:

declare 
      lob1 clob;
      val  xmltype := xmltype('<x>val</x>');
    begin
     dbms_lob.createtemporary (lob_loc=>lob1, cache=>true, dur=>dbms_lob.call);
     SELECT e.xml_col.getClobVal() INTO lob1 from XML.XML_TABLE e WHERE key_col = 3;
     DBMS_LOB.APPEND(lob1, 'is a test node</test>');
  end;
 /
Re: XMLTYPE.getClobVal() help 9i vs. 11G [message #483638 is a reply to message #483636] Tue, 23 November 2010 11:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
As I don't see what you have I can't help you more. Please copy and paste your session as I did it.
As you have a 9.2.0.1 version, I advise you to upgrade to the latest patchset.

Regards
Michel
Re: XMLTYPE.getClobVal() help 9i vs. 11G [message #483643 is a reply to message #483621] Tue, 23 November 2010 13:20 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
Senior Member
BlackSwan wrote on Tue, 23 November 2010 07:33

LOBs were new in V9.


I remember lobs being introduced in 8i (8.1.6). They just required initialization (using empty_clob or dbms_lob.createtemporary or some such method). I didn't notice at what point that initialization apparently became automatic.

Re: XMLTYPE.getClobVal() help 9i vs. 11G [message #483904 is a reply to message #483638] Thu, 25 November 2010 07:56 Go to previous messageGo to next message
ocdeveloper
Messages: 9
Registered: November 2010
Junior Member
Michel Cadot wrote on Tue, 23 November 2010 11:58
As I don't see what you have I can't help you more. Please copy and paste your session as I did it.
As you have a 9.2.0.1 version, I advise you to upgrade to the latest patchset.

Regards
Michel



Hi Michel. Here is a copy of my session:


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> CREATE TABLE XML(key_col number primary key, xml_col XMLTYPE);

Table created.

SQL> INSERT INTO XML VALUES (0, XMLTYPE('<x>val</x>'));

1 row created.

SQL> declare
  2        lob1 clob;
  3      begin
  4       dbms_lob.createtemporary (lob_loc=>lob1, cache=>true, dur=>dbms_lob.call);
  5       SELECT e.xml_col.getClobVal() INTO lob1 from XML e WHERE key_col = 0;
  6       DBMS_LOB.APPEND(lob1, 'is a test node</test>');
  7    end;
  8   /
declare
*
ERROR at line 1:
ORA-22275: invalid LOB locator specified
ORA-06512: at "SYS.DBMS_LOB", line 347
ORA-06512: at line 6

SQL>
Re: XMLTYPE.getClobVal() help 9i vs. 11G [message #483921 is a reply to message #483904] Thu, 25 November 2010 09:56 Go to previous message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
As I said:
Quote:
I advise you to upgrade to the latest patchset [as it works with this one].


Regards
Michel
Previous Topic: Dynamic SQL
Next Topic: retrieve records from sys_refcursor
Goto Forum:
  


Current Time: Mon Jun 02 11:16:20 CDT 2025