0

I want to pass multiple product keys to a procedure, like product 1,2,5,7

I'm calling the procedure the following way:

call procedure_test('emp1',(1,2,5,7));

passed with the following where condition:

i:= (1,2,5,7)
 where a.products in (i)
APC
  • 144,005
  • 19
  • 170
  • 281
Guilherme Pedrosa
  • 3
  • 1
  • 3
    [This](https://stackoverflow.com/q/2885575/1707353), is probably what you're looking for. – Jeff Holt Aug 20 '18 at 16:27
  • PL/SQL doesn't support anonymous array types - they have to be named, which means either a standalone `create or replace type xyz as table of number;` or else a PL/SQL declaration in a package spec, like `type xyz is table of number;` (or one of the other collection types - there are [three](https://stackoverflow.com/q/14934514/230471)). – William Robertson Aug 20 '18 at 17:24
  • Which of those collection types can be used in a `where` clause depends on the version of Oracle you are using, I believe. – Alex Poole Aug 20 '18 at 17:42

1 Answers1

0

Since I love a good associative array, here is my take (even though what jeff6times7 suggested is just as good) :

DECLARE
   -- Declare the associative array type
   TYPE typTabProductID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

   -- Array variable, used for test only
   tableTest typTabProductID;

   -- Procedure eating a parameter of array type
   PROCEDURE myProc(tabProductID typTabProductID) IS
   BEGIN
      FOR i IN 1 .. tabProductID.COUNT LOOP
         dbms_output.put_line(tabProductID(i));
      END LOOP;
   END;
BEGIN
   -- Fill the array variable
   tableTest(1) := 5;
   tableTest(2) := 8;
   tableTest(3) := 11;

   -- call the procedure
   myProc(tableTest);
END;
Dessma
  • 599
  • 3
  • 11
  • 1
    A nested table is likely to be more useful than an associative array, especially if the OP wants to use the passed values in a SQL statement, which it appears they do. – APC Aug 20 '18 at 18:02