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

1 comment:

  1. 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.

    ReplyDelete