ORA-06502: PL/SQL: numeric or value error: character string buffer too small

From: <dfosgate_at_llbean.com>
Date: 22 Feb 2005 06:08:56 -0800
Message-ID: <1109081336.005205.286300@f14g2000cwb.googlegroups.com>

Hi,

I'm processing a large file with the following data: 0AAA320000A
0AAA334000A
0AAA343000A
0AAA354000A
0AAA365000A
Total rows: 467381

When run PL/SQL get the following error: DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at "SYS.UTL_FILE", line 611
ORA-06512: at line 163
ORA-06512: at line 508

PL/SQL =====================================================

@/llbean/&1/interface/tomax

SET TERM OFF
SET SERVEROUTPUT ON SIZE 500000 DEFINE env = &1

SPOOL /llbean/&env/interface/import/pricelist.log;

DECLARE            lv_directory_txt VARCHAR2(29) := '/llbean/&env/interface/import';

	   lv_ifilename_txt   VARCHAR2(12)   := 'prlstdtl.dat';
	   lv_ifile_id_num    UTL_FILE.FILE_TYPE;
           lv_buffer_txt      VARCHAR2(15);
           lv_discrate_txt    VARCHAR2(12)   := 'discrate.dat';
	   lv_discrate_id_num UTL_FILE.FILE_TYPE;
           lv_discrate_rec    VARCHAR2(15);
           lv_upc_id          VARCHAR2(10);
           lv_sku_no          NUMBER(09);
	   lv_pct             NUMBER(5,2)    := 0.0;
	   lv_seq             NUMBER(12)     := 0;
	   line_out           VARCHAR2(1000);
	   recs_read          NUMBER(09)     := 0;
	   rows_inserted      NUMBER(09)     := 0;
	   rows_updated       NUMBER(09)     := 0;
	   lv_wng             NUMBER(09)     := 0;
	   lv_ts              VARCHAR2(32);
	   lv_cnt             NUMBER(12)     := 0;
	   lv_pstat           attribute_list_value.code_value%TYPE;
	   lv_nbr_hdrs        INTEGER        := 0;
	   lv_plid            price_list_hdr.price_list_id%TYPE;
	   lv_cgy             CHAR(1);
	   lv_name            price_list_hdr.name%TYPE;
	   ix                 INTEGER;
	   ixd                INTEGER;
	   ixh                INTEGER;
	   rc                 NUMBER(04)     := 0;
	   lv_dr_sz           INTEGER;
 	   lv_cmt_ctr         NUMBER(12)     := 10000;

	   TYPE array IS table of varchar(17) index by binary_integer;

           lv_dr              array;

--

• Declare CURSOR
  --
  CURSOR c_hdr IS SELECT price_list_id , name FROM price_list_hdr;

           c_hdr_rec price_list_hdr%ROWTYPE;

PROCEDURE prlstdtl IS

      BEGIN
--

• Load the discount rate array
  --
  lv_discrate_id_num := UTL_FILE.FOPEN(lv_directory_txt, lv_discrate_txt, 'R'); ix := 1; while ix is not null loop

	    	BEGIN <<read_file>>
	    	   UTL_FILE.GET_LINE(lv_discrate_id_num,
	    	   		     lv_discrate_rec);

	    	   EXCEPTION
	    	       WHEN NO_DATA_FOUND THEN
	    	   	  IF ix = 1 THEN
	    	   	     dbms_output.put_line ('ABEND - EMPTY DISCRATE FILE');
	     	   	     EXIT;
	    	   	  ELSE
	    	   	     dbms_output.put_line ('END OF DISCRATE FILE');
	    		     EXIT;
	    		  END IF;
	    	END read_file;

	        lv_dr(ix) := lv_discrate_rec;

 	    	dbms_output.put_line ('DISCRATE ARRAY REC ' ||
 	    	                       to_char(ix, '09999') ||
 	    	                       ' = ' ||
 	    	                       lv_dr(ix));

   	        lv_dr_sz := ix;
	        ix := ix + 1;
	  end loop;

	  IF ix = 1 THEN
	     rc:=8;
	     GOTO abend;
	  END IF;

--

• Get last used seq_no from price_list_dtl
  --
  BEGIN <<sel_seq>> SELECT MAX(seq_no) + 1 INTO lv_seq FROM PRICE_LIST_DTL ; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('ABEND - PRICE_LIST_DTL NOT FOUND'); rc:=8; GOTO abend; WHEN OTHERS THEN line_out := '** ERROR: '|| SQLCODE || ':'||SQLERRM||'**'; DBMS_OUTPUT.PUT_LINE(line_out); rc:=12; GOTO abend; END sel_seq;

      IF (lv_seq IS NULL or lv_seq < 1) THEN
         lv_seq:=1;
      END IF;

      line_out := 'Next PRLSTDTL SEQ_NO = ' || lv_seq;
      DBMS_OUTPUT.PUT_LINE(line_out);

--

• Get array size from count(*) of price_list_hdr
  --
  BEGIN <<sel_cnt>> SELECT count(*) INTO lv_nbr_hdrs FROM PRICE_LIST_HDR ; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('ABEND - PRICE_LIST_HDR NOT FOUND'); rc:=8; GOTO abend; WHEN OTHERS THEN line_out := '** ERROR: '|| SQLCODE || ':'||SQLERRM||'**'; DBMS_OUTPUT.PUT_LINE(line_out); rc:=12; GOTO abend; END sel_cnt;

      line_out := 'Number of Classes from PRICE_LIST_HDR = ' || lv_nbr_hdrs;

      DBMS_OUTPUT.PUT_LINE(line_out);

--

• Open the input data set for reading
  --
  lv_ifile_id_num := UTL_FILE.FOPEN(lv_directory_txt, lv_ifilename_txt, 'R'); LOOP <<toll>>

      line_out := 'AFTER OPEN RECORD';
      DBMS_OUTPUT.PUT_LINE(line_out);

--

• Initialize Buffer
  --
  lv_buffer_txt := NULL; lv_upc_id := NULL; lv_sku_no := NULL;

     line_out := 'BEFORE READ RECORD';
      DBMS_OUTPUT.PUT_LINE(line_out);

--

• Read File
  --
  BEGIN <<read_file>> UTL_FILE.GET_LINE(lv_ifile_id_num, lv_buffer_txt);

     line_out := 'AFTER READ RECORD';
      DBMS_OUTPUT.PUT_LINE(line_out);

--

• Exit LOOP at end of file
  --
  EXCEPTION WHEN NO_DATA_FOUND THEN EXIT; END read_file;

              recs_read := recs_read + 1;

              lv_upc_id := SUBSTR(lv_buffer_txt,1,10);
              lv_cgy    := SUBSTR(lv_buffer_txt,11,1);

--

• DEBUG
• DBMS_OUTPUT.PUT_LINE('UPC_ID = '||lv_upc_id);
• DBMS_OUTPUT.PUT_LINE('CGY = '||lv_cgy);
  --
  

--

• Get the SKU_NO from ITEMUPC using input UPC_ID
  --
  BEGIN <<sel_sku>> SELECT SKU_NO INTO lv_sku_no FROM ITEMUPC WHERE UPC_ID = lv_upc_id AND UPC_SEQ = 1;

		      EXCEPTION
			  WHEN NO_DATA_FOUND THEN
			       DBMS_OUTPUT.PUT_LINE('WARNING - SKU NOT FOUND for UPC = ' ||
lv_upc_id);
			       lv_wng:=lv_wng+1;
			       GOTO toll;
			  WHEN OTHERS THEN
			       line_out := '** ERROR: '|| SQLCODE || ':'||SQLERRM||'**';
			       DBMS_OUTPUT.PUT_LINE(line_out);
			       rc:=12;
			       GOTO abend;
	      END sel_sku;

--

• Get product status (Active or Liquidated) from ATTRIBUTE_LIST_VALUE
  --
  BEGIN <<sel_pstat>> SELECT attribute_list_value.code_value INTO lv_pstat FROM item_attribute_value , attribute_list_value WHERE item_attribute_value.sku_no = lv_sku_no and item_attribute_value.attribute_value = attribute_list_value.code_value and attribute_list_value.attribute_id = 197 ; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('ABEND - ITEM_ATTRIBUTE_VALUE etc. NOT FOUND'); rc:=8; GOTO abend; WHEN OTHERS THEN line_out := '** ERROR: '|| SQLCODE || ':'||SQLERRM||'**'; DBMS_OUTPUT.PUT_LINE(line_out); rc:=12; GOTO abend; END sel_pstat;
  --
  
• DEBUG
• line_out := 'UPC = ' || lv_upc_id || ' ATTR CODE VAL = '|| lv_pstat||' SKU = '||lv_sku_no;
• DBMS_OUTPUT.PUT_LINE(line_out);
  --
  

--

• Process each import record thru all price_list_hdr rows
  --
  ixh:=1; OPEN c_hdr; WHILE ixh <= lv_nbr_hdrs LOOP FETCH c_hdr INTO lv_plid , lv_name;
  --
  
• DEBUG
• DBMS_OUTPUT.PUT_LINE('curr cursor id = '|| lv_plid);
• DBMS_OUTPUT.PUT_LINE('curr cursor nm = '|| lv_name);
• DBMS_OUTPUT.PUT_LINE('Just After the FOR Statement');
• DBMS_OUTPUT.PUT_LINE('index = '||to_char(ixh, '099999999'));
• DBMS_OUTPUT.PUT_LINE('PLID = '||lv_plid);
  --
  

--

• Pull lv_pct (discount rate per catg w/in cls) from array
  --
  ixd := 1;
• while ixd is not null loop while ixd <= lv_dr_sz loop

	              IF (
			  lv_plid = SUBSTR(lv_dr(ixd),1,1)
			  AND
			  lv_cgy  = SUBSTR(lv_dr(ixd),6,1)
			 ) THEN

			lv_pct := SUBSTR(lv_dr(ixd),11,4);
			ix     := NULL;

		      END IF;

		      ixd := ixd + 1;
		      IF ixd > lv_dr_sz THEN ixd := NULL; END IF;

		   END LOOP;

--

• DEBUG
• DBMS_OUTPUT.PUT_LINE('CLS = '||lv_plid);
• DBMS_OUTPUT.PUT_LINE('CATG = '||lv_cgy);
• DBMS_OUTPUT.PUT_LINE('LV PCT = '||lv_pct);
  --
  

--

• If the PRICE_LIST_ID.SKU_NO exists, UPDATE, ELSE INSERT...
  --
  BEGIN <<sel_exist>> SELECT COUNT(*) INTO lv_cnt FROM price_list_dtl WHERE price_list_id = lv_plid AND sku_no = lv_sku_no ; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('ABEND - PRICE_LIST_DTL COUNT NOT FOUND'); rc:=8; GOTO abend; WHEN OTHERS THEN line_out := '** ERROR: '|| SQLCODE || ':'||SQLERRM||'**'; DBMS_OUTPUT.PUT_LINE(line_out); rc:=12; GOTO abend; END sel_exist;
  --
  
• DEBUG
• line_out := 'SKU EXISTS COUNT := ' || lv_cnt;
• DBMS_OUTPUT.PUT_LINE(line_out);
  --
  

                  IF (lv_plid = 1 AND lv_pstat <> 'A') THEN

                     lv_pct := 25.00;

                  END IF;

	          IF lv_cnt = 0 THEN

--

• DEBUG
• line_out := 'INSERTING := ' || 'RECORD# ' || recs_read || 'SKU '|| lv_sku_no;
• DBMS_OUTPUT.PUT_LINE(line_out);
  --
  

--

• IF PRICE_LIST_DTL SKU DOES NOT EXIST
• INSERT INTO PRICE_LIST_DTL for CATG within CLASS
  --
  BEGIN <<insert_prlstdtl>> INSERT INTO PRICE_LIST_DTL ( PRICE_LIST_ID ,SEQ_NO ,TYPE_CD ,DEPTGRP_NO ,DEPT_NO ,CLASS_NO ,LINE_NO ,SKU_NO ,PRICE_LEVEL ,PRICE_AMOUNT ,PRICE_PCT ,START_DATE ,END_DATE ,DATE_CREATED ,USER_CREATED ,DATE_MODIFIED ,USER_MODIFIED ) VALUES ( lv_plid , lv_seq , 'DFR' , NULL , NULL , NULL , NULL , lv_sku_no , 1 , NULL , lv_pct , NULL , NULL , sysdate , 'ROD046' , sysdate , 'ROD046' );

			       EXCEPTION WHEN OTHERS THEN
				    line_out := '** ERROR: '|| SQLCODE || ':'||SQLERRM||'**';
				    DBMS_OUTPUT.PUT_LINE(line_out);
				    rc:=12;
				    GOTO abend;

			   END insert_prlstdtl;

			   rows_inserted := rows_inserted + 1;

	                   lv_seq := lv_seq + 1;
                 ELSE

--

• DEBUG
• line_out := 'UPDATING := ' || 'RECORD# ' || recs_read || 'UPC '|| lv_upc_id;
• DBMS_OUTPUT.PUT_LINE(line_out);
  --
  

--

• IF PRICE_LIST_DTL SKU EXISTS:
• Update PRICE_LIST_DTL for CATG within CLASS
  --
  BEGIN <<upd_pld>> UPDATE PRICE_LIST_DTL SET PRICE_PCT = lv_pct , DATE_MODIFIED = sysdate , USER_MODIFIED = 'ROD046' WHERE PRICE_LIST_ID = lv_plid AND SKU_NO = lv_sku_no; EXCEPTION WHEN OTHERS THEN line_out := '** ERROR: '|| SQLCODE || ':'||SQLERRM||'**'; DBMS_OUTPUT.PUT_LINE(line_out); rc:=12; GOTO abend; END upd_pld;

                           rows_updated := rows_updated + 1;

                 END IF;

                 ixh:=ixh+1;

              END LOOP;

--

• Close HDR Cursor
  --
  CLOSE c_hdr;
  --
  
• Commit changes to database
  --
  BEGIN <<commit_changes>>

               IF ((rows_updated + rows_inserted) >= lv_cmt_ctr)
                THEN
                  COMMIT;
                  line_out := 'COMMITTED CHANGES TO Data Base - Records

read = ' || recs_read ||' Rows processed = ' || (rows_updated + rows_inserted);

                  DBMS_OUTPUT.PUT_LINE(line_out);
                  lv_ts := 'Time is: '||
to_char(sysdate,'yyyy-mm-dd_hh:mm:ss');
                  DBMS_OUTPUT.PUT_LINE(lv_ts);
                  lv_cmt_ctr := lv_cmt_ctr + 10000;
               END IF;
            END commit_changes;

--

• DEBUG
• DBMS_OUTPUT.PUT_LINE('FOLLOWING THE HDR CSR LOOP');
  --
  

          END LOOP toll;
--

• DEBUG
• DBMS_OUTPUT.PUT_LINE('FOLLOWING THE toll LOOP');
  --
  

<<abend>>
--

• Audit Stats
  --
  BEGIN <<audit_stats>> IF rc = 0

                  THEN
		  line_out := 'Recs Read: '|| recs_read || ' - Rows Inserted: '||
rows_inserted;
		  DBMS_OUTPUT.PUT_LINE(line_out);
		  line_out := 'Recs Read: '|| recs_read || ' - Rows Updated: '||
rows_updated;
		  DBMS_OUTPUT.PUT_LINE(line_out);
                  IF ((rows_inserted + rows_updated) <> ((recs_read *
lv_nbr_hdrs) - (lv_wng * lv_nbr_hdrs))) THEN
		     rc:=8;
		     line_out := 'ABEND - Rows INSERTED + Rows UPDATED does not equal
records READ * HDRs (minus warnings * HDRs)';
		     DBMS_OUTPUT.PUT_LINE(line_out);
		     line_out := 'Rows processed('||(rows_inserted +

rows_updated)||') should equal records READ('||recs_read||') * HDRs('||lv_nbr_hdrs||') - minus warnings * HDRs('||(lv_wng * lv_nbr_hdrs)||')';

		     DBMS_OUTPUT.PUT_LINE(line_out);
		  ELSE
		     line_out := 'Rows processed('||(rows_inserted +

rows_updated)||') should equal records READ('||recs_read||') * HDRs('||lv_nbr_hdrs||') - minus warnings * HDRs('||(lv_wng * lv_nbr_hdrs)||')';

		     DBMS_OUTPUT.PUT_LINE(line_out);
		     line_out := 'Last PRLSTDTL SEQ_NO = ' || (lv_seq);
		     DBMS_OUTPUT.PUT_LINE(line_out);
		  END IF;
	      END IF;
          END audit_stats;

/*
-- -- Close (all) file(s)

*/

          BEGIN <<close_files>>
              UTL_FILE.FCLOSE_ALL;
          END close_files;

          line_out := 'FILES CLOSED';
	  DBMS_OUTPUT.PUT_LINE(line_out);

--

• Commit changes to database
  --
  BEGIN <<save_changes>> IF rc = 0 THEN COMMIT; line_out := 'COMMITTED CHANGES TO Data Base'; DBMS_OUTPUT.PUT_LINE(line_out); ELSE ROLLBACK; line_out := 'ROLLBACK CHANGES TO Data Base'; DBMS_OUTPUT.PUT_LINE(line_out); END IF; END save_changes;
  --
  END prlstdtl; /* ------------------------------------------------------ */ /* Main Procedure /* ------------------------------------------------------ */ BEGIN line_out := '**'; DBMS_OUTPUT.PUT_LINE(line_out); lv_ts := 'BEGIN: '|| to_char(sysdate,'yyyy-mm-dd_hh:mm:ss'); DBMS_OUTPUT.PUT_LINE(lv_ts);

    prlstdtl;

    lv_ts := 'END : '|| to_char(sysdate,'yyyy-mm-dd_hh:mm:ss');

    DBMS_OUTPUT.PUT_LINE(lv_ts);
    line_out := '**';
    DBMS_OUTPUT.PUT_LINE(line_out);
    line_out := 'RC = ' || rc;
    DBMS_OUTPUT.PUT_LINE(line_out);

END;
/
SPOOL OFF; exit (rc);
@/l Received on Tue Feb 22 2005 - 08:08:56 CST