CODEWITHSUNDEEP
vb.netoracle-databasestored-proceduresparameters
Filipe Belatti
Filipe Belatti

Reputation: 1

Sending multiple sets of parameters to procedure

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.

Upvotes: 0

Views: 384

Answers (1)

Aramillo
Aramillo

Reputation: 3226

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;

Upvotes: 1

Related Questions