Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502: PL/SQL: numeric or value error: invalid LOB locator (oracle windows 2003)
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator [message #406316] Wed, 03 June 2009 05:11 Go to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Hi can any one suggest me where i need to modify.
The same procedure works fine with one record but not the second
through some java programme the field value set to null so how can i initialize that to execute successfully

SQL> ed
Wrote file afiedt.buf

  1    DECLARE
  2     my_text_handle CLOB;
  3    my_buffer clob;
  4     my_add_amt NUMBER := 32000;
  5     my_offset INTEGER := 1;
  6   buf varchar2(32000);
  7     BEGIN
  8   select text into my_buffer  from SRIRAM_CLOB;
  9   buf := dbms_lob.substr(my_buffer);
 10        my_add_amt := length(buf);
 11     SELECT long_text
 12     INTO my_text_handle
 13     FROM text
 14     where  TEXT_SEQUENCE_NUMBER=246 and  text_id= 88 FOR UPDATE;
 15     DBMS_LOB.WRITE(my_text_handle, my_add_amt, my_offset, buf);
 16     COMMIT;
 17*    END;
SQL> /

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

  1    DECLARE
  2     my_text_handle CLOB;
  3    my_buffer clob;
  4     my_add_amt NUMBER := 32000;
  5     my_offset INTEGER := 1;
  6   buf varchar2(32000);
  7     BEGIN
  8   select text into my_buffer  from SRIRAM_CLOB;
  9   buf := dbms_lob.substr(my_buffer);
 10        my_add_amt := length(buf);
 11     SELECT long_text
 12     INTO my_text_handle
 13     FROM text 
 14     where  TEXT_SEQUENCE_NUMBER=14257 and  text_id= 88 FOR UPDATE;
 15     DBMS_LOB.WRITE(my_text_handle, my_add_amt, my_offset, buf);
 16     COMMIT;
 17*    END;
SQL> /
  DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified:
ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 819
ORA-06512: at line 15



regards
Sriram
Re: ORA-06502: PL/SQL: numeric or value error: invalid LOB locator [message #406317 is a reply to message #406316] Wed, 03 June 2009 05:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-22275: 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.

Regards
Michel
Re: ORA-06502: PL/SQL: numeric or value error: invalid LOB locator [message #406319 is a reply to message #406317] Wed, 03 June 2009 05:27 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Hi,Michel thanks for your kind and fast reply....

As i asked
Quote:
through some java programme the field value set to null so how can i initialize that to execute successfully


here my case is (1) for that how can i initialize that....refer me any link

regards
Sriram
Re: ORA-06502: PL/SQL: numeric or value error: invalid LOB locator [message #406321 is a reply to message #406319] Wed, 03 June 2009 05:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
EMPTY_CLOB

Regards
Michel
Re: ORA-06502: PL/SQL: numeric or value error: invalid LOB locator [message #406327 is a reply to message #406321] Wed, 03 June 2009 05:53 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Thanks Michel for your reply...

So do i need to make it empty before update with new one.

Thanks
Sriram Smile

[Updated on: Wed, 03 June 2009 05:54]

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: invalid LOB locator [message #406329 is a reply to message #406327] Wed, 03 June 2009 05:58 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If your clob does not exist (field is null) then you have to create an empty clob to get a valid locator.
If it exist yet you can reuse the same locator.

Regards
Michel
Previous Topic: get value of DB parameter in JDBC
Next Topic: Binary search tree traversal
Goto Forum:
  


Current Time: Sun Dec 04 12:53:32 CST 2016

Total time taken to generate the page: 0.10877 seconds