How to solve Oracle Subscript beyond count error when inserting data from arrays?

Question

I have developed a stored procedure like this :

        create or replace 
        PROCEDURE PROC_INS (
              GB_LD_ID   IN NUMBER,
              GB_CS_SP       IN TBL_CHAR,
              GB_UF_NMS    IN TBL_CHAR,
              GB_RW_BRS   IN TBL_NBR,
              GB_RT_SMS       IN TBL_CHAR,
              GB_LC_SD     IN TBL_CHAR)
        AS

          LV_ERROR   NUMBER(10);
          LV_MESSAGE varchar2(512);
          LV_TM_DI NUMBER(19);

        BEGIN


        select max(ID) into LV_TM_DI from TME_TAB;
        if(LV_TM_DI  IS NULL)THEN
           LV_TM_DI:=0;
        end if;

          FOR i IN GB_RW_BRS.FIRST..GB_RW_BRS.LAST
          LOOP
            BEGIN
              Insert into TME_TAB (ID,VS,PUCS,DT_CRD,NGL_ID,NAME,UD_DT,RW_NHG,TRF_SM,LS_FD)
              Values((LV_TM_DI+1),0,GB_CS_SP(i),SYSTIMESTAMP,GB_LD_ID,GB_UF_NMS(i),SYSTIMESTAMP,GB_RW_BRS(i),GB_RT_SMS(i),GB_LC_SD(i));
            END;
            LV_TM_DI := LV_TM_DI + 1;
          END LOOP;
          COMMIT;


        EXCEPTION
        WHEN OTHERS THEN
           IF(LV_MESSAGE IS NULL) THEN
            LV_ERROR    :=-20004;
            LV_MESSAGE  :=SQLERRM;
          END IF;
          RAISE_APPLICATION_ERROR (LV_ERROR,LV_MESSAGE);

        END PROC_INS;

This procedure inserts records by reading arrays of type TBL_CHAR and TBL_NBR. TBL_CHAR is defined as below :

create or replace TYPE "TBL_CHAR" AS TABLE OF VARCHAR2(100);

But while the insertion runs I get this error : ORA-20004: ORA-06533: Subscript beyond count .

Can some one please help me out here ?

All these TBL_CHAR, TBL_NBR, TBL_CHAR array size are same? Assume GB_RW_BRS size 10 but GB_CS_SP is 7 then it may throw error like this when you process 8 record in GB_RW_BRS — Gaj, Jul 18 '18 at 10:19
Can you give example call to your procedure (one that causes this error to raise) — BeRightBack, Jul 18 '18 at 11:55

score 0 · Answer 1 · answered Jul 18 '18 at 10:59

Looks like arrays have different number of elements. My test:

create or replace type tbl_char as table of varchar2(100);
create or replace type tbl_nbr as table of number;
create table tme_tab(id number(3), nbr number(5), chr varchar2(100));

create or replace procedure proc_ins ( i_id in number, i_nbr in tbl_nbr, i_chr in tbl_char ) as
    lv_tm_di number(6);
begin
    select nvl(max(ID), 0) into lv_tm_di from tme_tab;

    for i in i_nbr.first..i_nbr.last loop
        lv_tm_di := lv_tm_di + 1;
        begin
            insert into tme_tab (id, nbr, chr) values(lv_tm_di, i_nbr(i), i_chr(i));
        end;
    end loop;
end proc_ins;

... and this works:

begin 
    proc_ins(1, tbl_nbr(1, 7, 0), tbl_char('P', 'Q', 'R'));
end;

... and this does not work (ORA-06533):

begin 
    proc_ins(1, tbl_nbr(1, 7, 0, 9), tbl_char('P', 'Q', 'R'));
end;

Check size before inserting and throw exception:

    if i_nbr.count <> i_chr.count then
        raise_application_error (-20001, 'wrong array dimmensions');
    end if;

Or insert nulls for shorter array. Also your procedure uses counter when generating Id, consider using sequence instead, important for concurrent updates.

MT0 · Answer 2 · 2018-07-18T11:10:56.600

Use a SEQUENCE to get the unique IDs (rather than MAX).
Don't COMMIT in the procedure, do that from the calling block as it lets you perform multiple DML operations in a transaction and then roll them all back if there is an issue.
Find the LEAST number of entries in the collections and only insert that many rows.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE TME_TAB (
  ID int,
  VS int,
  PUCS char(100),
  DT_CRD timestamp,
  NGL_ID number,
  NAME char(100),
  UD_DT timestamp,
  RW_NHG number,
  TRF_SM char(100),
  LS_FD char(100)
)
/

CREATE TYPE TBL_CHAR IS TABLE OF CHAR(100)
/
CREATE TYPE TBL_NBR IS TABLE OF NUMBER
/

CREATE SEQUENCE TME_TAB__ID__SEQ
/

create or replace PROCEDURE PROC_INS (
  GB_LD_ID  IN NUMBER,
  GB_CS_SP  IN TBL_CHAR,
  GB_UF_NMS IN TBL_CHAR,
  GB_RW_BRS IN TBL_NBR,
  GB_RT_SMS IN TBL_CHAR,
  GB_LC_SD  IN TBL_CHAR)
AS
  LV_ERROR   NUMBER(10);
  LV_MESSAGE varchar2(512);
  LV_TM_DI NUMBER(19);
BEGIN
  FOR i IN 1 .. LEAST(
                  GB_CS_SP.COUNT,
                  GB_UF_NMS.COUNT,
                  GB_RW_BRS.COUNT,
                  GB_RT_SMS.COUNT,
                  GB_LC_SD.COUNT
                )
  LOOP
    Insert into TME_TAB (
      ID,            VS,           PUCS,         DT_CRD,
      NGL_ID,        NAME,         UD_DT,        RW_NHG,
      TRF_SM,        LS_FD
    ) Values(
      TME_TAB__ID__SEQ.NEXTVAL,  0, GB_CS_SP(i),  SYSTIMESTAMP,
      GB_LD_ID,      GB_UF_NMS(i), SYSTIMESTAMP, GB_RW_BRS(i),
      GB_RT_SMS(i),  GB_LC_SD(i)
    );
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    IF(LV_MESSAGE IS NULL) THEN
      LV_ERROR    :=-20004;
      LV_MESSAGE  :=SQLERRM;
    END IF;
    RAISE_APPLICATION_ERROR (LV_ERROR,LV_MESSAGE);
END PROC_INS;
/

Query 1:

BEGIN
  PROC_INS (
    GB_LD_ID  => 1,
    GB_CS_SP  => TBL_CHAR( 'sp_a', 'sp_b' ),
    GB_UF_NMS => TBL_CHAR( 'nms_a', 'nms_b' ),
    GB_RW_BRS => TBL_NBR( 1.1, 1.2 ),
    GB_RT_SMS => TBL_CHAR( 'sms_a', 'sms_b' ),
    GB_LC_SD  => TBL_CHAR( 'sd_a', 'sd_b' )
  );
  COMMIT;
END;

Results:

Query 2:

SELECT * FROM TME_TAB

Results:

| ID | VS |                                                                                                 PUCS |                     DT_CRD | NGL_ID |                                                                                                 NAME |                      UD_DT | RW_NHG |                                                                                               TRF_SM |                                                                                                LS_FD |
|----|----|------------------------------------------------------------------------------------------------------|----------------------------|--------|------------------------------------------------------------------------------------------------------|----------------------------|--------|------------------------------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------|
|  1 |  0 | sp_a                                                                                                 | 2018-07-18 11:07:31.827742 |      1 | nms_a                                                                                                | 2018-07-18 11:07:31.827742 |    1.1 | sms_a                                                                                                | sd_a                                                                                                 |
|  2 |  0 | sp_b                                                                                                 | 2018-07-18 11:07:31.828545 |      1 | nms_b                                                                                                | 2018-07-18 11:07:31.828545 |    1.2 | sms_b                                                                                                | sd_b                                                                                                 |

How to solve Oracle Subscript beyond count error when inserting data from arrays?

2 Answers2