1 2 Previous Next 27 Replies Latest reply on Nov 2, 2012 3:18 AM by 936666

    Error: ORA-06533: Subscript beyond count

    936666
      when i run the below command;

      ERROR at line 1:
      ORA-06533: Subscript beyond count
      ORA-06512: at line 10

      sp_table will be like

      SPLITID DELIMITERLENGTH
      ---------- ---------------
      1 1
      2 1
      3 1
      4 1

      declare strx varchar2(4000) :='11111111111111111111111111111111111111111111111111111111111111111111111111111111';
      delarray np_type:=np_type(1,2,3,4,5);
      execstr varchar2(4000);
      cn cn_type :=cn_type();
      cxn cxn_type :=cxn_type();
      xpos number:=1;

      begin
      for i in 1..5 loop
      select Delimiterlength into cn(i) from sp_table where Splitid = delarray(i);
      select substr(strx,xpos,cn(i)) into cxn(i) from dual;
      execstr:='insert into test values(' || cxn(i) || ',';
      xpos:=length(cxn(i));
      end loop;
      execstr:=substr(execstr,1,length(execstr)-1);
      execstr:=execstr || ')';
      dbms_output.put_line(execstr);
      --execute immediate execstr;
      end;
      /
        • 1. Re: Error: ORA-06533: Subscript beyond count
          Marwim
          Hello,
          DECLARE
              strx VARCHAR2(4000) :='11111111111111111111111111111111111111111111111111111111111111111111111111111111';
              delarray np_type    :=np_type(1,2,3,4,5);
              execstr VARCHAR2(4000);
              cn cn_type   :=cn_type();
              cxn cxn_type :=cxn_type();
              xpos NUMBER  :=1;
          BEGIN
              FOR i IN 1..5
              LOOP
                  SELECT Delimiterlength
                  INTO cn(i)
                  FROM sp_table
                  WHERE Splitid = delarray(i);
                  SELECT SUBSTR(strx,xpos,cn(i))
                  INTO cxn(i)
                  FROM dual;
                  execstr:='insert into test values(' || cxn(i) || ',';
                  xpos   :=LENGTH(cxn(i));
              END LOOP;
              execstr:=SUBSTR(execstr,1,LENGTH(execstr)-1);
              execstr:=execstr || ')';
              dbms_output.put_line(execstr);
              --execute immediate execstr;
          END;
          /
          cn_type seems to be a nested table defined on db-level. You will have to EXTEND it in order to assign a value.
          http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#i27396

          Regards
          Marcus
          • 2. Re: Error: ORA-06533: Subscript beyond count
            Marwim
            SELECT SUBSTR(strx,xpos,cn(i))
            INTO cxn(i)
            FROM dual;
            should be written as
            cxn(i) := SUBSTR(strx,xpos,cn(i));
            No need to use dual table.
            • 3. Re: Error: ORA-06533: Subscript beyond count
              Girish Sharma
              Marwim,

              Actually OP has tried to get help from below link too :

              Split without delimiter based on position

              I tried my best to help him, but in the last I suggested him to open a new thread in this forum. Just have a look, what OP needs.. :)

              Regards
              Girish Sharma
              • 4. Re: Error: ORA-06533: Subscript beyond count
                Marwim
                Something like this?
                SET SERVEROUTPUT ON
                DROP TABLE sp_table;
                CREATE TABLE sp_table(splitid NUMBER,delimiterlength NUMBER);
                INSERT INTO sp_table values(1,4);
                INSERT INTO sp_table values(2,7);
                INSERT INTO sp_table values(3,9);
                INSERT INTO sp_table values(4,1);
                INSERT INTO sp_table values(5,2);
                INSERT INTO sp_table values(6,6);
                COMMIT;
                
                DECLARE
                    TYPE varchar2_table IS TABLE OF VARCHAR2(120)
                        INDEX BY BINARY_INTEGER;
                    TYPE number_table IS TABLE OF NUMBER
                        INDEX BY BINARY_INTEGER;
                    delarray    number_table;
                    cn          number_table;
                    cxn         varchar2_table;
                    xpos        NUMBER  :=1;
                    strx        VARCHAR2(4000) :='11111111111111111111111111111111111111111111111111111111111111111111111111111111';
                    execstr     VARCHAR2(4000);
                BEGIN
                    delarray(1) := 1;
                    delarray(2) := 2;
                    delarray(3) := 3;
                    delarray(4) := 4;
                    delarray(5) := 5;
                
                    execstr := 'insert into test values(';
                
                    FOR i IN 1..delarray.COUNT LOOP
                
                        SELECT  delimiterlength
                        INTO    cn(i)
                        FROM    sp_table
                        WHERE   splitid = delarray(i);
                
                        cxn(i)  := SUBSTR(strx,xpos,cn(i));
                        execstr :=execstr || cxn(i) || ',';
                        xpos    :=LENGTH(cxn(i));
                    END LOOP;
                
                    execstr := RTRIM(execstr,',')|| ')';
                    dbms_output.put_line(execstr);
                    --execute immediate execstr;
                END;
                /
                
                insert into test values(1111,1111111,111111111,1,11)
                I used associative arrays, so I don't have to extend them.

                Edited by: Marwim on 25.10.2012 10:17
                • 5. Re: Error: ORA-06533: Subscript beyond count
                  Girish Sharma
                  Marwim,

                  1.There are 80 columns in the test table in which that input string will be split and then data will be inserted into the table.
                  2.OP seems want to something like execute procedure which accepts input string of numbers and may be there is need of generate dynamic insert statement, based upon splitids supplied by varray type variable.
                  3.I am not getting what is the problem in my solution which I tried in above forum link.
                  4.It would be nice to have respective exception handling too; so that if there is short of length of string or if string data remained for insertion, there should be some message too (but this I think should be what OP needs though)

                  Regards
                  Girish Sharma
                  • 6. Re: Error: ORA-06533: Subscript beyond count
                    Marwim
                    OP seems want to something like
                    Then he should tell us. Many of us will help him to solve a problem, but I don't want to spend time as a volunteer to find solutions for maybe-problems.
                    • 7. Re: Error: ORA-06533: Subscript beyond count
                      936666
                      Hi thanks

                      After increasing my strx size for 110 columns i am getting this error in execute immediate execstr; where as in dbms_output.put_line(execstr); i am able to see the output

                      I tried increasing execstr VARCHAR2(4000) to execstr VARCHAR2(32000); even though there is result,

                      delarray(1) := 1;
                      delarray(2) := 2;
                      .
                      .
                      .
                      delarray(110) := 110;

                      ERROR at line 1:
                      ORA-00972: identifier is too long
                      ORA-06512: at line 31

                      Thanks!
                      • 8. Re: Error: ORA-06533: Subscript beyond count
                        Marwim
                        ORA-06512: at line 31
                        I don't know your line 31. Why don't you show us your code?
                        When posting it, please enclose it in
                         tags to keep the formatting.                                                                                                                                                                                                                                                                                                                                
                        • 9. Re: Error: ORA-06533: Subscript beyond count
                          936666
                           DECLARE
                              TYPE varchar2_table IS TABLE OF VARCHAR2(120)
                                  INDEX BY BINARY_INTEGER;
                              TYPE number_table IS TABLE OF NUMBER
                                  INDEX BY BINARY_INTEGER;
                              delarray    number_table;
                              cn          number_table;
                              cxn         varchar2_table;
                              xpos        NUMBER  :=1;
                              strx        VARCHAR2(4000) :='123456789';
                          
                              execstr     VARCHAR2(500);
                              cnt     Number(5);
                                  
                          BEGIN
                          SELECT  Count(delimiterlength) into cnt from sp_table;
                              execstr := 'insert into test values(';
                              FOR i IN 1..80 LOOP
                                  delarray(i) := i;
                                  SELECT  delimiterlength
                                  INTO    cn(i)
                                  FROM    sp_table
                                  WHERE   splitid = delarray(i);
                                  cxn(i)  := SUBSTR(strx,xpos,cn(i));
                                  execstr :=execstr || cxn(i) || ',';
                                  xpos    :=cn(i) + xpos;
                              END LOOP;
                           
                              execstr := RTRIM(execstr,',')|| ')';
                             -- dbms_output.put_line(execstr);
                             execute immediate execstr; **** line 31
                          END;
                          /  
                          • 10. Re: Error: ORA-06533: Subscript beyond count
                            Marwim
                            I don't have the data from sp_table, but could it be, that strx simply has not enough values, so the generated DML is invalid?

                            If you can see it from the dbms_output you can run it and see, whether it is valid.
                            • 11. Re: Error: ORA-06533: Subscript beyond count
                              936666
                              Yes dbms_output is valid i am able to see the query formed in such a way we can insert the data from the output.

                              Thanks!
                              • 12. Re: Error: ORA-06533: Subscript beyond count
                                Marwim
                                Well, my Crystal Ball is out of order. Could you please post the generated statement?
                                • 13. Re: Error: ORA-06533: Subscript beyond count
                                  936666
                                  insert into
                                  test(Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col1
                                  3,Col14,Col15,Col16,Col17,Col18,Col19,Col20,Col21,Col22,Col23,Col24,Col25,Col26,
                                  Col27,Col28,Col29,Col30,Col31,Col32,Col33,Col34,Col35,Col36,Col37,Col38,Col39,Co
                                  l40,Col41,Col42,Col43,Col44,Col45,Col46,Col47,Col48,Col49,Col50,Col51,Col52,Col5
                                  3,Col54,Col55,Col56,Col57,Col58,Col59,Col60,Col61,Col62,Col63,Col64,Col65,Col66,
                                  Col67,Col68,Col69,Col70,Col71,Col72,Col73,Col74,Col75,Col76,Col77,Col78,Col79,Co
                                  l80,Col81,Col82,Col83,Col84,Col85,Col86,Col87,Col88,Col89,Col90,Col91,Col92,Col9
                                  3,Col94,Col95,Col96,Col97,Col98,Col99,Col100,Col101,Col102,Col103,Col104,Col105,
                                  Col106,Col107,Col108,Col109,Col110)
                                  values(1,0,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0
                                  ,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1
                                  ,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,1,1) 
                                  • 14. Re: Error: ORA-06533: Subscript beyond count
                                    Marwim
                                    The output you provided cannot be produced by the code from the previous statement.

                                    Please post the exact statement you executed and the exact output of the generated statement, together with the exact error message.
                                    1 2 Previous Next