Home » SQL & PL/SQL » SQL & PL/SQL » Multi Level Nested Tables  () 1 Vote
Multi Level Nested Tables [message #231617] Tue, 17 April 2007 13:35 Go to next message
compcoder
Messages: 30
Registered: April 2007
Location: Maryland USA
Member
Is there a limit on the level of nesting for nested tables? I'm able to place two objects in a table but can't get the third one to work. I'm using 10gR2. (This is just an example of what I'm trying to do.)

CREATE TYPE barcode_typ AS OBJECT (
barcode_number VARCHAR2(40)
);

CREATE TYPE nt_barcode_typ AS TABLE OF barcode_typ;

CREATE TYPE location_typ AS OBJECT (
location_id NUMBER(4),
street_address VARCHAR2(40),
postal_code VARCHAR2(12),
city VARCHAR2(30),
state_province VARCHAR2(25),
barcode nt_barcode_typ);
/
CREATE TYPE nt_location_typ AS TABLE OF location_typ;
/

CREATE TYPE country_typ AS OBJECT (
country_id CHAR(2),
country_name VARCHAR2(40),
locations nt_location_typ);
/

CREATE TYPE nt_country_typ AS TABLE OF country_typ;
/

CREATE TABLE region_tab (
region_id NUMBER,
region_name VARCHAR2(25),
countries nt_country_typ)
NESTED TABLE countries STORE AS nt_countries_tab
(NESTED TABLE locations STORE AS nt_location_tab)
(NESTED TABLE barcode STORE AS nt_barcode_tab;

I get the following:

ORA-00922: missing or invalid option




Re: Multi Level Nested Tables [message #231619 is a reply to message #231617] Tue, 17 April 2007 13:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where do you get that? Copy and paste your screen, with format.
Which Oracle version?

Regards
Michel

Re: Multi Level Nested Tables [message #231629 is a reply to message #231619] Tue, 17 April 2007 15:05 Go to previous messageGo to next message
compcoder
Messages: 30
Registered: April 2007
Location: Maryland USA
Member
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 17 16:04:50 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the OLAP and Data Mining options


1 CREATE TABLE region_tab (
2 region_id NUMBER,
3 region_name VARCHAR2(25),
4 countries nt_country_typ)
5 NESTED TABLE countries STORE AS nt_countries_tab
6 (NESTED TABLE locations STORE AS nt_location_tab)
7* (NESTED TABLE barcode STORE AS nt_barcode_tab);
SQL> /
(NESTED TABLE barcode STORE AS nt_barcode_tab);
*
ERROR at line 7:
ORA-00922: missing or invalid option

SQL>

[Updated on: Tue, 17 April 2007 15:11]

Report message to a moderator

Re: Multi Level Nested Tables [message #231647 is a reply to message #231629] Tue, 17 April 2007 21:37 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Your parentheses in your create table statement are slightly off. You need to nest your "nested table ... store as" clauses in the same manner that your tables are nested, as demonstrated below.

SCOTT@10gXE> CREATE TYPE barcode_typ AS OBJECT (
  2  barcode_number VARCHAR2(40)
  3  );
  4  /

Type created.

SCOTT@10gXE> CREATE TYPE nt_barcode_typ AS TABLE OF barcode_typ;
  2  /

Type created.

SCOTT@10gXE> CREATE TYPE location_typ AS OBJECT (
  2  location_id NUMBER(4),
  3  street_address VARCHAR2(40),
  4  postal_code VARCHAR2(12),
  5  city VARCHAR2(30),
  6  state_province VARCHAR2(25),
  7  barcode nt_barcode_typ);
  8  /

Type created.

SCOTT@10gXE> CREATE TYPE nt_location_typ AS TABLE OF location_typ;
  2  /

Type created.

SCOTT@10gXE> 
SCOTT@10gXE> CREATE TYPE country_typ AS OBJECT (
  2  country_id CHAR(2),
  3  country_name VARCHAR2(40),
  4  locations nt_location_typ);
  5  /

Type created.

SCOTT@10gXE> 
SCOTT@10gXE> CREATE TYPE nt_country_typ AS TABLE OF country_typ;
  2  /

Type created.

SCOTT@10gXE> 
SCOTT@10gXE> CREATE TABLE region_tab (
  2  region_id NUMBER,
  3  region_name VARCHAR2(25),
  4  countries nt_country_typ)
  5  NESTED TABLE countries STORE AS nt_countries_tab
  6    (NESTED TABLE locations STORE AS nt_location_tab
  7  	 (NESTED TABLE barcode STORE AS nt_barcode_tab))
  8  /

Table created.

SCOTT@10gXE> 


Re: Multi Level Nested Tables [message #231776 is a reply to message #231647] Wed, 18 April 2007 06:56 Go to previous messageGo to next message
compcoder
Messages: 30
Registered: April 2007
Location: Maryland USA
Member
Thanks for the information. I was able to be the example provided to work. I attempted to create a table to my application without success. Here is the screen output.

SQL> CREATE OR REPLACE TYPE RFID_ALERTS_OBJ AS OBJECT
2 ( HAZMAT VARCHAR2(100),
3 QA_NOTICE VARCHAR2(100),
4 NOTIFY_MA VARCHAR2(100),
5 NOTIFY_EM VARCHAR2(100),
6 PIPLINE_TIME VARCHAR2(100)
7 )
8 /

Type created.

SQL> CREATE OR REPLACE TYPE RFID_ALERT_LIST AS TABLE OF RFID_ALERTS_OBJ
2 /

Type created.

SQL> CREATE OR REPLACE TYPE RFID_ITEM_BARCODE_OBJ AS OBJECT
2 ( BARCODE_NUMBER VARCHAR2(32))
3 /

Type created.

SQL> CREATE OR REPLACE TYPE RFID_ITEM_BARCODES AS TABLE OF RFID_ITEM_BARCODE_OBJ
2 /

Type created.

SQL> CREATE OR REPLACE TYPE RFID_DUEIN_DATA_OBJ3 AS OBJECT
2 (
3 RECEIVED_IND VARCHAR2(1),
4 CALL_NUM VARCHAR2(20),
5 DOC_NUM VARCHAR2(50),
6 ITEM_ID VARCHAR2(50),
7 ITEM_DESC VARCHAR2(50),
8 ITEM_BARCODES RFID_ITEM_BARCODES,
9 CUSTOMER_ID VARCHAR2(10),
10 LOCATION VARCHAR2(25),
11 QUANTITY NUMBER(8,2),
12 PRICE NUMBER(8,2),
13 PDLI VARCHAR2(1)
14 )
15 /

Type created.

SQL> CREATE OR REPLACE TYPE D_OBENLA.RFID_NUM_OBJ3 AS OBJECT
2 (
3 RFID_NUM VARCHAR2(50),
4 RFID_DATA RFID_DATA_OBJ
5 )
6 /

Type created.

SQL> CREATE OR REPLACE TYPE RFID_DUEIN_SERIAL_OBJ3 AS OBJECT
2 (
3 DUEIN_SERIAL INTEGER,
4 RFID_NUM RFID_NUM_OBJ3,
5 DUEIN_DATA RFID_DUEIN_DATA_OBJ3,
6 DUEIN_ALERTS RFID_ALERT_LIST);
7 /

Type created.

SQL> CREATE OR REPLACE TYPE RFID_DUEINLIST3 AS TABLE OF RFID_DUEIN_SERIAL_OBJ3;
2 /

Type created.

SQL> CREATE TABLE RFID_ASN_EXTRACT_DATA3
2 (
3 ASN_EXTRACT_SERIAL INTEGER,
4 CREATE_TIME TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP,
5 ORDER_SERIAL INTEGER,
6 DUEIN_LIST RFID_DUEINLIST3)
7 NESTED TABLE DUEIN_LIST STORE AS DUEIN_LIST_STORE_TAB
8 (NESTED TABLE ITEM_BARCODES STORE AS RFID_ITEM_BARCODE_STORE_TAB
9 (NESTED TABLE DUEIN_ALERTS STORE AS RFID_ALERTS_STORE_TAB));
CREATE TABLE RFID_ASN_EXTRACT_DATA3
*
ERROR at line 1:
ORA-02320: failure in creating storage table for nested table column DUEIN_LIST
ORA-00904: : invalid identifier

[Updated on: Wed, 18 April 2007 09:52]

Report message to a moderator

Re: Multi Level Nested Tables [message #231862 is a reply to message #231776] Wed, 18 April 2007 12:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Your example is slightly incomplete. I added a missing type and removed a schema qualifier where indicated.

This example is nested differently than the previous one. In the previous one you had one table of obects nested within another table of objects nested within another table of objects nested within a table. But, in the new one, you have two items at the same level, a type containing a table of objects and a table of objects, both nested within a table of objects nested within a table. So, once again, it is about placement of parentheses, so that things are nested properly. I have used indentation to help show this.

Also, since one of the nested tables is contained within an object, not just another nested table, you have to use a qualifier, like duein_data.item_barcodes, instead of just item_barcodes.

Please see the demonstration below.

SCOTT@10gXE> CREATE OR REPLACE TYPE RFID_ALERTS_OBJ AS OBJECT
  2    ( HAZMAT VARCHAR2(100),
  3  	 QA_NOTICE VARCHAR2(100),
  4  	 NOTIFY_MA VARCHAR2(100),
  5  	 NOTIFY_EM VARCHAR2(100),
  6  	 PIPLINE_TIME VARCHAR2(100)
  7    );
  8  /

Type created.

SCOTT@10gXE> CREATE OR REPLACE TYPE RFID_ALERT_LIST AS TABLE OF RFID_ALERTS_OBJ;
  2  /

Type created.

SCOTT@10gXE> CREATE OR REPLACE TYPE RFID_ITEM_BARCODE_OBJ AS OBJECT
  2    ( BARCODE_NUMBER VARCHAR2(32));
  3  /

Type created.

SCOTT@10gXE> CREATE OR REPLACE TYPE RFID_ITEM_BARCODES AS TABLE OF RFID_ITEM_BARCODE_OBJ;
  2  /

Type created.

SCOTT@10gXE> CREATE OR REPLACE TYPE RFID_DUEIN_DATA_OBJ3 AS OBJECT
  2    (
  3    RECEIVED_IND VARCHAR2(1),
  4    CALL_NUM VARCHAR2(20),
  5    DOC_NUM VARCHAR2(50),
  6    ITEM_ID VARCHAR2(50),
  7    ITEM_DESC VARCHAR2(50),
  8    ITEM_BARCODES RFID_ITEM_BARCODES,
  9    CUSTOMER_ID VARCHAR2(10),
 10    LOCATION VARCHAR2(25),
 11    QUANTITY NUMBER(8,2),
 12    PRICE NUMBER(8,2),
 13    PDLI VARCHAR2(1)
 14    );
 15  /

Type created.

SCOTT@10gXE> -- added missing type:
SCOTT@10gXE> CREATE OR REPLACE TYPE rfid_data_obj AS OBJECT
  2    (some_column  NUMBER);
  3  /

Type created.

SCOTT@10gXE> -- removed shcema qualifier:
SCOTT@10gXE> CREATE OR REPLACE TYPE RFID_NUM_OBJ3 AS OBJECT
  2    (
  3    RFID_NUM VARCHAR2(50),
  4    RFID_DATA RFID_DATA_OBJ
  5    );
  6  /

Type created.

SCOTT@10gXE> CREATE OR REPLACE TYPE RFID_DUEIN_SERIAL_OBJ3 AS OBJECT
  2    (
  3    DUEIN_SERIAL INTEGER
  4    , RFID_NUM RFID_NUM_OBJ3
  5    , DUEIN_DATA RFID_DUEIN_DATA_OBJ3
  6    , DUEIN_ALERTS RFID_ALERT_LIST
  7  );
  8  /

Type created.

SCOTT@10gXE> CREATE OR REPLACE TYPE RFID_DUEINLIST3 AS TABLE OF RFID_DUEIN_SERIAL_OBJ3;
  2  /

Type created.

SCOTT@10gXE> cREATE TABLE RFID_ASN_EXTRACT_DATA3
  2    ( ASN_EXTRACT_SERIAL  INTEGER
  3    , CREATE_TIME	     TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP
  4    , ORDER_SERIAL	     INTEGER
  5    , DUEIN_LIST	     RFID_DUEINLIST3
  6    )
  7    NESTED TABLE DUEIN_LIST STORE AS DUEIN_LIST_STORE_TAB
  8  	 (NESTED TABLE duein_data.ITEM_BARCODES STORE AS RFID_ITEM_BARCODE_STORE_TAB
  9  	  NESTED TABLE DUEIN_ALERTS STORE AS RFID_ALERTS_STORE_TAB)
 10  /

Table created.

SCOTT@10gXE> 
Re: Multi Level Nested Tables [message #231869 is a reply to message #231862] Wed, 18 April 2007 12:28 Go to previous messageGo to next message
compcoder
Messages: 30
Registered: April 2007
Location: Maryland USA
Member
Sorry about the missing object. I noticed it after I emailed you the code. I updated the posting.

The missing object contained another object in it along with a varchar2 value. Just by adding the object to that object I get the same errors before. Is there a book or someplace on the web that explains this?

Here is the complete code:

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 19 08:15:04 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the OLAP and Data Mining options

SQL> set echo on
SQL> @c:\fixme.sql
SQL> CREATE OR REPLACE TYPE RFID_ALERTS_OBJ AS OBJECT
2 ( HAZMAT VARCHAR2(100),
3 QA_NOTICE VARCHAR2(100),
4 NOTIFY_MA VARCHAR2(100),
5 NOTIFY_EM VARCHAR2(100),
6 PIPLINE_TIME VARCHAR2(100))
7 /

Type created.

SQL> CREATE OR REPLACE TYPE RFID_ALERT_LIST AS TABLE OF RFID_ALERTS_OBJ
2 /

Type created.

SQL> CREATE OR REPLACE TYPE RFID_ITEM_BARCODE_OBJ AS OBJECT
2 ( BARCODE_NUMBER VARCHAR2(32))
3 /

Type created.

SQL> CREATE OR REPLACE TYPE RFID_ITEM_BARCODES AS TABLE OF RFID_ITEM_BARCODE_OBJ
2 /

Type created.

SQL> CREATE OR REPLACE TYPE RFID_DUEIN_DATA_OBJ AS OBJECT
2 (
3 RECEIVED_IND VARCHAR2(1),
4 CALL_NUM VARCHAR2(20),
5 DOC_NUM VARCHAR2(50),
6 ITEM_ID VARCHAR2(50),
7 ITEM_DESC VARCHAR2(50),
8 ITEM_BARCODES RFID_ITEM_BARCODES,
9 CUSTOMER_ID VARCHAR2(10),
10 LOCATION VARCHAR2(25),
11 QUANTITY NUMBER(8,2),
12 PRICE NUMBER(8,2),
13 PDLI VARCHAR2(1)
14 )
15 /

Type created.

SQL>
SQL> CREATE OR REPLACE TYPE RFID_NUM_OBJ AS OBJECT
2 (
3 RFID_NUM VARCHAR2(50),
4 RFID_DATA RFID_DUEIN_DATA_OBJ
5 )
6 /

Type created.

SQL>
SQL> CREATE OR REPLACE TYPE RFID_DUEIN_SERIAL_OBJ AS OBJECT
2 (
3 DUEIN_SERIAL INTEGER
4 , RFID_NUM RFID_NUM_OBJ
5 , DUEIN_DATA RFID_DUEIN_DATA_OBJ
6 , DUEIN_ALERTS RFID_ALERT_LIST
7 )
8 /

Type created.

SQL> CREATE OR REPLACE TYPE RFID_DUEINLIST AS TABLE OF RFID_DUEIN_SERIAL_OBJ
2 /

Type created.

SQL> cREATE TABLE RFID_ASN_EXTRACT_DATA
2 ( ASN_EXTRACT_SERIAL INTEGER
3 , CREATE_TIME TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP
4 , ORDER_SERIAL INTEGER
5 , DUEIN_LIST RFID_DUEINLIST
6 )
7 NESTED TABLE DUEIN_LIST STORE AS DUEIN_LIST_STORE_TAB
8 (NESTED TABLE duein_data.ITEM_BARCODES STORE AS RFID_ITEM_BARCODE_STORE_TAB
9 NESTED TABLE DUEIN_ALERTS STORE AS RFID_ALERTS_STORE_TAB)
10 /
cREATE TABLE RFID_ASN_EXTRACT_DATA
*
ERROR at line 1:
ORA-02320: failure in creating storage table for nested table column DUEIN_LIST
ORA-22913: must specify table name for nested table column or attribute
  • Attachment: fixme.sql
    (Size: 1.71KB, Downloaded 262 times)

[Updated on: Thu, 19 April 2007 07:30]

Report message to a moderator

Re: Multi Level Nested Tables [message #232108 is a reply to message #231869] Thu, 19 April 2007 08:17 Go to previous messageGo to next message
compcoder
Messages: 30
Registered: April 2007
Location: Maryland USA
Member
I found the problem !!!!!!!! I was adding another nested table where I should not have. Thanks for all of your help. I have attached the final script for those of you that are interested.
  • Attachment: fixme.sql
    (Size: 1.93KB, Downloaded 416 times)
Re: Multi Level Nested Tables [message #232471 is a reply to message #232108] Fri, 20 April 2007 12:08 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
I hope you have good reasons for using nested tables - they can make your life very difficult. Do a google on the topic to get perspectives. Here's one... http://www.dbdebunk.com/page/page/2119298.htm
Re: Multi Level Nested Tables [message #232478 is a reply to message #232471] Fri, 20 April 2007 12:22 Go to previous messageGo to next message
compcoder
Messages: 30
Registered: April 2007
Location: Maryland USA
Member
I'm using the structure of the table to build an XML document from it. After loading the table, just send a basic "select" statement to the XMLGEN function out comes an XML stream.
Nested table updation Example [message #273391 is a reply to message #231617] Wed, 10 October 2007 05:02 Go to previous messageGo to next message
arunap
Messages: 3
Registered: July 2007
Location: hyd
Junior Member
Hi all,

i want to update the swap_sal with conditions
swap_no and po_no

create type swap_det as object(
	swap_no          varchar2(30),
	swap_qty		 number,
	swap_conf_date		 date,
	oub_pick_date		 date,
	oub_delivery_date	 date,	
	swap_days		 number
 );

create  type swap_det_tab as table of swap_det;

drop table rpt_swap_tat;

create table rpt_swap_tat(
comp_id		 	 varchar2(10),
site_id			 varchar2(12),
ebiz_appown_no		 number,
ebiz_user_no		 number,
currdate		 date,
po_no  		 	 varchar2(30),
ebiz_po_no		 number,
upload_date		 date,
inb_pick_date		 date,
po_conf_date		 date,
upload_qty		 number,
rcv_qty			 number,
swap_det          	 swap_det_tab,  
user_field1		 varchar2(50),
user_field2		 varchar2(50),
user_field3		 varchar2(50),
user_field4		 varchar2(50),
user_field5		 varchar2(50)
) nested table swap_det store as swap_data;


please anyone help me

thanks
Aruna

[Updated on: Wed, 10 October 2007 05:04] by Moderator

Report message to a moderator

Re: Nested table updation Example [message #273411 is a reply to message #273391] Wed, 10 October 2007 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nested objects are very difficult to maintain (update,insert,delete) as they are not relational.
So you have to workaround this with complex SQL or even hidden features.

Don't use them unless you have a very good reason (and I don't see none).
Why not a genuine detail table?

Regards
Michel
Re: Nested table updation Example [message #273800 is a reply to message #273391] Thu, 11 October 2007 13:35 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You say you want to update swap_sal, but there is no swap_sal in the code that you provided. Please clarify. Also, please provide insert statements for sample data an an example of the results that you want after the update.
Re: Nested table updation Example [message #273892 is a reply to message #273800] Fri, 12 October 2007 02:18 Go to previous messageGo to next message
arunap
Messages: 3
Registered: July 2007
Location: hyd
Junior Member
create type swap_det as object(
swap_no varchar2(30),
swap_qty number,
swap_conf_date date,
oub_pick_date date,
oub_delivery_date date,
swap_days number
);

create type swap_det_tab as table of swap_det;

drop table rpt_swap_tat;

create table rpt_swap_tat(
comp_id varchar2(10),
site_id varchar2(12),
ebiz_appown_no number,
ebiz_user_no number,
currdate date,
po_no varchar2(30),
ebiz_po_no number,
upload_date date,
inb_pick_date date,
po_conf_date date,
upload_qty number,
rcv_qty number,
swap_det swap_det_tab,
user_field1 varchar2(50),
user_field2 varchar2(50),
user_field3 varchar2(50),
user_field4 varchar2(50),
user_field5 varchar2(50)
) nested table swap_det store as swap_data;

i want to update swpa_qty(swap_sal is diff. table column) based on po_no and swap_no

insert into rpt_swap_tat (comp_id,site_id,ebiz_appown_no,po_no)
values('NOKIA-MR','DELHI',96,'MR2');

update rpt_swap_tat
set swap_det = (swap_det_tab(swap_det('SWAP2',null,null,null,null,null)))
where po_no='MR2'

now i want to update the swap_qty for the record 'MR2' and 'SWAP2'.

please help me

Thanks
Aruna
Re: Nested table updation Example [message #273895 is a reply to message #273892] Fri, 12 October 2007 02:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

If you don't know how to use these kind of objects why do you want to use them as they are much more difficult to use and lead to low performances?

Regards
Michel

[Updated on: Fri, 12 October 2007 02:53]

Report message to a moderator

Re: Multi Level Nested Tables [message #273967 is a reply to message #231617] Fri, 12 October 2007 07:24 Go to previous message
compcoder
Messages: 30
Registered: April 2007
Location: Maryland USA
Member
UPDATE TABLE(
  SELECT h.swap_det
  FROM rpt_swap_tat h
  WHERE h.po_no = 'MR2') p
SET p.swap_qty = 1
WHERE p.swap_no = 'SWAP2';


Check out this site

http://www.psoug.org/reference/collections.html
Previous Topic: tricky grouping
Next Topic: Trigger 'SCOTT.SALARY_ADJUST' is invalid and failed re-validation
Goto Forum:
  


Current Time: Fri Dec 09 02:21:08 CST 2016

Total time taken to generate the page: 0.20228 seconds