Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Array of Records in PL/SQL

321894May 22 2003 — edited May 23 2003
is there any data type like array of records that can be used in PLSQL programming?
Like I have a set of calculation done in a loop and want to load the records in the array once the loop is through the array of records has to be passed into a function as input.

I done want to load this into a temp table. I want this to happen dyanamically on fly with out storing in tables.

First this is that is there any data type for storring array of records

If available can this array be passed as output from one procedure and as an input another procedure.

if yes can any i get any small example or url where I can find the example.

Thanks in Advance
Nanda Kishore

Comments

155651
You can make use of user defined objects in Oracle 8 onwards. You can create either table of objects or varray of objects.

Mohan
395113
I am giving you an example of using array of Record


DECLARE
-- declaration of Record
TYPE REC1 IS RECORD (COLUMN1 VARCHAR2(10),
COLUMN2 DATE) ;
-- Array of Record
TYPE TAB1 IS TABLE OF REC1 INDEX BY BINARY_INTEGER ;
TAB2 TAB1 ;
-- New Procedure
PROCEDURE P1 (TAB3 TAB1) IS
BEGIN
FOR I IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(TAB3(I).COLUMN1||'-'||TAB3(I).COLUMN2) ;
END LOOP ;
END ;

BEGIN
FOR I IN 1..10 LOOP
TAB2(I).COLUMN1 := I ;
TAB2(I).COLUMN2 := TO_DATE('23/05/2003','DD/MM/YYYY') ;
END LOOP ;

P1(TAB2) ;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM) ;
END ;

I am assigning some values to array and passing it to an procedure and printing the value . The printing procedure
can exists separately also . Hope this solves your issue .
1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 20 2003
Added on May 22 2003
2 comments
962 views