Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Sathiamathi.

Asked: November 19, 2001 - 11:14 pm UTC

Last updated: April 22, 2013 - 2:35 pm UTC

Version: 8i

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I have a table with following columns. One of the column is consider as nested table.


tb_users Datatype
-------------------------------
1. userid varchar2(15)
2. businessname varchar2(50)
3. userprivileges ourprivileges
( Nested table column having type of privilegetype (privilege_id number(2),privilege_name varchar2(45))


Initially we have assigned some set of application privilege to the user..

Now i wanted to find user with privilege_id = 2

select userid, b.privilege_id from tb_users , table(userprivileges) b
where b.privilege_id = 2

I got the expected output.. we want to delete this privilege from all the user.

i want to delete the nested table record with privilege_id = 2(child table) for all the user

I can delete privilege of one particular user using following query.

delete from the ( select userprivileges from tb_users where
userid ='sathya') where privilege_id = 2

How to do it for all the user with single query.. Instead of writing procedure with cursor to do the same.

Thanks in Advance.

Regards,

Sathiamathi




















create table tb_users (userid varchar2(15),privileges
userid





and Tom said...

Ahh, the foibles of nested tables! This is (in my opinion) one of the issues with "OO" approaches here.

You want to have this nice representation as an object -- yet we constantly need a relational view of the data to get the job done.

My favorite example is a "bank". The application for the teller would like to have an object that represents the customer. They see the customer, with a list of accounts that have lists of transactions. Very nice for the teller. However at the end of the day, someone asks "how much money is in the bank"? Well, now we need a totally different view of the data -- from the perspective of transactions, not people (you don't want to go to each person object and say "did you do anything today?" that would not be realistic, you just want to sum up transactions)....


This is a small extract from my book that describes this issue and offers a solution for ONE OFF fixes (all of the details are in the book)


....
Previously I stated: 'We must always join, we cannot query the EMP data alone' but then followed that up with a caveat: 'you can if you really need to'. It is undocumented and not supported, so use it only as a last ditch method. Where it will come in most handy is if you ever need to mass update the nested table (remember, we would have to do that through the DEPT table with a join). There is an undocumented hint, NESTED_TABLE_GET_REFS, used by EXP and IMP to deal with nested tables. It will also be a way to see a little more about the physical structure of the nested tables. This magic hint is easy to discover after you export a table with a nested table. For example, I exported the table above in order to get its 'larger' definition from IMP. After doing the export, I found the following SQL in my shared pool (V$SQL table):

SELECT /*+NESTED_TABLE_GET_REFS+*/ NESTED_TABLE_ID,SYS_NC_ROWINFO$ FROM "TKYTE"."EMPS_NT"

A simple query like 'select sql_text from v$sql where upper(sql_text) like ?%EMP%' found it for me. If you run this, you'll get some 'magic' results:

tkyte@TKYTE816> SELECT /*+NESTED_TABLE_GET_REFS+*/
2 NESTED_TABLE_ID,SYS_NC_ROWINFO$
3 FROM "TKYTE"."EMPS_NT"
4 /

NESTED_TABLE_ID SYS_NC_ROWINFO$(EMPNO, ENAME,
-------------------------------- ------------------------------
9A39835005B149859735617476C9A80E EMP_TYPE(7782, 'CLARK',
'MANAGER', 7839, '09-JUN-81',
2450, 100)

9A39835005B149859735617476C9A80E EMP_TYPE(7839, 'KING',
'PRESIDENT', NULL,
'17-NOV-81', 5000, 100)

Well, this is somewhat surprising, if you describe this table:

tkyte@TKYTE816> desc emps_nt
Name Null? Type
----------------------------------- -------- ------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)

These two columns don?t even show up. They are part of the hidden implementation of nested tables. The NESTED_TABLE_ID is really a foreign key to the parent table DEPT_AND_EMP. DEPT_AND_EMP actually has a hidden column in it that is used to join to EMPS_NT. The SYS_NC_ROWINF$ 'column' is a magic column, it is more of a function then a column. The nested table here is really an object table (it is made of an object type) and SYS_NC_INFO$ is the internal way Oracle references the row as an object, instead of referencing each of the scalar columns. Under the covers, all Oracle has done for us is to implement a parent/child table with system generated primary and foreign keys. If we dig a little further, we can query the 'real' data dictionary to see all of the columns in the DEPT_AND_EMP table:

tkyte@TKYTE816> select name
2 from sys.col$
3 where obj# = ( select object_id
4 from user_objects
5 where object_name = 'DEPT_AND_EMP' )
6 /

NAME
------------------------------
DEPTNO
DNAME
LOC
EMPS
SYS_NC0000400005$


tkyte@TKYTE816> select SYS_NC0000400005$ from dept_and_emp;

SYS_NC0000400005$
--------------------------------
9A39835005B149859735617476C9A80E
A7140089B1954B39B73347EC20190D68
20D4AA0839FB49B0975FBDE367842E16
56350C866BA24ADE8CF9E47073C52296

The weird looking column name, SYS_NC0000400005$, is the system-generated key placed into the DEPT_AND_EMP table. If you dig even further you will find that Oracle has placed a unique index on this column. Unfortunately however, it neglected to index the NESTED_TABLE_ID in EMPS_NT. This column really needs to be indexed, as we are always joining FROM DEPT_AND_EMP to EMPS_NT. This is an important thing to remember about nested tables if you use them with all of the defaults as I did above ? always index the NESTED_TABLE_ID in the nested tables!

I've gotten off of the track though at this point. I was talking about how to treat the nested table as if it were a real table. The NESTED_TABLE_GET_REFS hint does that for us. We can use that like this:

tkyte@TKYTE816> select /*+ nested_table_get_refs */ empno, ename
2 from emps_nt where ename like '%A%';

EMPNO ENAME
---------- ----------
7782 CLARK
7876 ADAMS
7499 ALLEN
7521 WARD
7654 MARTIN
7698 BLAKE
7900 JAMES
7 rows selected.

tkyte@TKYTE816> update /*+ nested_table_get_refs */ emps_nt
2 set ename = initcap(ename);
14 rows updated.

tkyte@TKYTE816> select /*+ nested_table_get_refs */ empno, ename
2 from emps_nt where ename like '%a%';

EMPNO ENAME
---------- ----------
7782 Clark
7876 Adams
7521 Ward
7654 Martin
7698 Blake
7900 James
6 rows selected.

Again, this is not a documented supported feature. It may not work in all environments. It has a specific functionality; for EXP and IMP to work. This is the only environment it is assured to work in. Use it at your own risk. Use it with caution though and do not put it into production code. Use it for one-off fixes of data or to see what is in the nested table out of curiosity. The supported way to report on the data is to unnest it like this:

tkyte@TKYTE816> select d.deptno, d.dname, emp.*
2 from dept_and_emp D, table(d.emps) emp
3 /

And that is what you should use in queries and production code.
....

Rating

  (15 ratings)

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

Comments

How many levels of nesting one can have in nested table?

A reader, July 15, 2003 - 5:38 pm UTC


Tom Kyte
July 15, 2003 - 5:53 pm UTC

in 8i, one
in 9i, practically speaking, just a few - it gets VERY cumbersome beyond that.

the nested_table_get_refs hint problem

A reader, April 24, 2004 - 4:13 pm UTC

Hi Tom
This is just something I found and thought
may be you have some comment on it.
The nested_table_get_refs hint (which is now
officially supported as a way of unnesting a
nested table column) does not work in pl/sql
cursors as shown below:
----------------
benchmark@ORA10G> set echo on
benchmark@ORA10G> column name format a20
benchmark@ORA10G> desc components_with_parts
Name Null? Type
--------------------- ----- ----
ID NOT NULL NUMBER
COMPONENT_NAME VARCHAR2(50)
PARTS PART_TYPE_TAB <-- nested table type

benchmark@ORA10G> desc parts_nt <-------- the nested table
Name Null? Type
--------------------- ----- ----
D NUMBER
PART_NAME NOT NULL VARCHAR2(50)
PART_DESC VARCHAR2(500)

benchmark@ORA10G> select /*+ nested_table_get_refs */ id,
2 part_name, part_desc
3 from parts_nt;

ID PART_NAME PART_DESC
---------- ---------- --------------------
1 part1 part1 description
1 part2 part2 description

benchmark@ORA10G> begin
2 for i in ( select /*+ nested_table_get_refs */ id,
3 part_name, part_desc
4 from parts_nt )
5 loop
6 dbms_output.put_line( i.id );
7 end loop;
8 end;
9 /
from parts_nt )
*
ERROR at line 4:
ORA-06550: line 4, column 19:
PL/SQL: ORA-22812: cannot reference nested table column's storage table
ORA-06550: line 2, column 12:
PL/SQL: SQL Statement ignored
ORA-06550: line 6, column 27:
PLS-00364: loop index variable 'I' use is invalid
ORA-06550: line 6, column 5:
PL/SQL: Statement ignored


benchmark@ORA10G> begin
2 for i in ( select p.id, p.part_name, p.part_desc
3 from
4 components_with_parts c, TABLE( parts) p)
5 loop
6 dbms_output.put_line( i.id );
7 end loop;
8 end;
9 /
1
1

PL/SQL procedure successfully completed.

benchmark@ORA10G> begin
2 for i in ( select /*+ nested_table_get_refs */ id,
3 part_name, part_desc
4 from parts_nt )
5 loop
6 dbms_output.put_line( i.id );
7 end loop;
8 end;
9 /
from parts_nt )
*
ERROR at line 4:
ORA-06550: line 4, column 19:
PL/SQL: ORA-22812: cannot reference nested table column's storage table
ORA-06550: line 2, column 12:
PL/SQL: SQL Statement ignored
ORA-06550: line 6, column 27:
PLS-00364: loop index variable 'I' use is invalid
ORA-06550: line 6, column 5:
PL/SQL: Statement ignored


benchmark@ORA10G> declare
2 l_cursor sys_refcursor;
3 l_id number;
4 begin
5 open l_cursor for
6 select /*+ nested_table_get_refs */ id, part_name, part_desc
7 from parts_nt;
8 loop
9 fetch l_cursor into l_id;
10 exit when l_cursor%notfound;
11 dbms_output.put_line( l_id );
12 end loop;
13 end;
14 /
from parts_nt;
*
ERROR at line 7:
ORA-06550: line 7, column 8:
PL/SQL: ORA-22812: cannot reference nested table column's storage table
ORA-06550: line 6, column 3:
PL/SQL: SQL Statement ignored


benchmark@ORA10G> spool off
-------------------------

This may have to do with the fact that the
hint is a way to return the nested table records
as a "locator" (instead of as a value.) Of course
the workaround is to use the join with TABLE construct
as shown above.

Any comments?

Menon:)


Tom Kyte
April 26, 2004 - 5:41 am UTC 

looks like a product 'issue' with parsing the static sql in plsql.  NDS works around it.



ops$tkyte@ORA9IR2> create table t ( x int, y mytype ) nested table y store as y_tab
  2  /
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1, mytype(1,2,3) );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select /*+ nested_table_get_refs */ * from y_tab;
 
COLUMN_VALUE
------------
           1
           2
           3
 
ops$tkyte@ORA9IR2> begin
  2  for x in ( select /*+ nested_table_get_refs */ * from y_tab )
  3  loop
  4  null;
  5  end loop;
  6  end;
  7  /
for x in ( select /*+ nested_table_get_refs */ * from y_tab )
                                                    *
ERROR at line 2:
ORA-06550: line 2, column 53:
PL/SQL: ORA-22812: cannot reference nested table column's storage table
ORA-06550: line 2, column 10:
PL/SQL: SQL Statement ignored
 
 
ops$tkyte@ORA9IR2> declare
  2    c sys_refcursor;
  3    n int;
  4  begin
  5    open c for 'select /*+ nested_table_get_refs */ * from y_tab';
  6    loop
  7          fetch c into n;
  8          exit when c%notfound;
  9          dbms_output.put_line( n );
 10    end loop;
 11    close c;
 12  end;
 13  /
1
2
3
 
PL/SQL procedure successfully completed.

Nice

James, April 26, 2004 - 7:59 am UTC 

Hi Tom,
The following nested table creation fails.Why does this
happen?

SQL> create or replace type myt as object(x number);
  2  /

Type created.

SQL> create or replace type mytype as table of myt;
  2  /
create or replace type mytype as table of myt;
*
ERROR at line 1:
ORA-06545: PL/SQL: compilation error - compilation aborted 
ORA-06550: line 0, column 0: 
PLS-00565: MYTYPE must be completed as a potential REF target (object type) 

How to correct this?

Tom Kyte
April 26, 2004 - 9:18 am UTC 

ops$tkyte@ORA9IR2> create or replace type myt as object(x number);
  2  / 
Type created.
 
ops$tkyte@ORA9IR2> create or replace type mytype as table of myt;
  2  / 
Type created.


what version of sqlplus are you using?  database????

OK

James, April 26, 2004 - 10:27 am UTC

Hi Tom,
Thanks for your reply.I use 9I r2.I don't know why this happens.And Is there any hint like /*+varray_get_refs*/
that enables us to query directly the Varray collection?
Please do reply.
Bye!


Tom Kyte
April 26, 2004 - 10:35 am UTC

no, nested tables really are tables -- they as a child table, they exist

a varray is just a glob of data.... it is not in fact a table.

To James -

Menon:), April 26, 2004 - 10:52 am UTC

"Is there any
hint like /*+varray_get_refs*/
that enables us to query directly the Varray collection?"
There is no such hint - However, you could always
use the TABLE construct as follows to access
it in SQL (not sure if this is what you were looking for)
----
enchmark@ORA92I> drop table t1;

Table dropped.

benchmark@ORA92I> create or replace type varray_vc as varray(10) of varchar2(50);
2 /

Type created.

benchmark@ORA92I> create table t1
2 (
3 x number,
4 y varray_vc
5 );

Table created.

benchmark@ORA92I>
benchmark@ORA92I> insert into t1 values ( 1, varray_vc( 'xx','yy','zz' ) );

1 row created.

benchmark@ORA92I> commit;

Commit complete.

benchmark@ORA92I> select a.column_value
2 from t1, TABLE( t1.y) a;

xx
yy
zz

benchmark@ORA92I>
----------


OK

James, April 26, 2004 - 2:14 pm UTC

Hi Tom,
Thanks for your reply.But the use of the hint "nested_table_
get_refs" really astonished me.I thought there would be a
parallel "varray_get_refs" since varray seem to be a collection.To Menon :)Thanks for working with the code.But I know this some years back itself.i.e TABLE operator.


Tom Kyte
April 27, 2004 - 3:53 am UTC

they are both "collections" but one is a table (nested tables are really child tables in "disguise", one of the reasons I don't use them for persistent storage -- it is more efficient and more usable to do it yourself!) and the other is just a glob of data that a client must parse and pull apart into an array.

Nice

Catherine, April 27, 2004 - 1:13 am UTC 

Dear Tom,
I have a doubt with collections.When we have a Nested Table or Varray,
How to count the number of elements with in them.In Oracle 10G we have
a direct way of using the "Cardinality" function.But prior 10G,How to
do that?I have a sample structure for you.

I also want to apply the other group functions like "max,min,sum,avg" etc to the varray elements.

SQL> create type arr as array(5) of number;

SQL> create table t(desc varchar2(30),Multiples arr);

SQL> insert into t values('Two',arr(2,4,6,8));

SQL>insert into t values('Three',arr(3,6,9,12,15));

SQL>insert into t values('Four',arr(4,8,12,16,20));

Could you please help in this regard?

Tom Kyte
April 28, 2004 - 11:52 am UTC 

using TABLE() is should be straight forward from the above.  TABLE() just "un-nested" the varray -- making the structure flat.  count, min, max, et.al work on that:

ops$tkyte@ORA9IR2> select des,
  2         count(*),
  3             min(column_value) mi,
  4             max(column_value) ma,
  5             avg(column_value) av
  6    from t t, table(t.multiples)
  7   group by des
  8  /
 
DES     COUNT(*)         MI         MA         AV
----- ---------- ---------- ---------- ----------
Four           5          4         20         12
Three          5          3         15          9
Two            4          2          8          5

system-generated key

Branka, August 02, 2004 - 10:52 pm UTC

If I have 2 nested tables in one table, how will I know which system-generated key belog to which nested table?


select name
from sys.col$
where obj# = ( select object_id
from user_objects
where object_name = 'TEETIME')
/



Tom Kyte
August 03, 2004 - 7:43 am UTC

you have no need to know. by using the nested table construct, you've bought into "much magic goes on here, I'm not to be aware of the fact that there are hidden columns, they are not relevant nor exposed to me"


they'd probably be in "column order", haven't really looked into it

Index on nested table

Branka, August 12, 2004 - 1:38 pm UTC

Is this correct in Oracle 9i version too?
"
This is an
important thing to remember about nested tables if you use them with all of the
defaults as I did above ? always index the NESTED_TABLE_ID in the nested tables!
"

Tom Kyte
August 12, 2004 - 1:43 pm UTC 

ops$tkyte@ORA9IR2> create type array as table of number
  2  /
 
Type created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t
  2  ( x int,
  3    y array
  4  )
  5  nested table y store as y_tab
  6  /
 
Table created.
 
ops$tkyte@ORA9IR2> select table_name, index_name from user_indexes;
 
TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
T                              SYS_C008469
 
ops$tkyte@ORA9IR2>



Yes, the only index added by default is the unique index on the hidden column magically added to table t.

I would urge you to rethink using nested tables as a persistent storage mechanism!

Using Nested tables

Branka, August 12, 2004 - 3:59 pm UTC

What would be argument to rewrite whole system not to use nested tables? It is central part of the system now, and people who developed the system are very proud to have implemented nested tables.

Branka


Tom Kyte
August 12, 2004 - 4:28 pm UTC

i would not rewrite an existing, in production system.

However, are they happy with the extra 16 byte raw in the parent..
And the extra unique constraint on the parent...
And that they cannot query these nested tables as regular tables...

Using nested tables

Branka, August 12, 2004 - 4:35 pm UTC

They say that system is much faster after changing regular tables into nested tables, because all data that they need to show on the screen are in memory.

Tom Kyte
August 12, 2004 - 4:43 pm UTC

umm, er? huh?

it is just a parent child table - we "join". There is nothing you cannot do using an OR-view here (using your own primary key you already have in the parent, and the foreign key you already had in the child).

Gives you "nested table query capability" and "the raw performance of finely tuned tables" and "the ability to actually use the child table"

But even with old fashioned parent/child tables -- all of the data you need to show on the screen is in memory? i'm missing something here.

nested table index

Branka, August 13, 2004 - 4:24 pm UTC 

If I have table 
SQL> desc v
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 COURSE_ID                                 NOT NULL NUMBER
 BOOKING                                            VBOOK

SQL> desc vbook
 vbook TABLE OF BOOK_TYPE
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------
 TEETIME                                            NUMBER(4)
 ROUND_SEQ                                          NUMBER(3)
 ROUNDS                                             NUMBER


CREATE INDEX NDX_BOOK_TAB ON BOOK_TAB
(NESTED_TABLE_ID, TEETIME)

Would I still have to consider making index:

CREATE INDEX NEW_NDX_BOOK_TAB ON BOOK_TAB
(NESTED_TABLE_ID)

Or first one will cover it too?

Tom Kyte
August 13, 2004 - 6:44 pm UTC

first one suffices.

A reader, November 02, 2004 - 1:47 pm UTC


it changed my life

A reader, December 10, 2004 - 11:55 am UTC

NOW my db is both relational and object-oriented

OO Views and Nested Tables

Jon T, April 09, 2013 - 6:30 pm UTC

OK, let's say we take the approach used in your book and make OO views on relational tables and apply it to the bank scenario you gave.

An account has transactions. It could have tons. There are many times I don't want them all sent to the client and would like to return a locator instead of actually querying the transactions table, building the collection, and sending the whole thing over the wire.

Is there any way to have Oracle send a locator back for this collection (or a nice workaround)? The closest think I could think of to having NESTED_TABLE_GET_REFS was to return a table of transaction refs. I could make a method that returns the table but sort of defeats the purpose of having the collection to begin with.
Tom Kyte
April 22, 2013 - 2:35 pm UTC

well, in the book - I said by all means use them if you like - but I hope I didn't make you think I was saying "definitely use them"

the relational model would be really good for this :) the 'ref' I would return would be the foreign key into the transaction table. Since most of the times it sounds like you wouldn't want all of them....

or use a CURSOR()

ops$tkyte%ORA11GR2> select deptno, dname, cursor( select ename from scott.emp where emp.deptno = dept.deptno ) from scott.dept;

    DEPTNO DNAME          CURSOR(SELECTENAMEFR
---------- -------------- --------------------
        10 ACCOUNTING     CURSOR STATEMENT : 3

CURSOR STATEMENT : 3

ENAME
----------
CLARK
KING
MILLER

        20 RESEARCH       CURSOR STATEMENT : 3

CURSOR STATEMENT : 3

ENAME
----------
SCOTT
SMITH
JONES
ADAMS
FORD



if the client derefs the cursor - we access the data - if not, we don't...



more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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