Oracle Error "Ora-06502 PL/SQL: numaric or value error: invalid LOB locate"

Question

I'm getting this Oracle error Ora-06502 PL/SQL: numeric or value error: invalid LOB locate when two users try to commit at the same time.

I actually tried hard to solve this problem but I couldn't find a solution.

Please see the code for more details.

PROCEDURE CHECK_POP_DATA_PCC(RETURN_STATUS IN OUT NUMBER) IS LAST BOOLEAN;

 REC_NUMBER NUMBER;

 MESS_CTR NUMBER;

 NO_DETAIL EXCEPTION;

 SWF_FAIL EXCEPTION;

 USA_STATUS NUMBER;

 TEXT_STATUS NUMBER;

 FIRST_ROUND NUMBER;

 OERR NUMBER;

 FILE_NAME VARCHAR2(100);

 FILE_NAME1 VARCHAR2(100);

 FILE_NAME2 VARCHAR2(100);

 FILE_DIR VARCHAR2(100);

 PRINT_ERR EXCEPTION;

 PRINT_ERR1 EXCEPTION;

 MESS_TEXT VARCHAR2(1000);

 CTR NUMBER;

 LEN NUMBER;

 SUB NUMBER;

 OPEN_TYPE NUMBER;

 J NUMBER;

 STR1 VARCHAR2(100);

 STR2 VARCHAR2(100);

 SPC NUMBER;

 ESCP VARCHAR2(512);

 FIRST_SEQ NUMBER;

 REM NUMBER;

 CLOSE_TYPE NUMBER;

 SWF_ADD VARCHAR2(100);

 SWF_PASS VARCHAR2(100);

 SWF_UNAME VARCHAR2(100);

 COMP_FILE TEXT_IO.FILE_TYPE;

 FIRST_FILE TEXT_IO.FILE_TYPE;

 SECOND_FILE TEXT_IO.FILE_TYPE;

 cur_hdl integer;

 rows_p integer;

 tmp_chr VARCHAR2(2);

 SWF_FTP_COM VARCHAR2(100);

 File_status NUMBER;

 ret_sta NUMBER;

 key_all_data CLOB;

 RTGS_IPADD VARCHAR2(100);

 RTGS_FLA NUMBER (1,0);

 RTGS_UNAME VARCHAR2(100);

 RTGS_PASS VARCHAR2(100);

 IS_SEC NUMBER ;


CURSOR C2 IS
SELECT MESS_TYPE_CODE,
       SEQ_NUM,
       FROM_SWF_CODE,
       TO_SWF_CODE,
       PRTY_CODE
FROM SWF_MESS
WHERE BRA_CODE = :SYS.BRA_CODE
  AND REF_TYPE = :SYS.REF_TYPE
  AND REF_YEAR = :SYS.REF_YEAR
  AND REF_NUM = :SYS.REF_NUM
  AND NVL(FILE_NUM,0) = :SYS.FILE_NUM
  AND MESS_STA_CODE NOT IN (1,
                            2,
                            3,
                            5,
                            6,
                            7,
                            8);

 BEGIN RET_STA := 0;


SELECT SWF_IPADDRESS,
       SWF_USERNAME,
       SWF_PASSWORD,
       nvl(SWF_EOL_CHR,'CHR(10)'),
       FTP_COM,
       RTGS_IPADDRESS,
       RTGS_FLAG,
       RTGS_USERNAME,
       RTGS_PASSWORD,
       IS_SECURE INTO SWF_ADD,
                      SWF_UNAME,
                      SWF_PASS,
                      :TIT.EOL_SWF,
                           SWF_FTP_COM,
                           RTGS_IPADD,
                           RTGS_FLA,
                           RTGS_UNAME,
                           RTGS_PASS,
                           IS_SEC ---SHAZA(BANK/27/4540)

FROM web_par
WHERE app_ipaddress = nvl(:GLOBAL.APP_IPADDRESS,'0.0.0.0') ;

 /* *************************************** */ FIRST_ROUND := 0;

 RETURN_STATUS := 0;

 FIRST_SEQ := 0;

 key_all_data := '';

 IF :GLOBAL.BRA_BRA_CODE = :GLOBAL.BAN_HO_REG_CODE THEN GO_BLOCK('SYS');

 FIRST_RECORD;

 LAST := FALSE;

 WHILE NOT LAST LOOP IF :SYS.FLAG = 1 THEN
FOR C2REC IN C2 LOOP A07SWF00(:SYS.BRA_CODE,:SYS.REF_TYPE,:SYS.REF_YEAR,:SYS.REF_NUM, C2REC.MESS_TYPE_CODE,C2REC.SEQ_NUM,RETURN_STATUS);

 IF RETURN_STATUS = 0 THEN
COMMIT;

 CHECK_FRM_STATS;

 ELSE RAISE SWF_FAIL;

 END IF;

 END LOOP;

 END IF;

 IF :SYSTEM.LAST_RECORD = 'TRUE' THEN LAST := TRUE;

 ELSE NEXT_RECORD;

 END IF;

 END LOOP;

 END IF;

 /*****************************************************/ GO_BLOCK('SYS');

 FIRST_RECORD;

 LAST := FALSE;

 WHILE NOT LAST LOOP IF :SYS.FLAG = 1 THEN key_all_data := '';

 FIRST_ROUND := 0;


SELECT SWF_SEQ.NEXTVAL INTO :KEY.SWF_SEQ
FROM DUAL;

 IF FIRST_SEQ = 0 THEN :KEY.FROM_SWF_SEQ := :KEY.SWF_SEQ;

 FIRST_SEQ := 1;

 END IF;


FOR C2REC IN C2 LOOP /*******************************/
SELECT COUNT(*) INTO MESS_CTR
FROM SWF_DETL
WHERE BRA_CODE = :SYS.BRA_CODE
  AND REF_TYPE = :SYS.REF_TYPE
  AND REF_YEAR = :SYS.REF_YEAR
  AND REF_NUM = :SYS.REF_NUM
  AND MESS_TYPE_CODE = C2REC.MESS_TYPE_CODE
  AND SEQ_NUM = C2REC.SEQ_NUM;

 IF MESS_CTR > 0 THEN CHECK_MESS_USA(:SYS.BRA_CODE,:SYS.REF_TYPE,:SYS.REF_YEAR, :SYS.REF_NUM,C2REC.MESS_TYPE_CODE,C2REC.SEQ_NUM, USA_STATUS);

 IF USA_STATUS = 0 THEN CHECK_MESS_TEXT(C2REC.MESS_TYPE_CODE,C2REC.SEQ_NUM, C2REC.FROM_SWF_CODE,C2REC.TO_SWF_CODE, C2REC.PRTY_CODE,TEXT_STATUS);

 IF TEXT_STATUS = 0 THEN A07SWF60 ( :sys.BRA_CODE, :sys.REF_TYPE, :sys.REF_YEAR, :sys.REF_NUM, c2rec.MESS_TYPE_CODE, c2rec.SEQ_NUM, :global.WST_TELL_ID, :key.SWF_SEQ, :GLOBAL.CLI_BANK_DATE, :SYS.ALL_DATA, RETURN_STATUS);

 IF RETURN_STATUS <> 0 THEN RETURN_STATUS := -SQLCODE;

 RETURN;

 END IF;


UPDATE SWF_MESS
SET RTGS_FLAG = :SYS.RTGS_FLAG
WHERE BRA_CODE = :SYS.BRA_CODE
  AND REF_TYPE = :SYS.REF_TYPE
  AND REF_YEAR = :SYS.REF_YEAR
  AND REF_NUM = :SYS.REF_NUM
  AND MESS_TYPE_CODE = C2REC.MESS_TYPE_CODE
  AND SEQ_NUM = C2REC.SEQ_NUM;

 IF FIRST_ROUND = 0 THEN dbms_lob.createtemporary(key_all_data, TRUE);

 dbms_lob.open(key_all_data, 1);

 dbms_lob.append(key_all_data,:SYS.ALL_DATA);

 FIRST_ROUND := 1;

 ELSE ESCP := '';

 IF :SYS.PRE_LEN <> 0 THEN REM := :SYS.PRE_LEN/512;

 IF (REM - TRUNC(REM)) <> 0 THEN SPC := 512 - (:SYS.PRE_LEN - (TRUNC(REM) * 512));


FOR I IN 1..SPC LOOP ESCP := ESCP||CHR(32);

 END LOOP;

 END IF;

 ELSE ESCP := '';

 END IF;

 /*********************************************/ dbms_lob.append(key_all_data,ESCP);

 dbms_lob.append(key_all_data,:SYS.ALL_DATA);

 END IF;

 ELSE RETURN_STATUS := TEXT_STATUS;

 RAISE SWF_FAIL;

 END IF;

 ELSE RETURN_STATUS := USA_STATUS;

 RAISE SWF_FAIL;

 END IF;

 ELSE RAISE NO_DETAIL;

 END IF;

 END LOOP;

 IF :TIT.INP_SECU_CODE = 2 THEN FILE_NAME := 'OUT'||LPAD(TO_CHAR(:KEY.SWF_SEQ),5,'0')||'.ABI';

 ELSE --alliance
 FILE_NAME := 'OUT'||LPAD(TO_CHAR(:KEY.SWF_SEQ),5,'0')||'.MSG';

 END IF;

 --   FILE_DIR  := '/u/oracle/dev/spool';
 --LEN := NVL(LENGTH(key_all_data), 0);
 LEN := NVL(dbms_lob.getlength(key_all_data),0);--

CTR := LEN / 1000 ;

 IF CTR <= 1 THEN CTR := 1;

 SUB := LEN;

 ELSE IF (CTR - TRUNC(CTR)) <> 0 THEN CTR := TRUNC(CTR) + 1;

 END IF ;

 SUB := 1000;

 END IF;

 OPEN_TYPE := 1;

 J := 1;

 IF :tit.eol_swf <> 'CHR(13)||CHR(10)' THEN BEGIN key_all_data := replace(key_all_data,chr(13)||chr(10),chr(substr(:tit.eol_swf,5,2)));

 exception WHEN others THEN NULL;

 END;

 END IF;

 comp_file := TEXT_IO.FOPEN(NAME_IN('GLOBAL.PRINT_PATH')||FILE_NAME, 'W');


FOR I IN 1..CTR LOOP MESS_TEXT := dbms_lob.substr(key_all_data,1000,j);--

TEXT_IO.PUT (comp_file, mess_text);

 OPEN_TYPE := 2;

 IF return_status = 0 THEN J := J + 1000;

 ELSIF RETURN_STATUS IN(1196,
                        1197,
                        1198,
                        1199) THEN RAISE PRINT_ERR;

 ELSE RAISE PRINT_ERR1;

 END IF ;

 END LOOP;

 /************** CLOSING THE FILE *****************************/ IF :SYS.LEN <> 0 THEN REM := :SYS.LEN/512;

 IF (REM - TRUNC(REM)) <> 0 THEN SPC := 512 - (:SYS.LEN - (TRUNC(REM) * 512));

 CLOSE_TYPE := 1;

 ELSE CLOSE_TYPE := 3;

 END IF;

 ELSE CLOSE_TYPE := 3;

 END IF;

 OERR := 0;

 IF CLOSE_TYPE = 1 THEN
FOR I IN 1..SPC LOOP ESCP := CHR(32);

 Text_IO.PUT(COMP_FILE,ESCP);

 END LOOP;

 END IF;

 TEXT_IO.FCLOSE(comp_file);

 RETURN_STATUS := OERR;

 IF OERR <> 0 THEN IF OERR = 1199 THEN RAISE PRINT_ERR;

 ELSE RAISE PRINT_ERR1;

 END IF ;

 END IF;

 IF NVL(RTGS_FLA,0)=2
AND :SYS.RTGS_FLAG=1 THEN send_swf_mess(FILE_NAME, RTGS_IPADD, RTGS_UNAME, RTGS_PASS, SWF_FTP_COM,IS_SEC, RET_STA);

 ELSE send_swf_mess(FILE_NAME, SWF_ADD, SWF_UNAME, SWF_PASS, SWF_FTP_COM, IS_SEC, RET_STA);

 END IF;

 :SYS.LEN := 0;

 GO_BLOCK('SYS');

 END IF;

 IF :SYSTEM.LAST_RECORD = 'TRUE' THEN LAST := TRUE;

 ELSE NEXT_RECORD;

 END IF;

 END LOOP;

 dbms_lob.close(key_all_data);

 FIRST_RECORD;

 :KEY.TO_SWF_SEQ := :KEY.SWF_SEQ;

 EXCEPTION WHEN FORM_TRIGGER_FAILURE THEN RAISE FORM_TRIGGER_FAILURE ;

 WHEN NO_DETAIL THEN :TIT.COMMIT := 1;

 ROLLDATA;

 RETURN_STATUS := 374;

 :GLOBAL.TAB_ENT := '0374';

 DISPLAY_MSG;

 RETURN;

 WHEN SWF_FAIL THEN :TIT.COMMIT := 1;

 ROLLDATA;

 display_err(return_status);

 RETURN;

 WHEN PRINT_ERR THEN ROLLDATA;

 :global.tab_ent :=return_status ;

 display_msg ;

 :TIT.COMMIT := 1 ;

 WHEN PRINT_ERR1 THEN ROLLDATA;

 display_err(return_status);

 :TIT.COMMIT := 1 ;

 WHEN OTHERS THEN :TIT.COMMIT := 1;

 ROLLDATA;

 RETURN_STATUS := -SQLCODE;

 display_err(return_status);

 RETURN;

 END;

This is a screenshot of the error message that displays to one of the users whilst the other user sees Operation successful.

enter description

That's a lot of code to wade though; what debugging have you already done? Only initial thought is that the `key_all_data := ...` assignments look suspicious as they'll conflict with a temporary CLOB, surely? Committing and rolling back in the middle of a procedure makes me twitch, too, but not sure if it can cause this sort of problem. — Alex Poole, Jul 22 '15 at 10:03
@Elyasin - I think you need to find a different prettifier; you really think that's easier to read? — Alex Poole, Jul 22 '15 at 10:09
I really tried. But I could not find a better one online. It is easier to read for me. Do you think I should rollback? Please upvote this comment and I'll revert the change. Sorry for that. — Ely, Jul 22 '15 at 10:13
@AlexPoole - I'm with you that it look suspicious to assignments like that, but i'm talking on another case. let's say there's something wrong with data assignment so it wouldnt work in any case. also it works correctly in normal cases expect the mentioned case. — Shaza Tareq, Jul 22 '15 at 11:05

score 0 · Answer 1 · answered Jul 22 '15 at 10:03

0

I think that you are trying append null values to clob variable.

declare
 key_all_data clob := 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
 v varchar2(10);
begin
  begin 
  dbms_lob.append(key_all_data,v); -- exception 
  exception when others then
   dbms_output.put_line(sqlerrm);
  end;

 for rec in ( select empty_clob() c from dual) loop  -- run - ok 
    dbms_lob.append(key_all_data,rec.c); 
 end loop;

   begin 
 for rec in ( select to_clob(null) c from dual) loop -- exception
    dbms_lob.append(key_all_data,rec.c);
 end loop;
  exception when others then
   dbms_output.put_line(sqlerrm);
  end;
end;

answered Jul 22 '15 at 10:03

Arkadiusz Łukasiewicz

6,241
1
11
17

One user is successful, the other is not. It would be good to see how this leads to assigning NULL value. – Ely Jul 22 '15 at 10:05
I have launched my example on 11.2.0.4 – Arkadiusz Łukasiewicz Jul 22 '15 at 10:12
catch this exception and rethrow exception when others then raise_application_error(-20001,SQLERRM||chr(10)||dbms_utility.format_error_backtrace); end; – Arkadiusz Łukasiewicz Jul 22 '15 at 10:18
Im pretty sure that the problem is not caused by the null value. when i tried the same case by one user its never happened, i also mentioned the specific case and only case i got this error . – Shaza Tareq Jul 22 '15 at 10:59

score 0 · Accepted Answer · answered Jul 22 '15 at 11:45

You will get that error if your C2 cursor doesn't find anything; the CLOB is never opened because you don't go into the loop, but you still do the dbms_lob.close, and that will throw that ORA-22275 error:

DECLARE
  key_all_data CLOB;
BEGIN
  dbms_lob.close(key_all_data);
END;
/

ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275

You don't really need to open your temporary CLOB, which means you don't need to close it either; but you should free it either way. So you can remove the line:

dbms_lob.open(key_all_data, 1);

and change:

dbms_lob.close(key_all_data);

to:

if dbms_lob.istemporary(key_all_data) = 1 then
  dbms_lob.freetemporary(key_all_data);
end if;

If you want to keep the open then test the close part too:

if dbms_lob.istemporary(key_all_data) = 1 then
  if dbms_lob.isopen(key_all_data) = 1 then
    dbms_lob.close(key_all_data);
  end if;
  dbms_lob.freetemporary(key_all_data);
end if;

You might think that if you you have created the temporary CLOB and opened it then it will be open and therefore can be closed; but this:

key_all_data := replace(...);

... is replacing one temporary CLOB with another, which is not explicitly open. You can look at the istemporary and isopen values to see what is happening. You could look at dbms_lob.fragment_replace etc. instead, or skip the open/close and don't worry about it...

It isn't clear if one call to this procedure is changing what the next call sees in the cursor (since the update doesn't seem to do anything) but the form or something this calls might be doing more work that does.

There are probably lots of other issues and comments - exception when others then null jumps out as a really bad idea - but they're getting a bit off-topic.

You are right, this problem is solved after replace the close of LOB, thats why this error appears for the second user ..the cursor C2 doesn't find anything. I just put some traces to find the that the cursor deosnt get data for 2nd user, but unfortionitly you was faster to catch the problem. Thank you very much you also get the solution. — Shaza Tareq, Jul 22 '15 at 12:08

Oracle Error "Ora-06502 PL/SQL: numaric or value error: invalid LOB locate"

2 Answers2