Home » SQL & PL/SQL » SQL & PL/SQL » Error: ORA-06533 subscript beyond count (oracle 9i)
Error: ORA-06533 subscript beyond count [message #515264] Sat, 09 July 2011 13:06 Go to next message
hemz
Messages: 11
Registered: July 2011
Location: India
Junior Member
Hi,
I am trying to create a varray of strings using elements returned from a nested table, but getting ora-06533 error, can someone pls help me over come this
I am using a script as below:

DECLARE
    time_start     NUMBER;
    time_end       NUMBER;
    TYPE text_nt
      IS TABLE OF cpw_account_info%ROWTYPE;
    v_ename_nt     TEXT_NT;
    TYPE term_acc IS VARRAY(5000) OF VARCHAR2(5000);
    term_acc_stmnt TERM_ACC; --:=term_acc();
    CURSOR c_emp IS
      SELECT *
      FROM   cpw_account_info;
BEGIN
    OPEN c_emp;

    time_start := dbms_utility.get_time;

    LOOP
        FETCH c_emp BULK COLLECT INTO v_ename_nt LIMIT 500;

        dbms_output.Put_line('v_eName_nt.count: '
                             ||v_ename_nt.COUNT);

        dbms_output.Put_line('=====================================');

        FOR i IN 1..v_ename_nt.COUNT LOOP
            dbms_output.Put_line(V_ename_nt(i).account_num
                                 ||','
                                 ||V_ename_nt(i).end_dat
                                 ||','
                                 ||V_ename_nt(i).termination_reason_id
                                 ||','
                                 ||V_ename_nt (i).erly_term_chrg_boo);

            term_acc_stmnt := Term_acc();

            term_acc_stmnt.Extend(v_ename_nt.COUNT);

            -- create array of strings using varray
            term_acc_stmnt := Term_acc('ACC.TERMACC('
                                       || V_ename_nt(i).account_num
                                       ||','
                                       || V_ename_nt(i).end_dat
                                       ||','
                                       || V_ename_nt(i).termination_reason_id
                                       ||','
                                       || V_ename_nt(i).erly_term_chrg_boo
                                       || ');');

            dbms_output.Put_line('Terminate API String: '
                                 || Term_acc_stmnt(i));
        END LOOP;

        exit WHEN c_emp%notfound;
    END LOOP;

    time_end := dbms_utility.get_time;

    dbms_output.Put_line('time_start: '
                         ||time_start);

    dbms_output.Put_line('time_end: '
                         ||time_end);

    dbms_output.Put_line('time elapsed in ms: '
                         ||( time_end - time_start ) / 100 * 1000);

    CLOSE c_emp;
END;

/  


DBMS_OUTPUT:
v_eName_nt.count: 5
=====================================
123455,09-JUL-11,1,F
Terminate API String: ACC.TERMACC(123455,09-JUL-11,1,F);
123456,09-JUL-11,1,F
**ora-06533 subscript beyond count**
/ the script errors out with this error when i'm expecting the script to proceed and produce strings like*
Terminate API String: ACC.TERMACC(99999,09-JUL-11,1,F);
Terminate API String: ACC.TERMACC(11111,09-JUL-11,1,F);
*Terminate API String: ACC.TERMACC(22222,09-JUL-11,1,F); /

I guess I am makin a mistak of accessin varray within a nested table loop, your suggestions please

My table has data in the below format
ACCOUNT_NUM END_DAT TERMINATION_REASON_ID ERLY_TERM_CHRG_BOO
123455 09/07/2011 1 F
123456 09/07/2011 1 F
123457 09/07/2011 1 F
123458 09/07/2011 1 F
123459 09/07/2011 1 F

What am i trying to do?
I am trying to fetch this data through a nested table and prepare a set of strings(api calls) thru varray
Execute the prepared varray thru execute immediate statement

Does this give clarity of what i am trying to do?
* < code tags > added & code formatted by BlackSwan. please do so yourself in the future

[Updated on: Sat, 09 July 2011 13:15] by Moderator

Report message to a moderator

Re: Error: ORA-06533 subscript beyond count [message #515265 is a reply to message #515264] Sat, 09 July 2011 13:11 Go to previous messageGo to next message
BlackSwan
Messages: 23162
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Error: ORA-06533 subscript beyond count [message #515266 is a reply to message #515265] Sat, 09 July 2011 13:19 Go to previous messageGo to next message
hemz
Messages: 11
Registered: July 2011
Location: India
Junior Member
Hi BlackSwan,
Sorry for that, is that any better now??
Re: Error: ORA-06533 subscript beyond count [message #515267 is a reply to message #515266] Sat, 09 July 2011 13:23 Go to previous messageGo to next message
BlackSwan
Messages: 23162
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
#9?
Re: Error: ORA-06533 subscript beyond count [message #515268 is a reply to message #515267] Sat, 09 July 2011 13:31 Go to previous messageGo to next message
hemz
Messages: 11
Registered: July 2011
Location: India
Junior Member
Hi BlackSwan,
I am really sorry, I am new to this forum, the code seems to be formatted by you.
I have run through the guide lines and seems to have met most of the rules, is there something specific that you are pointing at?

Please do share
Re: Error: ORA-06533 subscript beyond count [message #515270 is a reply to message #515268] Sat, 09 July 2011 13:55 Go to previous messageGo to next message
hemz
Messages: 11
Registered: July 2011
Location: India
Junior Member
The table and rows within the table I am using

CREATE TABLE CPW_ACCOUNT_INFO
(
  ACCOUNT_NUM            VARCHAR2(20),
  END_DAT                DATE,
  TERMINATION_REASON_ID  NUMBER(9),
  ERLY_TERM_CHRG_BOO     VARCHAR2(1)
)


SET DEFINE OFF;
Insert into CPW_ACCOUNT_INFO
   (ACCOUNT_NUM, END_DAT, TERMINATION_REASON_ID, ERLY_TERM_CHRG_BOO)
Values
   ('123455', TO_DATE('07/09/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 'F');
Insert into CPW_ACCOUNT_INFO
   (ACCOUNT_NUM, END_DAT, TERMINATION_REASON_ID, ERLY_TERM_CHRG_BOO)
Values
   ('123456', TO_DATE('07/09/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 'F');
Insert into IST.CPW_ACCOUNT_INFO
   (ACCOUNT_NUM, END_DAT, TERMINATION_REASON_ID, ERLY_TERM_CHRG_BOO)
Values
   ('123457', TO_DATE('07/09/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 'F');
Insert into CPW_ACCOUNT_INFO
   (ACCOUNT_NUM, END_DAT, TERMINATION_REASON_ID, ERLY_TERM_CHRG_BOO)
Values
   ('123458', TO_DATE('07/09/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 'F');
Insert into IST.CPW_ACCOUNT_INFO
   (ACCOUNT_NUM, END_DAT, TERMINATION_REASON_ID, ERLY_TERM_CHRG_BOO)
Values
   ('123459', TO_DATE('07/09/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 'F');
COMMIT;

[Updated on: Sat, 09 July 2011 13:56]

Report message to a moderator

Re: Error: ORA-06533 subscript beyond count [message #515272 is a reply to message #515270] Sat, 09 July 2011 14:46 Go to previous messageGo to next message
BlackSwan
Messages: 23162
Registered: January 2009
Senior Member
remove line below
dbms_output.Put_line('Terminate API String: ' || Term_acc_stmnt(i));

>Execute the prepared varray thru execute immediate statement
Did I miss something?
Re: Error: ORA-06533 subscript beyond count [message #515274 is a reply to message #515272] Sat, 09 July 2011 15:12 Go to previous messageGo to next message
hemz
Messages: 11
Registered: July 2011
Location: India
Junior Member
Hey BS,
I have just modified the code by:
> replcaing varray by another nested table
> initialize second nested table to
 term_acc:=term_acc('0'); 

> additonal for loop

declare
time_start number;
time_end number;
 
type text_nt is table of cpw_account_info%rowtype;
v_eName_nt text_nt;
 
type term_acc is table of varchar2(5000);
[b]term_acc_stmnt term_acc:=term_acc('0'); [/b]
 
cursor c_emp is select * from cpw_account_info;
 
  begin
   open c_emp;
   time_start:=dbms_utility.get_time;
    loop
     fetch c_emp bulk collect into v_eName_nt limit 500;
             DBMS_OUTPUT.put_line('v_eName_nt.count: '||v_eName_nt.count);
             DBMS_OUTPUT.put_line('=====================================');
     for i in 1..v_eName_nt.count
      loop
        --DBMS_OUTPUT.put_line(v_ename_nt(i).ACCOUNT_NUM||','||v_ename_nt(i).END_DAT||','||
v_ename_nt(i).TERMINATION_REASON_ID||','||
v_ename_nt(i).ERLY_TERM_CHRG_BOO);
        --term_acc_stmnt:=term_acc();
        [b]for x in 1..term_acc_stmnt.last
         loop
            term_acc_stmnt.extend(v_eName_nt.count);[/b]
 
            term_acc_stmnt:=term_acc('GNVACC.TERMINATEACCOUNT1NC('||''''||
                                 v_ename_nt(i).ACCOUNT_NUM ||''''||','||
                                 to_date(v_ename_nt(i).END_DAT,'dd-mon-yyyy') ||','||''''||
                                 v_ename_nt(i).TERMINATION_REASON_ID||''''||','||''''||
                                 v_ename_nt(i).ERLY_TERM_CHRG_BOO ||''''|| ');'
                                 );
            [b]dbms_output.put_line('Terminate API String: '|| term_acc_stmnt(x));
         end loop;[/b]
       end loop;
    exit when c_emp%NOTFOUND;
    end loop;
    time_end:=dbms_utility.get_time;
   
    dbms_output.put_line('time_start: '||time_start);
    dbms_output.put_line('time_end: '||time_end);
    dbms_output.put_line('time elapsed in ms: '||(time_end-time_start)/100*1000);
   close c_emp;
  end;
/


the script now seems to return the strings i wanted, but is this rite way to do it?, as I think i have unnecessarily initialized and added an extra loop.

dbms_output:
v_eName_nt.count: 5
=====================================
Terminate API String: GNVACC.TERMINATEACCOUNT1NC('123455',09-JUL-11,'1','F');
Terminate API String: GNVACC.TERMINATEACCOUNT1NC('123456',09-JUL-11,'1','F');
Terminate API String: GNVACC.TERMINATEACCOUNT1NC('123457',09-JUL-11,'1','F');
Terminate API String: GNVACC.TERMINATEACCOUNT1NC('123458',09-JUL-11,'1','F');
Terminate API String: GNVACC.TERMINATEACCOUNT1NC('123459',09-JUL-11,'1','F');


The changes are inidicated in bold

[Updated on: Sun, 10 July 2011 00:40] by Moderator

Report message to a moderator

Re: Error: ORA-06533 subscript beyond count [message #515275 is a reply to message #515274] Sat, 09 July 2011 15:16 Go to previous messageGo to next message
hemz
Messages: 11
Registered: July 2011
Location: India
Junior Member
Many thanks BS, but i have to prepare the string of statements before using the execute immediate statement
Should my second approach be anything wrong?, or be anything that has to do with costly performance?
Re: Error: ORA-06533 subscript beyond count [message #515276 is a reply to message #515275] Sat, 09 July 2011 16:20 Go to previous messageGo to next message
BlackSwan
Messages: 23162
Registered: January 2009
Senior Member
>the script now seems to return the strings i wanted, but is this rite way to do it?

I have NO idea to what "it" refers.

Part of me suspects you are making three left turns instead of a single right turn.

What problem are you really trying to solve?
How will you, I, or anyone recognize a correct solution has been posted?
Re: Error: ORA-06533 subscript beyond count [message #515277 is a reply to message #515276] Sat, 09 July 2011 16:43 Go to previous messageGo to next message
hemz
Messages: 11
Registered: July 2011
Location: India
Junior Member
Alrite there i seem to feel the same, there should have been an easy way around it, but i am taking lot of turns

I want an array of strings to be returned in the below manner
Terminate API String: GNVACC.TERMINATEACCOUNT1NC('123455',09-JUL-11,'1','F');
Terminate API String: GNVACC.TERMINATEACCOUNT1NC('123456',09-JUL-11,'1','F');
Terminate API String: GNVACC.TERMINATEACCOUNT1NC('123457',09-JUL-11,'1','F');
Terminate API String: GNVACC.TERMINATEACCOUNT1NC('123458',09-JUL-11,'1','F');
Terminate API String: GNVACC.TERMINATEACCOUNT1NC('123459',09-JUL-11,'1','F');


> The "it" that i am talking about is adding another for loop within the original code
for x in 1..term_acc_stmnt.last
         loop
            term_acc_stmnt.extend(v_eName_nt.count);
            ...
            ...
            ...
            dbms_output.put_line('Terminate API String: '|| term_acc_stmnt(x));
         end loop;

> This actually worked returning me the strings I expected without error, but I guess there should have been a better way to do it, which I am failing

Do you get a hang of it now Swan?
Re: Error: ORA-06533 subscript beyond count [message #515278 is a reply to message #515277] Sat, 09 July 2011 16:48 Go to previous messageGo to next message
hemz
Messages: 11
Registered: July 2011
Location: India
Junior Member
The actual requirement I am trying to achieve is:
1> bulk collect all the values in table cpw_account_info into a collection_type1
2> prepare a string list of api calls through another collection_type2 using values from collection_type1
3> execute immediate the "string list of api calls" prepared prepared by "2>"

Guess I have got myself into too many twists and turns around for a simple logic and not able to get out of it now Sad
Re: Error: ORA-06533 subscript beyond count [message #515279 is a reply to message #515278] Sat, 09 July 2011 17:24 Go to previous messageGo to next message
BlackSwan
Messages: 23162
Registered: January 2009
Senior Member
DECLARE
    CURSOR c_emp IS
      SELECT *
      FROM   cpw_account_info;
BEGIN
    FOR emp_rec IN c_emp LOOP
        gnvacc.Terminateaccount1nc(emp_rec.account_num,
                                  emp_rec.end_dat, 
                                  emp_rec.termination_reason_id,
                                  emp_rec.erly_term_chrg_boo);
    END LOOP;
END;

/  
Re: Error: ORA-06533 subscript beyond count [message #515280 is a reply to message #515279] Sat, 09 July 2011 17:42 Go to previous messageGo to next message
hemz
Messages: 11
Registered: July 2011
Location: India
Junior Member
Hey Thanks BS,
I was thinking this way of fetching and passing sequentially,
may cause a performance overhead and hence opted for bulk collect and varray to prepare dynamic api calls.
Do you think the same?, should it help if i used oracle parellism to achieve better performance?

I was however able to get to a way through what i was trying
declare
    time_start number;
    time_end number;
    type term_acc is varray(5000) of varchar2(5000);
    term_acc_stmnt_limit term_acc;
    term_acc_stmnt term_acc :=term_acc();
    v_ind number;
    cursor c_emp is select ACCOUNT_NUM||','||END_DAT||','||TERMINATION_REASON_ID||','||ERLY_TERM_CHRG_BOO 
from cpw_account_info;
begin
    open c_emp;
    time_start:=dbms_utility.get_time;
    loop
      fetch c_emp bulk collect into term_acc_stmnt_limit limit 500;
      DBMS_OUTPUT.put_line('term_acc_stmnt_limit.count: '||term_acc_stmnt_limit.count);
      DBMS_OUTPUT.put_line('=====================================');
      v_ind := term_acc_stmnt.count;
      term_acc_stmnt.extend(term_acc_stmnt_limit.count);
      for i in 1..term_acc_stmnt_limit.count loop
        term_acc_stmnt(v_ind + i) := term_acc_stmnt_limit(i);
        dbms_output.put_line('Terminate API String: '|| term_acc_stmnt(v_ind + i));
      end loop;
      exit when c_emp%NOTFOUND;
    end loop;
    time_end:=dbms_utility.get_time;
    dbms_output.put_line('time_start: '||time_start);
    dbms_output.put_line('time_end: '||time_end);
    dbms_output.put_line('time elapsed in ms: '||(time_end-time_start)/100*1000);
    close c_emp;
end;
/ 

[Updated on: Sun, 10 July 2011 00:41] by Moderator

Report message to a moderator

Re: Error: ORA-06533 subscript beyond count [message #515281 is a reply to message #515280] Sat, 09 July 2011 17:49 Go to previous messageGo to next message
BlackSwan
Messages: 23162
Registered: January 2009
Senior Member
as a learning exercise I suggest you SQL_TRACE both implementations to see which performs better & why.

Your code will end up HARD PARSING every procedure call all the while doing many context switches between PL/SQL & SQL engines.
Re: Error: ORA-06533 subscript beyond count [message #515282 is a reply to message #515281] Sat, 09 July 2011 18:00 Go to previous message
hemz
Messages: 11
Registered: July 2011
Location: India
Junior Member
I will check on that
Thank you BS..Its been a nice start on this forum Smile
Previous Topic: ORA-27369: job of type EXECUTABLE failed with exit code: Exchange full
Next Topic: Encountered the symbol "end-of-file" when expecting one of the following: ;(3 Merged)
Goto Forum:
  


Current Time: Mon Dec 22 19:46:38 CST 2014

Total time taken to generate the page: 0.09989 seconds