?
Solved

Oracle problem creating multi-layer nested table

Posted on 2005-03-09
5
Medium Priority
?
681 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

752 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