0

I'm using vb.net and oracle db, and currently I have a stored-procedure that is called from my code. Right now it looks similar to this:

CREATE OR REPLACE PROCEDURE MYPROCEDURE(
param1   table.field1%TYPE,
param2   table.field2%TYPE,
param3   table.field3%TYPE,
param4   varchar2,
output   OUT number) AS

BEGIN
  DO STUFF
END;

I want to ask if it is possible to change this to send multiple sets of parameters at once, so I could use a FOR LOOP inside my procedure to minimize the number of calls. I want to achieve something like this:

CREATE OR REPLACE PROCEDURE MYPROCEDURE(
param    myArray
output   OUT number) AS

BEGIN
  FOR i IN 1..myArray.COUNT LOOP
     UPDATE FIELD FROM TABLE WHERE ID = myArray(i).field1;
  END LOOP;
END;

Or if there's anything else that would work the same it would be great.

Many thanks.

Filipe Belatti
  • 1
  • This is duplicate question to http://stackoverflow.com/questions/2885575/passing-an-array-of-data-as-an-input-parameter-to-an-oracle-procedure – Lalit Kumar B Sep 16 '14 at 14:19
  • @LalitKumarB Thanks Lalit. I was looking at that topic, but I was still wondering if I could do it with a set of parameters, and not just one. I think I found the solution, I just have to test it now. – Filipe Belatti Sep 17 '14 at 19:32

1 Answers1

1

Yes you can pass a list of objects as parameter in oracle procedure. First you must create the datatype of this list of objects, but you can't do this inside a procedure you have to define it as an oracle object. For example:

CREATE OR REPLACE TYPE TEST."MY_TYPE" AS OBJECT
                  (PARAM1 VARCHAR (20), PARAM2 NUMBER);

Unfortunately you can define dynamic datatypes inside objects (table.field1%TYPE), but I think you know what datatype this field have.

Second, create a package that have the list of parameter and procedure definition like this:

CREATE OR REPLACE PACKAGE ARRAY_EXAMPLE2
AS
   TYPE COL IS TABLE OF MY_TYPE;

   PROCEDURE PROCESS_ARRAY (ArrayIn IN COL);
END;

And finally the package implementation

CREATE OR REPLACE PACKAGE BODY ARRAY_EXAMPLE2
AS
   PROCEDURE PROCESS_ARRAY (ArrayIn IN COL)
   IS
   BEGIN
      FOR i IN 1 .. ArrayIn.COUNT
      LOOP
         DBMS_OUTPUT.PUT_LINE ('Hello ' || ArrayIn (i).PARAM1);
      END LOOP;
   END;
END;

You can try it using this lines of code:

BEGIN
   ARRAY_EXAMPLE2.
    PROCESS_ARRAY (
      array_example2.
       COL (MY_TYPE ('Peter', 12),
            MY_TYPE ('Jorge', 4),
            MY_TYPE ('Bryan', 5)));
END;
Aramillo
  • 3,176
  • 3
  • 24
  • 49
  • You should know that `WHEN OTHERS THEN NULL` is itself a BUG. You should never use it and at least never post such code as an answer. – Lalit Kumar B Sep 16 '14 at 14:15
  • @LalitKumarB MMM, I didn't know that, instead I think it is not the important part of the answer that's why i don't understand the vote down. I'll fix this problem – Aramillo Sep 16 '14 at 14:27
  • I would like to see you fix it, I would give vote up. And understand the consequences of `WHEN OTHERS THEN NULL`. It hides the error, and many more negative impacts. Have a look at my article here http://lalitkumarb.wordpress.com/2014/05/02/when-others-then-null-a-bug/ – Lalit Kumar B Sep 16 '14 at 14:36
  • Ok, good to see you removed the bug. Voted up! Go through the article, practice yourself for better understanding. It will help you, good luck. – Lalit Kumar B Sep 16 '14 at 14:38