Skip to Main Content
  • Questions
  • How to populate a TABLE type based on an OBJECT TYPE

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Hernan.

Asked: June 25, 2018 - 11:01 pm UTC

Last updated: April 16, 2020 - 11:35 am UTC

Version: 12.1.0.1

Viewed 10K+ times! This question is

You Asked

Hi All
I'm trying to create a table type based on a object type, but when I try to populate it, I get an error. I copied the syntax from several places, and still, it seems i'm doing something wrong.

Here's the code

CREATE OR REPLACE TYPE T1_OBJ AS OBJECT
(field1 VARCHAR2(10),
 field2 INTEGER);
 
CREATE OR REPLACE TYPE T1_TAB AS TABLE OF T1_OBJ;

DECLARE
  v_t1 T1_TAB;
BEGIN
  v_t1 := T1_TAB('Hernan',NULL);
END;

now, for some reason, if i put all null values, it works

DECLARE
  v_t1 T1_TAB;
BEGIN
  v_t1 := T1_TAB(NULL,NULL);
END;


But whenever i try to put ANY value, it just returns an error message. "Wrong number or type of arguments"
Can anyone please point me in the right direction? There are not many samples online.
Thanks in advance.

and Connor said...

We often create a table of objects, and then think of it like an "Excel-like" structure, ie, I have an 'n' rows and each row has 'p' columns, where 'p' is the number of attributes in the object.

But this is not really the case. It really is an array *of objects*.

So if we want to populate that array, the elements inside it must be *objects*.

SQL> CREATE OR REPLACE TYPE T1_OBJ AS OBJECT
  2  (field1 VARCHAR2(10),
  3   field2 INTEGER);
  4  /

Type created.

SQL>
SQL> CREATE OR REPLACE TYPE T1_TAB AS TABLE OF T1_OBJ;
  2  /

Type created.

SQL>
SQL> DECLARE
  2    v_t1 T1_TAB;
  3  BEGIN
  4    v_t1 := T1_TAB('Hernan',NULL);
  5  END;
  6  /
  v_t1 := T1_TAB('Hernan',NULL);
          *
ERROR at line 4:
ORA-06550: line 4, column 11:
PLS-00306: wrong number or types of arguments in call to 'T1_TAB'
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored


SQL>
SQL>
SQL> DECLARE
  2    v_t1 T1_TAB;
  3  BEGIN
  4    v_t1 := T1_TAB(T1_OBJ('Hernan',NULL));
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> DECLARE
  2    v_t1 T1_TAB;
  3  BEGIN
  4    v_t1 := T1_TAB(
  5                T1_OBJ('Hernan',NULL),
  6                T1_OBJ('Connor',5),
  7                T1_OBJ('Mike',10),
  8                T1_OBJ('Susie',NULL)
  9                );
 10  END;
 11  /

PL/SQL procedure successfully completed.


Rating

  (2 ratings)

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

Comments

How to show the contents in that format?

Hernan Baioni, June 26, 2018 - 10:40 pm UTC

So, that's great, it worked.
I would have never guessed the format.
But now i have a problem. With that format, i stored the value in v_t1.
How do I show the contents with dbms_output?
Thanks in advance.
Connor McDonald
June 27, 2018 - 1:21 am UTC

SQL> set serverout on
SQL> DECLARE
  2  v_t1 T1_TAB;
  3  BEGIN
  4  v_t1 := T1_TAB(
  5    T1_OBJ('Hernan',NULL),
  6    T1_OBJ('Connor',5),
  7    T1_OBJ('Mike',10),
  8    T1_OBJ('Susie',NULL)
  9    );
 10
 11   for i in 1 .. v_t1.count
 12   loop
 13     dbms_output.put_line(v_t1(i).field1);
 14     dbms_output.put_line(v_t1(i).field2);
 15   end loop;
 16
 17  END;
 18   /
Hernan
Connor
5
Mike
10
Susie

PL/SQL procedure successfully completed.


Check out resources to help you out at

https://devgym.oracle.com
https://livesql.oracle.com


It helped me solve my query

PriyankaJ, April 09, 2020 - 10:13 am UTC

Thanks Oracle gurus.

I had created my object types in the following manner :

CREATE OR REPLACE TYPE o_obj_a AS OBJECT
( field_1a VARCHAR2(20),
field_2a VARCHAR2(20)
);
/

CREATE OR REPLACE TYPE o_obj_a_t IS TABLE OF o_obj_a;
/

CREATE OR REPLACE TYPE o_obj_b AS OBJECT
( field_1b VARCHAR2(20),
field_2b VARCHAR2(20)
);
/

CREATE OR REPLACE TYPE o_obj_b_t IS TABLE OF o_obj_b;
/

CREATE OR REPLACE TYPE o_obj_c AS OBJECT
( field_1c VARCHAR2(20),
field_2c VARCHAR2(20),
a_details o_obj_a_t,
b_details o_obj_b_t
);
/

CREATE OR REPLACE TYPE O_OBJ_C_T IS TABLE OF O_OBJ_C;
/

I wanted to initialize all the object types but I wasn't able to do so through an anonymous block. This is where your block helped me out.

set serveroutput on ;
DECLARE
a_details o_obj_a_t;
b_details o_obj_b_t;
c_details o_obj_c_t;
BEGIN
a_details := o_obj_a_t(o_obj_a('a_data1','a_data2'));
b_details := o_obj_b_t(o_obj_b('b_data1','b_data2'));
c_details := o_obj_c_t(o_obj_c('c_data1','c_data2',a_details,b_details));
FOR i IN 1..c_details.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE('Field 1 of C : '||c_details(i).field_1c);
DBMS_OUTPUT.PUT_LINE('Field 2 of C : '||c_details(i).field_2c);
DBMS_OUTPUT.PUT_LINE('Field 1 of A : '||c_details(i).a_details(i).field_1a);
DBMS_OUTPUT.PUT_LINE('Field 2 of A : '||c_details(i).a_details(i).field_2a);
DBMS_OUTPUT.PUT_LINE('Field 1 of B : '||c_details(i).b_details(i).field_1b);
DBMS_OUTPUT.PUT_LINE('Field 2 of B : '||c_details(i).b_details(i).field_1b);
END LOOP;
END;
/

Got my output :

Field 1 of C : c_data1
Field 2 of C : c_data2
Field 1 of A : a_data1
Field 2 of A : a_data2
Field 1 of B : b_data1
Field 2 of B : b_data1

PL/SQL procedure successfully completed.

Thanks once again. :)
Connor McDonald
April 16, 2020 - 11:35 am UTC

Glad we could help!

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