CODEWITHSUNDEEP
oracle-databaseplsql
chickenman
chickenman

Reputation: 798

How to pass an array type in plsql package specification?

I am new to plsql. I am trying to put two scripts under the same package. These scripts deal with arrays. How do I pass an array into the procedure? If I am to declare the array, do I do it in the specification or the body? I am trying this right now but it doesn't work.

CREATE PACKAGE cop_cow_script AS

PROCEDURE COP_COW_DATALOAD_V2(arr_claims VARRAY(15000) OF VARCHAR2(10), arr_sql VARRAY(500) OF VARCHAR2(1000));

END cop_cow_script;

As you see I want to pass in those two arrays.

Upvotes: 0

Views: 2715

Answers (2)

krokodilko
krokodilko

Reputation: 36107

You need to declare types in the package specification and use them as parameter types in the procedure declaration:

CREATE OR REPLACE PACKAGE cop_cow_script AS

   TYPE arr_claims_t   IS VARRAY(15000) OF VARCHAR2(10);
   TYPE arr_sql_t       IS VARRAY(500) OF VARCHAR2(1000);

PROCEDURE COP_COW_DATALOAD_V2(arr_claims arr_claims_t, arr_sql arr_sql_t);

END cop_cow_script;
/

EDIT - below is an example of the package body - the procedure loops through elements of it's first parameter and prints them using DBMS_OUTPUT.PUT_LINE

PROCEDURE COP_COW_DATALOAD_V2(arr_claims arr_claims_t, arr_sql arr_sql_t)
IS
BEGIN 
   FOR i IN arr_claims.FIRST .. arr_claims.LAST
   LOOP
     DBMS_OUTPUT.PUT_LINE( arr_claims( i ) );
   END LOOP;
END;

END cop_cow_script;
/

An then you can initialize them and pass to the procedure invocation for example in this way (this is an anonymous block that declares two variables, initializes them and invokes the procedure passing both parameters to it:

DECLARE 
   my_array1 cop_cow_script.arr_claims_t := cop_cow_script.arr_claims_t();
   my_array2 cop_cow_script.arr_sql_t := cop_cow_script.arr_sql_t();
BEGIN
   my_array1.extend;
   my_array1( 1 ) := 'string 1';

   my_array2.extend;
   my_array2( 1 ) := 'string 2';

   cop_cow_script.COP_COW_DATALOAD_V2( my_array1, my_array2 );
END;
/

Upvotes: 5

Justin Cave
Justin Cave

Reputation: 231661

First off, I'm hard-pressed to imagine a situation where you'd actually want to use a PL/SQL varray rather than a nested table or an associative array. There really isn't a benefit to declaring a fixed size collection.

Whatever sort of collection type you want, you'd need to declare the collection type either in the package specification or at the SQL level (depending on the type of collection) separate from the procedure specification

CREATE PACKAGE cop_cow_script AS
  TYPE arr_claims IS varray(15000) of varchar(10);
  TYPE arr_sql    IS varray(500)   of varchar(1000);

  PROCEDURE COP_COW_DATALOAD_V2(p_claims arr_claims, 
                                p_sql arr_sql);

END cop_cow_script;

Upvotes: 1

Related Questions