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  |
 |
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 #515270 is a reply to message #515268] |
Sat, 09 July 2011 13:55  |
|
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 #515274 is a reply to message #515272] |
Sat, 09 July 2011 15:12 |
|
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
> 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 #515277 is a reply to message #515276] |
Sat, 09 July 2011 16:43 |
|
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 #515280 is a reply to message #515279] |
Sat, 09 July 2011 17:42 |
|
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 |
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun May 19 16:37:13 CDT 2013
Total time taken to generate the page: 1.07529 seconds
|
] 
Members
Search
Help
Register
Login
Home
