?
Solved

Oracle problem creating multi-layer nested table

Posted on 2005-03-09
5
Medium Priority
?
712 Views
Last Modified: 2013-12-11
CREATE TYPE InvestigatorType AS OBJECT
(
  INV_DESC VARCHAR2(20)
);
/

CREATE TYPE InvListType AS TABLE OF InvestigatorType;
/

CREATE TYPE SiteType AS OBJECT (
   SITENO VARCHAR2(20),
   COUNTRY VARCHAR2(20),
   InvList InvListType
);
/

CREATE TYPE SiteListType AS TABLE OF SiteType;
/

create table Trial
(
 name varchar2(400),
 SiteList SiteListType
)
NESTED TABLE SiteList STORE AS SiteList_ntbl;

I got the following errors:
ORA-02320: failure in creating storage table for nested table column SITELIST
ORA-22913: must specify table name for nested table column or attribute

I guess i haven't allocated space for InvList in SiteType.  But what's the syntax to do that?  

Thanks

Jack
0
Comment
Question by:jackheex
  • 2
4 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 13505465
an example:

create table ccp_studio_template (
studio_template_id number not null,
description varchar2(30) not null,
template_type varchar2(4) not null,
template_code varchar2(10),
version_number varchar2(5),
template_options_obj type3_objtyp)
nested table template_options_obj.image_table store as x1 (storage (initial 256K))
nested table template_options_obj.text_table store as x2
storage (initial 1M next 1M pctincrease 0)
/
0
 

Author Comment

by:jackheex
ID: 13506470
Thanks that's good to know.  But that didn't seem to solve my problem.

I believe in my case, the column Trial.SiteList is a nested table.  Therefore the following clause is required in the CREATE TABLE TRIAL statement:

    NESTED TABLE SiteList STORE AS SiteList_ntbl;

But that is not enough (getting ORA-02320 and ORA-22913).  I think that's because in SiteType, there is also a nested table InvList.  I think Oracle is complaining about not finding a similar NESTED TABLE clause for InvList.

I increased the point to 200.

Thank you!

Jack
0
 

Author Comment

by:jackheex
ID: 13506717
I figured it out.  Using VARRAY, instead of nested table, for collections, will avoid the storage space problem.  Here is the modified code, how to insert values into to final table, and how to export the table into a XML file.

CREATE TYPE InvestigatorType AS OBJECT
(
  INV_DESC VARCHAR2(20)
);
/
create type InvArrType As varray(1000) of InvestigatorType;
/
CREATE or replace TYPE SiteType AS OBJECT (
   SITENO VARCHAR2(20),
   COUNTRY VARCHAR2(20),
   InvArr InvArrType
);
/
CREATE TYPE SiteArrType AS varray(1000) OF SiteType;
/
create table Trial
(
 name varchar2(400),
 SiteArr SiteArrType
);
/
insert into Trial values ('alan-test-01',
         SiteArrType(SiteType('site1', 'usa', InvArrType(InvestigatorType('Jack'),InvestigatorType('John'))),
               SiteType('site2', 'usa', InvArrType(InvestigatorType('James'),InvestigatorType('Mack')))
));

-- output Trial table to XML
 
DECLARE
      QRY VARCHAR2(4000);
      XMLSTRING CLOB;
      FP UTL_FILE.FILE_TYPE;
BEGIN
      QRY             := 'SELECT * FROM trial';
      XMLSTRING       := DBMS_XMLQUERY.GETXML(QRY);
      FP             := UTL_FILE.FOPEN('C:\TEMP','TEST.TXT','W');
      UTL_FILE.PUT      (FP, XMLSTRING);
      UTL_FILE.FCLOSE(FP);
END;

Thanks

Jack
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 13738920
PAQed with points refunded (200)

modulo
Community Support Moderator
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question