CODEWITHSUNDEEP
oracle-databaseplsql
subodh1989
subodh1989

Reputation: 716

pass pl/sql record as arguement to procedure

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.

Upvotes: 2

Views: 14050

Answers (2)

Perrault Jean-Paul
Perrault Jean-Paul

Reputation: 1

Use an object type. object types are visible to all packages

Upvotes: 0

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

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:

2

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;

Upvotes: 1

Related Questions