MultiDimensional Array

[Rajkartha]

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

[TimHall]

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!

[Rajkartha]

ThanX a lot. U guys r great.

I would love to see a varray sample too.

[TimHall]

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!