Skip to Main Content
  • Questions
  • Pass parameter to where clause in bulk collect statement

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Robert.

Asked: May 02, 2019 - 1:27 pm UTC

Last updated: May 03, 2019 - 9:59 am UTC

Version: oracle 12c

Viewed 1000+ times

You Asked

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.

and Chris said...

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.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library