All,
Someone please help me. I am trying to receive an array of 5 columns and 20 rows into a procedure. Please help me with some sample code of PACKAGE & PACKAGE BODY.
ThanX
Raj
Printable View
All,
Someone please help me. I am trying to receive an array of 5 columns and 20 rows into a procedure. Please help me with some sample code of PACKAGE & PACKAGE BODY.
ThanX
Raj
If you are talking about a PL/SQL table you'll need something like this:
CREATE OR REPLACE PACKAGE definitions AS
TYPE my_record_type IS RECORD (
col1 number(10),
col2 number(10),
col3 number(10),
col4 number(10),
col5 number(10));
TYPE my_table_type IS TABLE OF my_record_type INDEX BY BINARY_INTEGER;
END Definitions;
/
CREATE OR REPLACE PACKAGE table_param AS
PROCEDURE call;
PROCEDURE receive (p_table IN definitions.my_table_type);
END table_param;
/
CREATE OR REPLACE PACKAGE BODY table_param AS
PROCEDURE call IS
my_table definitions.my_table_type;
BEGIN
FOR i IN 1 .. 20 LOOP
my_table(i).col1 := i*1;
my_table(i).col2 := i*2;
my_table(i).col3 := i*3;
my_table(i).col4 := i*4;
my_table(i).col5 := i*5;
END LOOP;
receive (p_table => my_table);
END call;
PROCEDURE receive (p_table IN definitions.my_table_type) IS
BEGIN
FOR i IN 1 .. p_table.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('p_table(' || i || ').col1 := ' || p_table(i).col1);
DBMS_OUTPUT.PUT_LINE('p_table(' || i || ').col2 := ' || p_table(i).col2);
DBMS_OUTPUT.PUT_LINE('p_table(' || i || ').col3 := ' || p_table(i).col3);
DBMS_OUTPUT.PUT_LINE('p_table(' || i || ').col4 := ' || p_table(i).col4);
DBMS_OUTPUT.PUT_LINE('p_table(' || i || ').col5 := ' || p_table(i).col5);
END LOOP;
END receive;
END table_param;
/
SET SERVEROUTPUT ON SIZE 1000000
EXEC table_param.call;
Cheers!
ThanX a lot. U guys r great.
I would love to see a varray sample too.
It's almost identical:
CREATE OR REPLACE PACKAGE definitions AS
TYPE my_record_type IS RECORD (
col1 number(10),
col2 number(10),
col3 number(10),
col4 number(10),
col5 number(10));
TYPE my_varray_type IS VARRAY(20) OF my_record_type;
END Definitions;
/
CREATE OR REPLACE PACKAGE varray_param AS
PROCEDURE call;
PROCEDURE receive (p_varray IN definitions.my_varray_type);
END varray_param;
/
CREATE OR REPLACE PACKAGE BODY varray_param AS
PROCEDURE call IS
my_varray definitions.my_varray_type := definitions.my_varray_type();
BEGIN
FOR i IN 1 .. 20 LOOP
my_varray.extend;
my_varray(i).col1 := i*1;
my_varray(i).col2 := i*2;
my_varray(i).col3 := i*3;
my_varray(i).col4 := i*4;
my_varray(i).col5 := i*5;
END LOOP;
receive (p_varray => my_varray);
END call;
PROCEDURE receive (p_varray IN definitions.my_varray_type) IS
BEGIN
FOR i IN 1 .. p_varray.count LOOP
DBMS_OUTPUT.PUT_LINE('p_varray(' || i || ').col1 := ' || p_varray(i).col1);
DBMS_OUTPUT.PUT_LINE('p_varray(' || i || ').col2 := ' || p_varray(i).col2);
DBMS_OUTPUT.PUT_LINE('p_varray(' || i || ').col3 := ' || p_varray(i).col3);
DBMS_OUTPUT.PUT_LINE('p_varray(' || i || ').col4 := ' || p_varray(i).col4);
DBMS_OUTPUT.PUT_LINE('p_varray(' || i || ').col5 := ' || p_varray(i).col5);
END LOOP;
END receive;
END varray_param;
/
SET SERVEROUTPUT ON SIZE 1000000
EXEC varray_param.call;
Cheers!