ORA-06502: invalid LOB locator specified

In previous post, we tried to figure out the optimized way to append string to a CLOB. While appending string you might face "ORA-06502: invalid LOB locator specified". Lets try to reproduce "ORA-06502: invalid LOB locator specified" and solve it.

Following PLSQL block is throwing an exception "ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified:"

SQL> declare
  2    my_clob clob;
  3    my_clob_temp clob;
  4    my_time timestamp;
  5  begin
  6    my_time := systimestamp;
  7    for i in 1..10
  8    loop
  9      my_clob_temp := to_clob(i) || ',';
 10      dbms_lob.append(my_clob,my_clob_temp);
 11    end loop;
 12    dbms_output.put_line('Execution Time: ' || TO_CHAR(systimestamp - my_time));
 13  end;
 14  /
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 639
ORA-06512: at line 10

The reason for exception ""ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified:"" is we have used a clob variable in the PLSQL block which was not initialized. We need to initialise our clob with dbms_lob.createtemporary first.

dbms_lob.createtemporary creates a temporary BLOB or CLOB and its corresponding index in the user's default temporary tablespace.

SQL> declare
  2    my_clob clob;
  3    my_clob_temp clob;
  4    my_time timestamp;
  5  begin
  6    my_time := systimestamp;
  7    dbms_lob.createtemporary(my_clob, TRUE);
  8    for i in 1..10
  9    loop
 10      my_clob_temp := to_clob(i) || ',';
 11      dbms_lob.append(my_clob,my_clob_temp);
 12    end loop;
 13    dbms_output.put_line('Execution Time: ' || TO_CHAR(systimestamp - my_time));
 14  end;
 15  /

PL/SQL procedure successfully completed.


Related Posts
- Append String to CLOB in Optimized way
- ORA-06502: PL/SQL: numeric or value errorstring
- ORA-01489: result of string concatenation is too long
- PLS-00172: string literal too long
- ORA-01403: no data found

No comments:

Post a Comment