title: Oracle Error "Ora-06502 PL/SQL: numaric or value error: invalid LOB locate"
body: <p>I'm getting this Oracle error <em>Ora-06502 PL/SQL: numeric or value error: invalid LOB locate</em> when two users try to commit at the same time.</p>
  
  <p>I actually tried hard to solve this problem but I couldn't find a solution.</p>
  
  <p>Please see the code for more details.</p>
  
  <pre><code>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 &gt; 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 &lt;&gt; 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 &lt;&gt; 0 THEN REM := :SYS.PRE_LEN/512;
  
   IF (REM - TRUNC(REM)) &lt;&gt; 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 &lt;= 1 THEN CTR := 1;
  
   SUB := LEN;
  
   ELSE IF (CTR - TRUNC(CTR)) &lt;&gt; 0 THEN CTR := TRUNC(CTR) + 1;
  
   END IF ;
  
   SUB := 1000;
  
   END IF;
  
   OPEN_TYPE := 1;
  
   J := 1;
  
   IF :tit.eol_swf &lt;&gt; '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 &lt;&gt; 0 THEN REM := :SYS.LEN/512;
  
   IF (REM - TRUNC(REM)) &lt;&gt; 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 &lt;&gt; 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;
  </code></pre>
  
  <p>This is a screenshot of the error message that displays to one of the users whilst the other user sees <em>Operation successful</em>.</p>
  
  <p><img src="https://i.stack.imgur.com/Jah5q.png" alt="enter description"></p>