Skip to Main Content
  • Questions
  • PL/SQL - Array collection of Records

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Riyaz.

Asked: May 22, 2005 - 10:34 am UTC

Last updated: September 12, 2017 - 7:41 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I am a regular visitor of your site and I love it.
Every time I visit this site, I learn new things.

Here is my problem.
I tried using a collection(PL/SQL Tables) of Records in Oracle 8i but later came to know that it is not supported until Oracle 9.2.
What is the best alternate for this data structure in Oracle 8i? The following is the code I tried:

PROCEDURE EAC_CALCULATE_UNBILLED IS

TYPE EAC_UNBILLED_REC IS RECORD (

JOBNO VARCHAR2(50),
PERIOD DATE,
EARNED NUMBER,
INVOICED NUMBER,
NEW_EARNED NUMBER,
NEW_INVOICED NUMBER,

V30 NUMBER,

V60 NUMBER,

V90 NUMBER,

V180 NUMBER,

V360 NUMBER,
V360PLUS NUMBER);

TYPE EAC_UNBILLED_AGEING IS TABLE OF EAC_UNBILLED_REC;

VAR_UNBILLED EAC_UNBILLED_AGEING;

BEGIN

--SELECT EARNED AND INVOICED VALUES FOR OPEN JOBS
SELECT
JH.JOBNO,
JH.PERIOD,
REV_EAR.ACTAMT,
REV_IN.ACTAMT,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
BULK COLLECT
INTO
VAR_UNBILLED
FROM
EAC_JOB_HEADER_TBL JH,
EAC_REVENUE_EARNED_VW REV_EAR,
EAC_REVENUE_INVOICED_VW REV_IN,
EAC_ADM_STATUS_TBL STATUS
WHERE
JH.JOB_ID = REV_EAR.JOB_ID
AND JH.JOB_ID = REV_IN.JOB_ID
AND JH.JOBSTATUS_ID = STATUS.JOBSTATUS_ID
AND UPPER(STATUS.JOBSTATUS_NAME) = 'OPEN';

END EAC_CALCULATE_UNBILLED;

It raised PLS-00597 error.
Please suggest.

and Tom said...

Collections will work for this:


tkyte@ORA8IW> create or replace type myScalarType as object
2 ( x number,
3 y date,
4 z varchar2(30)
5 )
6 /

Type created.

tkyte@ORA8IW> create or replace type myArrayType as table of myScalarType
2 /

Type created.

tkyte@ORA8IW>
tkyte@ORA8IW> declare
2 l_data myArrayType;
3 begin
4 select myScalarType( user_id, created, userName )
5 bulk collect into l_data
6 from all_users;
7 dbms_output.put_line( l_data.count || ' array elements' );
8 for i in 1 .. l_data.count
9 loop
10 dbms_output.put_line( l_data(i).z );
11 end loop;
12 end;
13 /
22 array elements
SYS
SYSTEM
....
U3
SCOTT

PL/SQL procedure successfully completed.

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Array of Records

Partha, November 21, 2007 - 8:35 am UTC

The problem which I am facing is similar to that presented in the original question. I have created a table of records, which I want to populate manually. I am facing several problems doing so. I cannot seem to find a suitable solution in any of the Oracle Docs or in this site, which may solve my problem.

The code is given below :
DECLARE

    TYPE my_rec IS RECORD (
       object_name         user_objects.object_name%type,
       object_type         user_objects.object_type%type,
       tablespace          user_tablespaces.tablespace_name%type,
       num_rows            NUMBER
    );

    TYPE my_array IS TABLE OF my_rec INDEX BY BINARY_INTEGER;

    my_table my_array ;
    l_row integer;

    function insert_data(
                 p_object_name     varchar2,
                 p_object_type     varchar2,
                 p_tablespace      varchar2,
                 p_num_rows        number
              )
    return integer
    is
        l_last_row integer;
        l_my_rec my_rec;
    begin
        my_table.extend;
        l_last_row := my_table.last_row;
        my_table(l_last_row).object_name  := p_object_name;
        my_table(l_last_row).object_type  := p_object_type;
        my_table(l_last_row).tablespace  := p_tablespace;
        my_table(l_last_row).num_rows  := p_num_rows;
        return l_last_row;
    end;

BEGIN

   l_row := insert_data('TABLE','TABLE1','TS1',340);
   l_row := insert_data('TABLE','TABLE2','TS1',640);
   l_row := insert_data('INDEX','INDEX1','TS2',340);

   for x in my_table.first .. my_table.last
   loop
       dbms_output.put_line(my_table(x).object_name||','||my_table(x).object_type||','||my_table(x).tablespace||','||to_char(my_table(x).num_rows));
   end loop;
END;
/

When I run the above code, it gives the following error :
SQL> @my_space.sql
        my_table.extend;
        *
ERROR at line 26:
ORA-06550: line 26, column 9:
PLS-00306: wrong number or types of arguments in call to 'EXTEND'
ORA-06550: line 26, column 9:
PL/SQL: Statement ignored
ORA-06550: line 27, column 25:
PLS-00302: component 'LAST_ROW' must be declared
ORA-06550: line 27, column 2:
PL/SQL: Statement ignored

I tried to initialize my_table at the place of declaration as shown below :

    my_table my_array := my_array();

But that gives me the following error :
PLS-00222: no function with name 'MY_ARRAY' exists in this scope

Any ideas what I am doing wrong.

Also I was wondering based on the answer to the main question that it is not possible to do it this way in PL/SQL. Correct me if I am wrong.

Tom Kyte
November 20, 2007 - 1:31 pm UTC

index by binary_integer does not need, require, allow for "extend"

it is already 'extended'

 20      return integer
 21      is
 22          l_last_row integer := my_table.count+1;
 23      begin
 24          my_table(l_last_row).object_name  := p_object_name;
 25          my_table(l_last_row).object_type  := p_object_type;
 26          my_table(l_last_row).tablespace  := p_tablespace;
 27          my_table(l_last_row).num_rows  := p_num_rows;
 28          return l_last_row;
 29      end;
 30
 31  BEGIN
 32
 33     l_row := insert_data('TABLE','TABLE1','TS1',340);
 34     l_row := insert_data('TABLE','TABLE2','TS1',640);
 35     l_row := insert_data('INDEX','INDEX1','TS2',340);
 36
 37     for x in my_table.first .. my_table.last
 38     loop
 39         dbms_output.put_line
 40         (my_table(x).object_name||','||my_table(x).object_type||','||
 41          my_table(x).tablespace||','||to_char(my_table(x).num_rows));
 42     end loop;
 43  END;
 44  /
TABLE,TABLE1,TS1,340
TABLE,TABLE2,TS1,640
INDEX,INDEX1,TS2,340

PL/SQL procedure successfully completed.


Varray/Table of Records

DILLIP KUMAR SAHOO, April 04, 2013 - 4:00 am UTC

Hi Tom,

I want to create a nested table of Records, i am able to achieve this by creating a variable first i.e Y and then initializing Z with Y. Similarly in case of initializing A.

DECLARE
TYPE T3 IS RECORD(X1 PLS_INTEGER:=99,X2 EMP.ENAME%TYPE);
TYPE T4 IS TABLE OF T3;
TYPE T5 IS VARRAY(2) OF T3;
Y T3;
Z T4;
A T5;
BEGIN
Z:=T4(Y,Y);
A:=T5(Y,Y);

Z(1).X1:=199;
Z(1).X2:='dillip';
A(2).X1:=200;
A(2).X2:='sahoo';

DBMS_OUTPUT.PUT_LINE(Z.COUNT);
DBMS_OUTPUT.PUT_LINE(A.COUNT);
END;

Output:
2
2

Is there any technique of initializing Z or A directly using a Collection constructor:
Something like: 1) Z:=T4(T3(199,'dillip'),T3(200,'sahoo')); OR
2)A:=T4((199,'dillip'),(200,'sahoo'));
Tom Kyte
April 22, 2013 - 2:02 pm UTC

if you use an object type instead of a plsql record -- yes.

ops$tkyte%ORA11GR2> create or replace type t3 as object ( x1 number, x2 varchar2(10) );
  2  /

Type created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> DECLARE
  2  TYPE T4 IS TABLE OF T3;
  3  TYPE T5 IS VARRAY(2) OF T3;
  4  Y T3;
  5  Z T4;
  6  A T5;
  7  BEGIN
  8          a := t5( t3( 1, 'x'), t3( 2, 'y' ) );
  9  END;
 10  /

PL/SQL procedure successfully completed.

Thanks a lot

Dillip Kumar Sahoo, May 03, 2013 - 12:00 pm UTC

Thanks a lot Tom.. for simple and useful implementation.

what about packaged type rather than database object?

Guillaume, October 15, 2015 - 9:29 am UTC

Hello.

I searched everywhere and did not find a clue why my script does not work.
I understand that if I create an object (public), I can then use it everywhere, at any level, like a global variable.
But I wish I could do it without declaring an object at a database level, rather at some "user" scope in a specific package (as its use would be highly specific).

Why this does not work?

CREATE OR REPLACE PACKAGE pkg_test
IS
 TYPE myrec IS RECORD ( FIELD1 VARCHAR2( 30 ), OPERAND VARCHAR2( 1 ), FIELD2 VARCHAR2( 30 ) );
 TYPE mytab IS TABLE OF myrec INDEX BY PLS_INTEGER;
END;

DECLARE
 rec pkg_test.mytab; -- Seems it can see here
BEGIN
    rec(1) := pkg_test.mytab('A','=','B'); -- does not work: PLS0222, cannot see it anymore and consider mytab as a function (instead of a user type)
    rec(1).FIELD1 := 'A'; -- ...and so on, works
END;

Chris Saxon
October 15, 2015 - 10:20 am UTC

There are a few problems here:

- mytab is an associative array. These don't have constructors. Remove the index by pls_integer clause if you want to do this
http://docs.oracle.com/database/121/LNPLS/composites.htm#LNPLS00504
- rec(1) expects a record. The code assigns it a table of records. Either assign a record to entry 1, or remove the index and assign the table.
- Records don't have constructors. You need to reference each field individually:
http://docs.oracle.com/database/121/LNPLS/composites.htm#LNPLS00510

Here's the code modified to fix these issues:

CHRIS>CREATE OR REPLACE PACKAGE pkg_test
  2  IS
  3      TYPE myrec IS RECORD ( FIELD1 VARCHAR2( 30 ), OPERAND VARCHAR2( 1 ), FIELD2 VARCHAR2( 30 ) );
  4      TYPE mytab IS TABLE OF myrec;
  5  END;
  6  /

Package created.

Elapsed: 00:00:01.03
CHRIS>DECLARE
  2      r   pkg_test.myrec;
  3      rec pkg_test.mytab;
  4  BEGIN
  5      r.field1 := 'a';
  6      rec := pkg_test.mytab(r); --assign the array
  7      rec(1) := r; -- array the record
  8  END;
  9  /

PL/SQL procedure successfully completed.

Thanks!

Guillaume, October 15, 2015 - 10:35 am UTC

Crystal clear! Thank you so much for the answer and the demonstration.

Nested Tables Of Type Object

CHARAN, September 08, 2017 - 2:13 pm UTC

Can a table 'A' be used as a nested table where it's been created in the following way using an object type :-
create type my_rec as record(cost number(5),course_name varchar2(10));
create type my_tab as table of my_rec;
create table Tab_1(s_name varchar(10),cost_course my_tab)nested table cost_course store as course_tab;


And Finally another doubt is that can a normal sql table contain more than one nested table-columns ?

Thanks In Advance...
Connor McDonald
September 12, 2017 - 7:41 am UTC

DId you test your script or just type it ?

SQL> create type my_rec as record(cost number(5),course_name varchar2(10));
  2  /

Warning: Type created with compilation errors.

SQL> sho err
Errors for TYPE MY_REC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/16     PLS-00103: Encountered the symbol "RECORD" when expecting one of
         the following:
         array varray table object fixed varying opaque sparse


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library