ORACLE-SQL: stored procedure using array as parameter for select

Question

I have to use an array into my stored procedure. So I created a type variable:

CREATE OR REPLACE TYPE integer_array is table of number;

Then I tried to write my stored procedure, but I can't to compile it:

create or replace
PROCEDURE SP_TEST(
      i_id_profiles in integer_array, 
      o_clients OUT SYS_REFCURSOR
   )
AS
BEGIN
open o_clients for 
    FOR i IN 1..i_id_profiles.count LOOP
      select a.id_client from b_client a, i_client_profile b where a.id_client = b.id_client 
      and b.id_profile = i_id_profiles(i);
    END LOOP;
END SP_TEST;

Can you help me? I want to get a SYS_REFCURSOR of my select.

thanks

The error:

PLS-00103: Encountered the symbol "FOR" when expecting one of the following: ( - + case mod new not null select with
continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: end not pragma final instantiable order overriding static member constructor map

I add now the compilation errors, but in my opinion the stored procedure is totaly wrong. — jeangr
– jeangr, Commented Oct 2, 2013 at 10:18

Nick Krasnov · Accepted Answer · 2013-10-02 10:27:20Z

3

You open a ref cursor for a query(static or dynamic), you cannot open a ref cursor for a for loop construct or any kind of loop construct. It simply semantically incorrect. Moreover, in this situation, there is no need of a loop of any kind at all. As you've created the integer_array as sql type(schema object) you can use table operator to simply select from the instance of that type(represent it as a table). So your procedure might look like this:

create or replace PROCEDURE SP_TEST(
      i_id_profiles in integer_array, 
      o_clients OUT SYS_REFCURSOR
   )
AS
BEGIN
  open o_clients for 
      select a.id_client 
         from b_client a 
         join i_client_profile b 
           on (a.id_client = b.id_client)
         join table(i_id_profiles)  s
           on b.id_profile = s.column_value;
END SP_TEST;

edited Oct 2, 2013 at 10:27

answered Oct 2, 2013 at 10:17

Nick Krasnov

27.3k6 gold badges66 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ORACLE-SQL: stored procedure using array as parameter for select

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related