Hi I have a basic procedure which bulk collects the results of a select statement into a table array. I then print out one line to show that it has worked..
Code (SQL):
CREATE OR REPLACE PROCEDURE use_var
IS
TYPE r_tab IS TABLE OF msf010%rowtype;
rr_tab r_tab;
BEGIN
SELECT msf010.* bulk collect INTO
rr_tab
FROM msf010 WHERE table_type='E6' ;
DBMS_OUTPUT.PUT_LINE(RR_TAB(1).TABLE_CODE);
END;
The procedure works fine but I wanted to be able to pass a parameter to the procedure that would form part of the where clause as below..
Code (SQL):
CREATE OR REPLACE PROCEDURE use_var2 (rob_var2 IN varchar2)
IS
TYPE r_tab IS TABLE OF msf010%rowtype;
rr_tab r_tab;
BEGIN
SELECT msf010.* bulk collect INTO
rr_tab
FROM msf010 WHERE table_type=rob_var2 ;
DBMS_OUTPUT.PUT_LINE(RR_TAB(1).TABLE_CODE);
END;
EXEC use_var2('E6')
Which produces the following error ouput..
Error starting at line 14 in command:
exec use_var2('E6')
Error report:
ORA-06533: Subscript beyond count
ORA-06512: at "RREES4.USE_VAR2", line 11
ORA-06512: at line 1
06533. 00000 - "Subscript beyond count"
*Cause: An in-limit subscript was greater than the count of a varray
or too large for a nested table.
*Action: Check the program logic and explicitly extend if necessary.
When I execute this procedure at work it throws the error as shown above but in the live sql link it works. I have only 4 rows in the table i created for the live sql worksheet area. So its something to do with too many rows in the original table.. for table type = 'E6' there is around 13 rows but the whole table will have over 100,000 rows.
Also should i be using dynamic sql to pass the parameter?
Thanks,
PS - please advise if i have set up the live sql link correctly for you to view .. first time using it. Sorry not able to provide live sql link as its still being reviewed. once it is approved ill provide.
Rob.
Presumably, your query returns no rows. So there is no element 1 in the array:
declare
type tp is table of dual%rowtype;
vals tp;
begin
select dummy
bulk collect
into vals
from dual
where 1 = 0;
dbms_output.put_line ( 'Found ' || vals.count || ' rows' );
dbms_output.put_line ( vals(1).dummy );
end;
/
Found 0 rows
ORA-06533: Subscript beyond count
Also should i be using dynamic sql to pass the parameter? NOOOOOOOOOOOOOOOOOOOOO!
You've written the query correctly. It's just the dbms_output reference to RR_TAB(1).TABLE_CODE that's the problem.
Verify that the query fetched something before doing this:
declare
type tp is table of dual%rowtype;
vals tp;
begin
select dummy
bulk collect
into vals
from dual
where 1 = 0;
if vals.count > 0 then
dbms_output.put_line ( vals(1).dummy );
else
dbms_output.put_line ( 'Got nothing' );
end if;
end;
/
Got nothing
On LiveSQL - you save it as an "unlisted" link. You can still share the link for this type of script with us. Without needing approval.