pass pl/sql record as arguement to procedure

Question

How to pass pl/sql record type to a procedure :

CREATE OR REPLACE PACKAGE BODY PKGDeleteNumber
AS
 PROCEDURE deleteNumber (
    list_of_numbers  IN List_Numbers
 )
 IS
   i_write VARCHAR2(5);
 BEGIN
   --do something
 END deleteNumber;

END PKGDeleteNumber;
/

In this procedure deleteNumber I have used List_Numbers, which is a record type. The package declaration for the same is :

CREATE OR REPLACE PACKAGE PKGDeleteNumber
AS
   TYPE List_Numbers IS RECORD (
     IID NUMBER
   );
  TYPE list_of_numbers IS TABLE OF List_Numbers;
  PROCEDURE deleteNumber (
    list_of_numbers  IN List_Numbers
  );
END PKGDeleteNumber;

I have to execute the procedure deleteNumber passing a list of values. I inserted numbers in temp_test table, then using a cursor U fetched the data from it :

   SELECT *
     BULK COLLECT INTO test1
     FROM temp_test;

Now, to call the procedure I am using

execute immediate  'begin PKGDELETENUMBER.DELETENUMBER(:1); end;' 
  using test1;

I have tried many other things as well(for loop, dbms_binding, etc). How do I pass a pl/sql record type as argument to the procedure?

EDIT:

Basically, I want to pass a list of numbers, using native dynamic sql only...

adding the table temp_test defn (no index or constraint):

create table test_temp ( 
   IID number
);

and then inserted 1,2,3,4,5 using normal insert statements.

For this solution,

In a package testproc
CREATE TYPE num_tab_t IS TABLE OF NUMBER;

CREATE OR REPLACE PROCEDURE my_dyn_proc_test (p_num_array IN num_tab_t) AS
BEGIN
  dbms_output.put_line(p_num_array.COUNT);
END;
/

this is called from sql prompt/toad DECLARE v_tab testproc.num_tab_t := testproc.num_tab_t(1, 10); BEGIN EXECUTE IMMEDIATE 'BEGIN testproc.my_dyn_proc_test(:1); END;' USING v_tab; END;

this will not work.This shows error.I am not at my workstation so am not able to reproduce the issue now.

You don't show the definition of test1, and you getting an error? Please show it. — OldProgrammer
it is a simple table with 5 numbers in it...will put the defination — subodh1989
Why do you want to use native dynamic SQL (EXECUTE IMMEDIATE) for this? — Frank Schmitt
there is some project requirement becoz of which i have to use dynamic sql only.. — subodh1989

Przemyslaw Kruglej Przemyslaw Kruglej · Accepted Answer · 2013-10-28T14:17:23

You can't use RECORD types in USING clause of EXECUTE IMMEDIATE statement. If you just want to pass a list of numbers, why don't you just use a variable of TABLE OF NUMBER type? Check below example:

CREATE TYPE num_tab_t IS TABLE OF NUMBER;

CREATE OR REPLACE PROCEDURE my_dyn_proc_test (p_num_array IN num_tab_t) AS
BEGIN
  dbms_output.put_line(p_num_array.COUNT);
END;
/

DECLARE
  v_tab num_tab_t := num_tab_t(1, 10);
BEGIN
  EXECUTE IMMEDIATE 'BEGIN my_dyn_proc_test(:1); END;' USING v_tab;
END;

Output:

Edit

Try this:

CREATE TYPE num_tab_t IS TABLE OF NUMBER;

CREATE OR REPLACE PACKAGE testproc AS
  PROCEDURE my_dyn_proc_test (p_num_array IN num_tab_t);
END;
/

CREATE OR REPLACE PACKAGE BODY testproc AS
  PROCEDURE my_dyn_proc_test (p_num_array IN num_tab_t) AS
  BEGIN
    dbms_output.put_line(p_num_array.COUNT);
  END;
END;
/

DECLARE
  v_tab num_tab_t := num_tab_t(1, 10);
BEGIN
  EXECUTE IMMEDIATE 'BEGIN testproc.my_dyn_proc_test(:1); END;' USING v_tab;
END;

pass pl/sql record as arguement to procedure

2 Answers