Thanks for the question, Seetharaman.

Asked: July 11, 2000 - 1:00 am UTC

Last updated: December 15, 2022 - 5:12 pm UTC

Version: 8.1.5

Viewed 1000+ times

You Asked

Hi Tom
Greetings..

My question this time is how to create a nested table and how to retrieve records out of it??

Pls reply me with an example

Thanks in advance

regards

Seetharaman Srinivasan

and Tom said...

Here is an example. I will use a "complex" object type called "myScalarType"

ops$tkyte@8i> create or replace type
2 myScalarType as object
3 ( x int,
4 y date
5 )
6 /

Type created.

Then I create a nested table type of that scalar object:
ops$tkyte@8i>
ops$tkyte@8i> create or replace type
2 myArrayType as table of myScalarType
3 /

Type created.

and finally, a table with a column of that type:
ops$tkyte@8i>
ops$tkyte@8i>
ops$tkyte@8i> create table x
2 ( pk int primary key,
3 array myArrayType
4 )
5 nested table array store as X_Array_Data
6 (
7 (PRIMARY KEY (nested_table_id, X))
8 ORGANIZATION INDEX
9 )
10 /

Table created.

I like to store the nested tables as IOTS. You in general will always want to index the nested_table_id (hidden foreign key back to the parent) so this makes sense to build the table with this key in mind. Now, to create some data and query it:

ops$tkyte@8i>
ops$tkyte@8i>
ops$tkyte@8i> insert into x values
2 ( 1, myArrayType( myScalarType(1,sysdate ),
3 myScalarType(2,sysdate+2) ,
4 myScalarType(3,sysdate-2)
5 )
6 );

1 row created.

ops$tkyte@8i> insert into x values
2 ( 2, myArrayType( myScalarType(1,sysdate+100),
3 myScalarType(2,sysdate-2) ,
4 myScalarType(3,sysdate+243)
5 )
6 );

1 row created.

I can either select arrays of data with each row:

ops$tkyte@8i>
ops$tkyte@8i>
ops$tkyte@8i> select x.pk, x.array array from X x
2 /

PK ARRAY(X, Y)
---------- ----------------------------------------
1 MYARRAYTYPE(MYSCALARTYPE(1, '11-JUL-00')
, MYSCALARTYPE(2, '13-JUL-00'), MYSCALAR
TYPE(3, '09-JUL-00'))

2 MYARRAYTYPE(MYSCALARTYPE(1, '19-OCT-00')
, MYSCALARTYPE(2, '09-JUL-00'), MYSCALAR
TYPE(3, '11-MAR-01'))

Or I can treat a single instance of a nested table as a "table" itself:

ops$tkyte@8i>
ops$tkyte@8i> select *
2 from THE (select array from x where pk=1)
3 /

X Y
---------- ---------
1 11-JUL-00
2 13-JUL-00
3 09-JUL-00

Rating

(7 ratings)

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

Comments

code no work.

steve shiflett, November 19, 2001 - 1:45 pm UTC

This doesn't seem to work for 9i

ops$tkyte@8i> create table x
2 ( pk int primary key,
3 array myArrayType
4 )
5 nested table array store as X_Array_Data
6 (
7 (PRIMARY KEY (nested_table_id, X))
8 ORGANIZATION INDEX
9 )
10 /

November 19, 2001 - 4:29 pm UTC

code works great in 9i:

ops$tkyte@ORA9I.WORLD> create or replace type
  2  myScalarType as object
  3  ( x int,
  4    y date
  5  )
  6  /

Type created.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> create or replace type
  2  myArrayType as table of myScalarType
  3  /

Type created.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> create table x
  2  ( pk       int primary key,
  3    array myArrayType
  4  )
  5  nested table array store as X_Array_Data
  6  (
  7    (PRIMARY KEY (nested_table_id, X))
  8     ORGANIZATION INDEX
  9  )
 10  /

Table created.

one would REALLY need to supply the error message if you expect any meaningful replies!!!

you most likely forgot to create the nested table types -- but without the error message, I cannot even hazzard a good guess.

What's the advantage to this?

John, November 20, 2001 - 12:03 pm UTC

Great example!
But Why?
Seems like more work to me.

Do you use this in the real world?

November 20, 2001 - 12:26 pm UTC

see also
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2010064718579 <code>

In real life, I do not use them as a persistent storage mechanism. I use them as a datatype in my plsql code constantly. I use real parent/child tables personally for persistent storage.

Code does not work!!!

Bhushan, June 11, 2008 - 2:45 pm UTC

Hi Thomas,
Could you please let me know what is wrong in the following code ?
DB version: 10.2.0.3.0
OS is Vista
CREATE OR REPLACE TYPE emp_details_obj AS OBJECT (address VARCHAR2(100),per_gf VARCHAR2(20));
CREATE OR REPLACE TYPE type_emp_details
(a NUMBER,c emp_details_obj);

CREATE TABLE emp (x INT,b type_emp_details)
NESTED TABLE b STORE AS m
#1 what is the significance of M here...?

INSERT INTO emp VALUES (1,type_emp_details(emp_details_obj('i am','good')));
#2 The above insert throws back ORA 00600
Also,
In your example below in every of your insert you have inserted the scalar value 3 times like,

insert into x values
( 1, myArrayType( myScalarType(1,sysdate ),
myScalarType(2,sysdate+2) ,
myScalarType(3,sysdate-2)
)
);
specifying three times is Mandatory or its just an example and a co-incidence that you have mentioned it thrice everywhere..?
Sorry but since it is not working at my end, cannot test it.
Looking forward for your reply.

Cheers!!!
Bhushan

June 11, 2008 - 8:49 pm UTC

this type does not compute:

CREATE OR REPLACE TYPE type_emp_details
(a NUMBER,c emp_details_obj);

does not compute???

Bhushan, June 12, 2008 - 12:15 am UTC

hi,
Dont understand, what do you mean by does not compute..?
I could create that type on my machine..do you mean although it may get created it is not a valid structure...?

Regards,
Bhushan

Please Ignore Above Message.

Bhushan, June 12, 2008 - 12:17 am UTC

you may use this create statement instead of it and continue...

CREATE OR REPLACE TYPE type_emp_details IS TABLE OF emp_details_obj

Thanks,
Bhushan

June 12, 2008 - 7:20 am UTC

Now that we have code that actually runs, I cannot reproduce your issue at all, and given that the example is so so so simple, I have to guess that you are doing something entirely different from what you say you did - since you gave an example that would never compile, this is highly likely (that what you did bears no resemblance to what you actually did)

Please use cut and paste just like I do.

M is the name of the nested table that is created for you, see below, you can describe it, it is a child table:


ops$tkyte%ORA10GR2> CREATE OR REPLACE TYPE emp_details_obj AS OBJECT (address VARCHAR2(100),per_gf VARCHAR2(20))
  2  /

Type created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> CREATE OR REPLACE TYPE type_emp_details IS TABLE OF emp_details_obj
  2  /

Type created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> CREATE TABLE emp (x INT,b type_emp_details)
  2  NESTED TABLE b STORE AS m
  3  /

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> INSERT INTO emp VALUES (1,type_emp_details(emp_details_obj('i am','good')));

1 row created.

ops$tkyte%ORA10GR2> select * from emp;

         X
----------
B(ADDRESS, PER_GF)
-------------------------------------------------------------------------------
         1
TYPE_EMP_DETAILS(EMP_DETAILS_OBJ('i am', 'good'))


ops$tkyte%ORA10GR2> desc m
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 ADDRESS                                           VARCHAR2(100)
 PER_GF                                            VARCHAR2(20)

You are Right!!!

Bhushan, June 14, 2008 - 2:00 am UTC

Hey,
right!!
I had messed it up somewhere in between.
It works fine at my end too.
Thank you for your time.

cheers!!!
Bhushan

Nested table and bulk inserts.

Rajeshwaran, Jeyabal, December 07, 2022 - 2:27 pm UTC

Team,

the below example was from 21c database and can you help us to understand why it error ORA-00902 here ?

demo@PDB1> create or replace package mypkg
  2  as
  3     type array is table of emp%rowtype ;
  4     l_data array ;
  5  end;
  6  /

Package created.

demo@PDB1> variable x refcursor
demo@PDB1> declare
  2     l_data mypkg.array ;
  3  begin
  4     select * bulk collect into l_data
  5     from emp
  6     where deptno = 10;
  7
  8     dbms_output.put_line('I got '||l_data.count||' rows');
  9
 10     open :x for select empno,ename,job from table(l_data);
 11  end;
 12  /
I got 3 rows

PL/SQL procedure successfully completed.

demo@PDB1> print x

     EMPNO ENAME      JOB
---------- ---------- ---------
      7782 CLARK      MANAGER
      7839 KING       PRESIDENT
      7934 MILLER     CLERK

demo@PDB1> create table emp3 as select * from emp where 1 = 0 ;

Table created.

demo@PDB1> declare
  2     l_data mypkg.array ;
  3  begin
  4     select * bulk collect into l_data
  5     from emp
  6     where deptno = 10;
  7
  8     dbms_output.put_line('I got '||l_data.count||' rows');
  9
 10     insert into emp3(empno,ename,job)
 11     select empno,ename,job
 12     from table(l_data);
 13
 14     dbms_output.put_line('Rows affected = '||sql%rowcount );
 15  end;
 16  /
I got 3 rows
declare
*
ERROR at line 1:
ORA-00902: invalid datatype
ORA-06512: at line 10

December 15, 2022 - 5:12 pm UTC

It's not supported

If the PL/SQL-only data type is an associative array, it cannot be used within a non-query DML statement (INSERT, UPDATE, DELETE, MERGE) nor in a subquery.

https://docs.oracle.com/database/121/LNPLS/release_changes.htm#GUID-57E439FB-B196-46CB-857C-0ADAB32D9EA0

You can workaround this by using FORALL inserts.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

code no work.

What's the advantage to this?

Code does not work!!!

does not compute???

Please Ignore Above Message.

You are Right!!!

Nested table and bulk inserts.

More to Explore

PL/SQL demos

PL/SQL docs

Nested Table

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

code no work.

What's the advantage to this?

Code does not work!!!

does not compute???

Please Ignore Above Message.

You are Right!!!

Nested table and bulk inserts.

More to Explore

PL/SQL demos

PL/SQL docs