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.

1 comment:

RajOctober 12, 2019 at 1:32 AM
Hello,

I have the same exception "numeric or value error: invalid LOB locator specified: ORA-22275". I tried using "dbms_lob.createtemporary()" as well. I am trying to read xml and load data into a Oracle table. the procedure below which has an exception

can anyone comment on this plz..

create or replace
PROCEDURE insert_xml_emps(p_directory in varchar2,
p_filename in varchar2,
vtableName in varchar2) as
v_filelocator BFILE;
v_cloblocator CLOB;
l_ctx DBMS_XMLSTORE.CTXTYPE;
l_rows NUMBER;
v_amount_to_load NUMBER;
dest_offset NUMBER := 1;
src_offset NUMBER := 1;
lang_context NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
warning NUMBER;
BEGIN
dbms_lob.createtemporary(v_cloblocator, true);
v_filelocator := bfilename(p_directory, p_filename);
dbms_lob.open(v_filelocator, dbms_lob.file_readonly);
v_amount_to_load := DBMS_LOB.getlength(v_filelocator);

DBMS_LOB.LOADCLOBFROMFILE(v_cloblocator,
v_filelocator,
v_amount_to_load,
dest_offset,
src_offset,
0,
lang_context,
warning);

l_ctx := DBMS_XMLSTORE.newContext(vTableName);
DBMS_XMLSTORE.setRowTag(l_ctx, 'ROWSET');
DBMS_XMLSTORE.setRowTag(l_ctx, 'IBSCOLYTD');
-- clear the update settings
DBMS_XMLStore.clearUpdateColumnList(l_ctx);
-- set the columns to be updated as a list of values
DBMS_XMLStore.setUpdateColumn(l_ctx, 'ACTNOI');
DBMS_XMLStore.setUpdateColumn(l_ctx, 'MEMONOI');
DBMS_XMLStore.setUpdatecolumn(l_ctx, 'MEMODTEI');
DBMS_XMLStore.setUpdatecolumn(l_ctx, 'AMOUNTI');
DBMS_XMLStore.setUpdatecolumn(l_ctx, 'BRCDSI');
DBMS_XMLStore.setUpdatecolumn(l_ctx, 'TYPEI');
DBMS_XMLStore.setUpdatecolumn(l_ctx, 'TRANSMONI');
-- Now insert the doc.
l_rows := DBMS_XMLSTORE.insertxml(l_ctx, v_cloblocator);
DBMS_XMLSTORE.closeContext(l_ctx);
dbms_output.put_line(l_rows || ' rows inserted...');
dbms_lob.close(v_filelocator);
DBMS_LOB.FREETEMPORARY(v_cloblocator);
END;

Exception :

Connecting to the database EDW_Dev.
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 1014
ORA-06512: at "RELANGOVAN.INSERT_XML_EMPS", line 16
ORA-06512: at line 10
Process exited.