Skip to Main Content
  • Questions
  • Initializing a PLSQL table of records

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Jack.

Asked: December 13, 2003 - 5:30 pm UTC

Last updated: April 10, 2018 - 1:59 am UTC

Version: 9.2.0

Viewed 100K+ times! This question is

You Asked

Tom,

How do you initialize a PL/SQL table of records in the Declaration section of a PL/SQL block?

In the following snippet, I can successfully initialize a normal scalar PL/SQL table but am unsuccessful initializing a table of records. Can it be done?

Also, why is the last loop bombing with "Reference to uninitialized collection"?

SET SERVEROUTPUT ON
DECLARE
TYPE t_type IS TABLE OF VARCHAR2 (30);
l_array t_type := t_type ('X1', 'X2', 'X3');

TYPE r_type IS RECORD (
code VARCHAR2 (3),
description VARCHAR2 (30)
);
TYPE tr_type IS TABLE OF r_type;
l_rarray tr_type; -- := tr_type... ?
l_rec r_type;
BEGIN
FOR i IN l_array.FIRST .. l_array.LAST LOOP
DBMS_OUTPUT.put_line (l_array (i));
END LOOP;
--
-- I'd like to move these initializations to the declaration
-- section above.
l_rec.code := 'R1';
l_rec.description := 'R1 Description';
l_rarray(1) := l_rec;
--
-- why is this bombing with "Reference to uninitialized collection"
FOR i IN l_rarray.FIRST .. l_rarray.LAST LOOP
DBMS_OUTPUT.put_line (l_rarray (i).code || ' - ' || l_rarray (i).description);
END LOOP;
END;
/


Thanks!

and Tom said...

(See Addenda for 18c at bottom)

There is no syntax for initializing in the declare section with a table of records.

A collection of objects -- yes.

ops$tkyte@ORA920> create type myScalarType as object
2 ( x int, y date, z varchar2(20) );
3 /

Type created.

ops$tkyte@ORA920> create type myArrayType as table of myScalarType
2 /

Type created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
2 l_data myArrayType := myArrayType( myScalarType( 1, sysdate, 'hello' ),
3 myScalarType( 2, sysdate, 'world' ) );
4 begin
5 null;
6 end;
7 /

PL/SQL procedure successfully completed.



As for the second question -- the LOOP is not "bombing out", the assignment to l_rarray(1) is. l_rarray is not initialized at all, and if we initialized it:

ops$tkyte@ORA920> SET SERVEROUTPUT ON
ops$tkyte@ORA920> DECLARE
2 TYPE r_type IS RECORD (
3 code VARCHAR2 (3),
4 description VARCHAR2 (30)
5 );
6 TYPE tr_type IS TABLE OF r_type;
7 l_rarray tr_type := tr_type();
8 l_rec r_type;
9 BEGIN
10 l_rec.code := 'R1';
11 l_rec.description := 'R1 Description';
12 l_rarray(1) := l_rec;
13 END;
14 /
DECLARE
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 12

you'll see we did not allocate it (collections work differently then plsql table types). So, we'll allocate it

ops$tkyte@ORA920>
ops$tkyte@ORA920> DECLARE
2 TYPE r_type IS RECORD (
3 code VARCHAR2 (3),
4 description VARCHAR2 (30)
5 );
6 TYPE tr_type IS TABLE OF r_type;
7 l_rarray tr_type := tr_type();
8 l_rec r_type;
9 BEGIN
10 l_rec.code := 'R1';
11 l_rec.description := 'R1 Description';
12
13 l_rarray.extend;

14 l_rarray(1) := l_rec;
15 END;
16 /

PL/SQL procedure successfully completed.

or, we can allocate and initialize in one step:

ops$tkyte@ORA920>
ops$tkyte@ORA920> DECLARE
2 TYPE r_type IS RECORD (
3 code VARCHAR2 (3),
4 description VARCHAR2 (30)
5 );
6 TYPE tr_type IS TABLE OF r_type;
7 l_rarray tr_type ;
8 l_rec r_type;
9 BEGIN
10 l_rec.code := 'R1';
11 l_rec.description := 'R1 Description';
12 l_rarray := tr_type( l_rec );

13
14 FOR i IN l_rarray.FIRST .. l_rarray.LAST
15 LOOP
16 dbms_output.put_line( i );
17 DBMS_OUTPUT.put_line (l_rarray(i).code || ' - ' || l_rarray(i).description);
18 END LOOP;
19 END;
20 /
1
R1 - R1 Description

PL/SQL procedure successfully completed.


===================
Addenda:

18c improves upon this with new features. See the following for more details

http://stevenfeuersteinonplsql.blogspot.com/2018/03/qualified-expressions-aka-constructor.html

https://oracle-base.com/articles/18c/qualified-expressions-in-plsql-18c

Rating

  (17 ratings)

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

Comments

why not use "INDEX BY BINARY_INTEGER"

Kelvin Tian, December 17, 2003 - 5:35 am UTC

i added "INDEX BY BINARY_INTEGER" to the statements,so it worded correctly.
i want to know why not use "INDEX BY BINARY_INTEGER" ?

Thanks.

DECLARE
TYPE t_type IS TABLE OF VARCHAR2 (30);
l_array t_type := t_type ('X1', 'X2', 'X3');

TYPE r_type IS RECORD (
code VARCHAR2 (3),
description VARCHAR2 (30)
);
TYPE tr_type IS TABLE OF r_type INDEX BY BINARY_INTEGER;
l_rarray tr_type; -- := tr_type... ?
l_rec r_type;
BEGIN
FOR i IN l_array.FIRST .. l_array.LAST LOOP
DBMS_OUTPUT.put_line (l_array (i));
END LOOP;

l_rec.code := 'R1';
l_rec.description := 'R1 Description';
l_rarray(1) := l_rec;

FOR i IN l_rarray.FIRST .. l_rarray.LAST LOOP
DBMS_OUTPUT.put_line (l_rarray (i).code || ' - ' || l_rarray
(i).description);
END LOOP;
END;

Tom Kyte
December 17, 2003 - 7:00 am UTC

you certainly can use that. it is a different datatype at that point -- i answered "how to initialize my data"

Useful info

Darin Markus, September 03, 2004 - 1:19 pm UTC

This information was extremely useful; it answered my question exactly about initializing a collection of records. However, now when I try to simply replace the word RECORD with OBJECT in my type declaration, I get an error. (Please note this is beiing done in the declaration block of a PL/SQL procedure - before the BEGIN statement)

Original, which was OK:

type tab_proc_rec is RECORD (
tab_name user_tables.table_name%type,
proc_name user_source.name%type);

type tab_proc_table is table of tab_proc_obj;

Simply replace RECORD with OBJECT as follows:
type tab_proc_obj is OBJECT (
tab_name user_tables.table_name%type,
proc_name user_source.name%type);

and i get the following error on the OBJECT TYPE declaration line:
PLS-00540: object not supported in this context.

Most confusing, since I believe I have done this before.

Tom Kyte
September 03, 2004 - 1:46 pm UTC

create type as object is available only in SQL.

create type tab_proc_obj as object
( ....
)
/




Will re-initializing TRIM it?

A reader, September 15, 2004 - 5:14 am UTC

Hi Tom,

This is how we initilize.

l_rarray tr_type := tr_type();

And then extend and use it.

l_rarray.extend;

So if I re-initilize it again, say

l_rarray := tr_type();

Will this be equivalent to l_rarray.trim?

Or an explict TRIM will be required to free the memory?

Thanks



Tom Kyte
September 15, 2004 - 9:26 am UTC

it would allow plsql to free and reuse the PGA memory.

What about record type define in the database

Jean Boule, March 04, 2005 - 9:54 am UTC

Hi Tom,

I've got type define in the database in this manner

CREATE OR REPLACE TYPE jean_TYPE_REC_test
as object
(
l_a varchar2(10) ,
l_b number
);

CREATE OR REPLACE TYPE jean_TYPE_TAB_test
as table of jean_TYPE_REC_test
;

and I’m trying to access this type without initialising all field in the record type like this

declare
p_tab jean_type_tab_test;
l_rec jean_type_rec_test ;
begin
l_rec.l_a := 'aa;
p_tab := jean_type_tab_test(l_rec);

end;

But I get ORA-06530 Reference to uninitialized composite

I know if I do this it will work

declare
p_tab jean_type_tab_test;
l_rec jean_type_rec_test ;
begin
l_rec := jean_type_rec_test('aa',null);
p_tab := jean_type_tab_test(l_rec);

end;


But I don't want to be force to initialize all field of the record type!

Is it possible?

Thanks

Jean Boulé




Tom Kyte
March 04, 2005 - 10:30 am UTC

you cannot get there from here.  There is nothing to assign TO until you create something there.

You are not going to get there from here.

But -- constructors can be very useful if you see the need to do this lots.

You might be interested only in the first one and not need the others:


ops$tkyte@ORA9IR2> CREATE OR REPLACE TYPE jean_TYPE_REC_test
  2  as object
  3   (
  4     l_a                    varchar2(10) ,
  5     l_b                    number  ,
  6     constructor function jean_TYPE_REC_test return self as result,
  7     constructor function jean_TYPE_REC_test(a in varchar2) return self as result,
  8     constructor function jean_TYPE_REC_test(b in number) return self as result
  9   )
 10  /
 
Type created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> CREATE OR REPLACE TYPE body jean_TYPE_REC_test
  2  as
  3  constructor function jean_TYPE_REC_test return self as result
  4  is
  5  begin
  6          return;
  7  end;
  8
  9  constructor function jean_TYPE_REC_test(a in varchar2) return self as result
 10  is
 11  begin
 12      self.l_a := a;
 13          return;
 14  end;
 15
 16  constructor function jean_TYPE_REC_test(b in number) return self as result
 17  is
 18  begin
 19      self.l_b := b;
 20          return;
 21  end;
 22
 23  end;
 24  /
 
Type body created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_rec jean_type_rec_test := jean_type_rec_test();
  3  begin
  4          l_rec.l_a := 'aa';
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
 

Regarding Initializing plsql table of records

Raja, April 15, 2005 - 7:54 am UTC

My requrement is as below:-
declare
type r_data is record
(
v_empno emp.empno%type,
v_ename emp.ename%type,
);
type t_data is table of r_data index by binary_integer;
v_t_data t_data;
-- HOW TO INITIALIZE THIS v_t_data ??
--I am facing problems.Suppose i want to initialize this plsql table with
--7902,'tom'
--1000,'dinesh'
--WHAT SHOULD I DO FOR IT
begin
...

Thanks and regards


Tom Kyte
April 15, 2005 - 9:25 am UTC

you don't, not with records.  you would assign to it in the begin block.

using object types:


ops$tkyte@ORA9IR2> create or replace type scalarType as object ( empno number, ename varchar2(20) )
  2  /
 
Type created.
 
ops$tkyte@ORA9IR2> create or replace type tableType as table of scalarType
  2  /
 
Type created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_data tabletype := tabletype( scalarType( 1, 'tom' ),
  3                                         scalarType( 2, 'lori' ) );
  4  begin
  5          null;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.

you can 

A reader, January 19, 2006 - 5:34 am UTC

Hi Tom,

I have setup second database from existing database. Then I have following error on one schema.

(1). Package:

CREATE OR REPLACE PACKAGE FONDSRPT.query_idx_tst AUTHID DEFINER
AS
TYPE idx_his_wgh_1_t IS RECORD (
idx_cus VARCHAR (10),
idx_dat_ref DATE,
idx_dat_ref_val NUMBER,
idx_from DATE,
idx_from_factor NUMBER,
idx_nam VARCHAR (10),
fx_cur VARCHAR (3),
wgh NUMBER,
asof DATE,
val NUMBER,
fx_rate NUMBER
);

TYPE idx_his_wgh_1_ref_t IS REF CURSOR
RETURN idx_his_wgh_1_t;

TYPE idx_his_yld_t IS RECORD (
idx_cus VARCHAR2 (10 BYTE),
idx_dat_ref DATE,
idx_dat_ref_val NUMBER,
idx_from DATE,
idx_from_factor NUMBER,
idx_nam VARCHAR2 (10 BYTE),
asof DATE,
yield NUMBER
);

TYPE idx_his_yld_tab_t IS TABLE OF idx_his_yld_t;

....

FUNCTION idx_his_yld (c_idx_his idx_his_wgh_1_ref_t)
RETURN idx_his_yld_tab_t PIPELINED;

END;
/

CREATE OR REPLACE PACKAGE BODY FONDSRPT.query_idx_tst
IS
FUNCTION idx_his_yld (c_idx_his idx_his_wgh_1_ref_t)
RETURN idx_his_yld_tab_t PIPELINED
IS
in_rec_d0 idx_his_wgh_1_t;
in_rec_d1 idx_his_wgh_1_t;
in_rec_tbl idx_his_wgh_1_tab_t := idx_his_wgh_1_tab_t ();
in_cmp_tbl idx_his_wgh_1_tab_t := NULL;
out_rec idx_his_yld_t;
......
BEGIN
in_rec_tbl.EXTEND (10);
i1 := in_rec_tbl.FIRST;
.....

LOOP
FETCH c_idx_his
INTO in_rec_tbl (i1);

EXIT WHEN c_idx_his%NOTFOUND;
IF (n_weight = 1)
THEN
-- out_rec.yield := 100;
-- PIPE ROW (out_rec);
IF in_cmp_tbl IS NULL
THEN
in_cmp_tbl := in_rec_tbl;
-- out_rec.yield := 101;
-- PIPE ROW (out_rec);
ELSE
-- out_rec.yield := 102;
-- PIPE ROW (out_rec);
n_yield_day := 0;

FOR i2 IN in_rec_tbl.FIRST .. i1
LOOP
IF (in_cmp_tbl (i2).val) <> 0
THEN
n_yield_day :=
n_yield_day
+ ( ( in_rec_tbl (i2).val
/ NVL (in_rec_tbl (i2).fx_rate, 1)
) * in_rec_tbl (i2).wgh
);
END IF;
END LOOP;

in_cmp_tbl := in_rec_tbl;
n_yield_tot := n_yield_tot * n_yield_day;

......
out_rec.yield := n_yield_tot;-- * (out_rec.idx_from_factor);
PIPE ROW (out_rec);
END IF;

i1 := in_rec_tbl.FIRST;
ELSE
i1 := i1 + 1;
END IF;
END LOOP;

CLOSE c_idx_his;
RETURN;
END;
...
END; /* Package */
/


(2).Following VIEW calls the function : idx_his_yld in above package.

CREATE OR REPLACE FORCE VIEW FONDSRPT.V_IDX_CUS_YIELD
(IDX_CUS, IDX_DAT_REF, IDX_DAT_REF_VAL, IDX_FROM, IDX_FROM_FACTOR,
IDX_NAM, ASOF, YIELD)
AS
SELECT distinct idx_cus, idx_dat_ref, idx_dat_ref_val, idx_from, idx_from_factor,
idx_nam, asof, yield
FROM TABLE
(fondsrpt.query_idx_tst.idx_his_yld
(CURSOR
(SELECT t3.* FROM v_idx_his_yld t3, Another_Table;
)
)
) --439 |;;


My problem is This view is working fine in original database. But new database it is not working. it gives following error:

ERROR at line 4:
ORA-06533: Subscript beyond count
ORA-06512: at "FONDSRPT.QUERY_IDX_TST", line 297


Line 297 in Package as below in above function.

FETCH c_idx_his INTO in_rec_tbl (i1);


The database infrastructure is exactly same as original database.

Could you please tell what I have missed to get above error?


Thank you very much

Tom Kyte
January 19, 2006 - 12:37 pm UTC

I say the same thing I said elsewhere. Please read there.

Comparing 2 PL/SQL Table of records

Vikram Romeo, June 29, 2006 - 2:17 pm UTC

Hi Tom,

is there an Oracle Inbuilt Function which can compare 2 pl/sql table of records?

if not, is there an easier way of doing this?

Regards,
Vikram Romeo

Tom Kyte
June 29, 2006 - 3:10 pm UTC

there is not, you have to iterate and compare tables of records.

if they were collections of objects.... that would be different.

Sorting of PL/SQL record

A reader, July 17, 2008 - 1:59 am UTC

Hi Tom,

Is there a way to do a sorting based on 1 or more column in PL/SQL record ?

Thks,
Raymond
Tom Kyte
July 17, 2008 - 11:44 am UTC

I don't know what it means to sort a "record"

A reader, July 17, 2008 - 9:17 pm UTC

Hi Tom,

Sorry for not giving enough explanation, what I meant is there a way to do a sorting on PL/SQL table of records based on 1 or more column.

Thks,
Raymond
Tom Kyte
July 18, 2008 - 4:22 pm UTC

a plsql table of records is incredibly different than a record :)

but you cannot "sort" a table of records, you can easily sort a collection into another collection


ops$tkyte%ORA10GR2> create or replace type myScalarType as object
  2  ( x int, y date )
  3  /

Type created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace type myTableType as table of myScalarType
  2  /

Type created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
  2          l_unsorted myTableType := myTableType( myScalarType( 5, sysdate ), myScalarType( 1, sysdate-2 ) );
  3          l_sorted myTableType;
  4  begin
  5          select myScalarType(x,y) bulk collect into l_sorted
  6            from table( l_unsorted )
  7           order by x;
  8
  9          for i in 1 .. l_sorted.count
 10          loop
 11                  dbms_output.put_line( l_sorted(i).x || ', ' || l_sorted(i).y );
 12          end loop;
 13  end;
 14  /
1, 16-JUL-08
5, 18-JUL-08

PL/SQL procedure successfully completed.

Sort a pl/sql table

A reader, July 18, 2008 - 11:52 am UTC

This might do the trick:

Create record and table object types as:

create or replace TYPE shares_sort IS OBJECT
(application_no NUMBER(10)
,price NUMBER(10,2)
,shares NUMBER(10)
,value NUMBER(10,2));
/

create or replace TYPE shares_sort_table IS TABLE OF shares_sort;
/

Code to do the sorting:

DECLARE

TYPE r_application IS RECORD
(application_no NUMBER(10)
,price NUMBER(10,2)
,shares NUMBER(10)
,value NUMBER(10,2));

TYPE t_application IS TABLE OF r_application INDEX BY PLS_INTEGER;

v_sort_table shares_sort_table := shares_sort_table();
v_index PLS_INTEGER;
v_applications t_application;

BEGIN

v_applications(1).application_no := 45465;
v_applications(1).price := 1.00;
v_applications(1).shares := 434;
v_applications(1).value := 434.00;

v_applications(2).application_no := 7878;
v_applications(2).price := 2.00;
v_applications(2).shares := 444;
v_applications(2).value := 888.00;

v_sort_table.DELETE;

v_index := v_applications.FIRST;
LOOP
EXIT WHEN v_index IS NULL;
v_sort_table.EXTEND;
v_sort_table(v_sort_table.LAST) := shares_sort(v_applications(v_index).application_no, v_applications(v_index).price, v_applications(v_index).shares, v_applications(v_index).value);
v_index := v_applications.NEXT(v_index);
END LOOP;

-- Sort the table in order of price ascending, shares descending

SELECT CAST (MULTISET (SELECT shares_sort(application_no, price, shares, value)
FROM TABLE(v_sort_table)
ORDER BY price ASC, shares DESC)
AS shares_sort_table)
INTO v_sort_table
FROM dual;

END;
/


A reader, July 20, 2008 - 10:16 pm UTC

Another brilliant answer....as always.

Thks so much,
Raymond

A reader, July 20, 2008 - 10:51 pm UTC

Hi Tom,

Does the above statement support for Oracle 8.1.7.4.0 ?
I'm getting PLS-00801: Internal Error(22914).

Rgds,
Raymond
Tom Kyte
July 22, 2008 - 10:08 am UTC

I don't have an 8174 handy to test with, but back then you might have to use CAST

ops$tkyte%ORA10GR2> declare
2 l_unsorted myTableType := myTableType( myScalarType( 5, sysdate ), myScalarType( 1,
sysdate-2 ) );
3 l_sorted myTableType;
4 begin
5 select myScalarType(x,y) bulk collect into l_sorted
6 from table( cast( l_unsorted as myTableType ) )
7 order by x;

Without creating the function

Anil, February 17, 2009 - 9:39 am UTC

Tom,
You have shown by creating a function. Is there any way where I can achieve this without creating the function (F_LIST) in my example shown below. I mean populating the array (table_contract) using pl/sql block and selecting using the TABLE() as you have shown.

create or replace type rec_contract as object
( column1 varchar2(250), column2 varchar2(4000) );


create or replace type table_contract as table of rec_contract;

create or replace function f_list return table_contract
as
ls_contract varchar2(4000);
ls_column1 varchar2(250);
l_table_contract table_contract := table_contract();
begin
l_table_contract.delete;
ls_column1 := null;
for x in (select column1, column2 from sales group by column1, column2)
loop
if ls_column1 is null or ls_column1 <> x.column1 then
if ls_column1 is not null then
l_table_contract.extend;
l_table_contract(l_table_contract.count) := rec_contract(ls_column1, ls_contract);
end if;
ls_column1 := x.column1;
ls_contract := x.bfg_column2;
else
ls_contract := ls_contract ||','||x.column2;
end if;
end loop;
l_table_contract.extend;
l_table_contract(l_table_contract.last) := rec_contract(ls_column1, ls_contract);

SELECT CAST (MULTISET (SELECT rec_contract(column1, column2)
FROM TABLE(l_table_contract)
ORDER BY column2)
AS table_contract)
INTO l_table_contract
FROM dual;
return l_table_contract;
end;

select * from TABLE ( cast(f_list as table_contract) );




Tom Kyte
February 17, 2009 - 9:45 am UTC

http://asktom.oracle.com/Misc/varying-in-lists.html


ops$tkyte%ORA10GR2> variable txt varchar2(30)
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec :txt := 'a,b,c,hello,world,d,e,f'

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> with data
  2  as
  3  (
  4  select
  5    trim( substr (txt,
  6          instr (txt, ',', 1, level  ) + 1,
  7          instr (txt, ',', 1, level+1)
  8             - instr (txt, ',', 1, level) -1 ) )
  9      as token
 10     from (select ','||:txt||',' txt
 11             from dual)
 12   connect by level <= length(:txt)-length(replace(:txt,',',''))+1
 13   )
 14   select * from data;

TOKEN
----------------------------------
a
b
c
hello
world
d
e
f

8 rows selected.


Filling collection with objects

Marcin Jackowski, October 29, 2010 - 9:00 am UTC

It is strange but Oracle does not raise any exception in case a collection is made of objects that were not instantiated (or not instantiated properly) - an example:

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Oct 29 15:51:15 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> set serveroutput on;
SQL> CREATE TYPE O_TYPE is object (id number(38,0))
  2  /

Type created.

SQL> declare
  2   type to_type is table of o_type;
  3   my_object o_type;
  4   my_object2 o_type := o_type(null);
  5   my_table to_type := to_type() ;
  6  begin
  7   my_object.id := 123;
  8   my_object2.id := 123;
  9   my_table.extend;
 10   my_table(1) := my_object;
 11   dbms_output.put_line('id='||my_table(1).id);
 12   my_table(1) := my_object2;
 13   dbms_output.put_line('id2='||my_table(1).id);
 14  end;
 15  /
id=
id2=123

PL/SQL procedure successfully completed.


Tom Kyte
November 01, 2010 - 6:52 am UTC

fixed in current releases, probable that there is a patch for older releases (contact support)

ops$tkyte%ORA11GR2> set serveroutput on;
ops$tkyte%ORA11GR2> CREATE TYPE O_TYPE is object (id number(38,0))
  2  /

Type created.

ops$tkyte%ORA11GR2> declare
  2   type to_type is table of o_type;
  3   my_object o_type;
  4   my_object2 o_type := o_type(null);
  5   my_table to_type := to_type() ;
  6  begin
  7   my_object.id := 123;
  8   my_object2.id := 123;
  9   my_table.extend;
 10   my_table(1) := my_object;
 11   dbms_output.put_line('id='||my_table(1).id);
 12   my_table(1) := my_object2;
 13   dbms_output.put_line('id2='||my_table(1).id);
 14  end;
 15  /
declare
*
ERROR at line 1:
ORA-06530: Reference to uninitialized composite
ORA-06512: at line 7

passing plsql type variable and unit testing code using plsql

Saurabh, June 06, 2012 - 4:00 pm UTC

The DDL statement

create type examplearrayobject as object( z varchar2(300));

create type myexampleArrayType as table of examplearrayobject;

create or replace procedure collectionexample(example IN myexampleArrayType)
as
begin
for i in 1 .. example.count
loop
dbms_output.put_line(example(i).z);
end loop;
end;
/


Declaration block for execution from sqlplus
declare
l_data myexampleArrayType := myexampleArrayType( examplearrayobject('ab'));
i BINARY_INTEGER := 1;
begin
collectionexample(l_data(i));
end;
/


Error while execution:
collectionexample(l_data(i));
*
ERROR at line 5:
ORA-06550: line 5, column 1:
PLS-00306: wrong number or types of arguments in call to 'COLLECTIONEXAMPLE'
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored


Please provide assistance in resolving above error.

Thank You
Saurabh
Tom Kyte
June 06, 2012 - 5:36 pm UTC

you said you were going to pass an array:

create or replace procedure collectionexample(example IN myexampleArrayType)


you passed an element of an array

collectionexample(l_data(i));


you passed an examplearrayobject, not a myexampleArrayType

SUMIT, October 09, 2012 - 5:38 am UTC

Hi tom ,
can we insert record into type same like table.
like :-
INSERT INTO COURSE (STUDENT_LIST)(CAST(MULTISET
(SELECT student_name, student_address,grade FROM GRADE, STUDENT)
));
this one i got on net,I don't know it is correct or not.i tried but i am getting. What is student_list is this normal type?
do u have syntax for it ...
please help me..........

Tom Kyte
October 09, 2012 - 12:47 pm UTC

need a full example, a single insert without schema isn't something anyone can comment on.

want to use object with record no table

sonia, September 23, 2013 - 6:54 pm UTC

after defining the type object, in this example we define the type table, but then this parameter allows me to insert more than one row and only want one, how do i use object with one record
thanks

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