OraFAQ Forum: SQL & PL/SQL » Oracle 10g

Home » SQL & PL/SQL » SQL & PL/SQL » Oracle 10g - Nested nested tables

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle 10g - Nested nested tables [message #168933]

Mon, 24 April 2006 04:36

timoomit
Messages: 5
Registered: April 2006

Junior Member

Hi!

I'm trying to create tables and either get ORA-22912 (column is not an nested table) or when leaving out the 'NESTED TABLE' declaration ORA-22913 (table name for nested table column has to be declared).
Below you can find one example for my object type definitions and a part of the table declaration:

CREATE TYPE userRight_t AS OBJECT(
label VARCHAR2(20)
);

CREATE TYPE userRightTab_t AS TABLE OF userRight_t;

CREATE TYPE userRightManagement_t AS OBJECT(
employee REF employee_t,
userRights userRightTab_t
);

CREATE TYPE process_t AS OBJECT(
processID VARCHAR2(15),
...
userRights userRightManagement_t,
...
);

The table declaration:

CREATE TABLE Process OF process_t(
processID NOT NULL,
SCOPE FOR (processDepartment) IS Department,
SCOPE FOR (processOwner) IS Department
)
NESTED TABLE userRights STORE AS userRights_store (NESTED TABLE userRights STORE AS userRights2_store),
...;

I don't know what is wrong about it.

Thanks

Timo

Report message to a moderator

Re: Oracle 10g - Nested nested tables [message #169043 is a reply to message #168933]

Mon, 24 April 2006 15:36

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

SCOTT@10gXE> CREATE TYPE userRight_t AS OBJECT
  2    (label VARCHAR2(20));
  3  /

Type created.

SCOTT@10gXE> CREATE TYPE userRightTab_t AS TABLE OF userRight_t
  2  /

Type created.

SCOTT@10gXE> CREATE TYPE userRightManagement_t AS OBJECT
  2    (--employee   REF employee_t,
  3  	userRights userRightTab_t);
  4  /

Type created.

SCOTT@10gXE> CREATE TYPE process_t AS OBJECT
  2    (processID  VARCHAR2(15),
  3  	userRights2 userRightManagement_t);
  4  /

Type created.

SCOTT@10gXE> CREATE TABLE Process OF process_t
  2    (processID NOT NULL --,
  3  	--SCOPE FOR (processDepartment) IS Department,
  4  	--SCOPE FOR (processOwner) IS Department
  5    )
  6    NESTED TABLE userRights2.userRights STORE AS userRights_store
  7  /

Table created.

SCOTT@10gXE>

Report message to a moderator

Re: Oracle 10g - Nested nested tables [message #169133 is a reply to message #168933]

Tue, 25 April 2006 04:32

timoomit
Messages: 5
Registered: April 2006

Junior Member

Hi!

Thank you very much for your reply! I adapted my statements and it works in some cases, but for most of them I get:

ORA-02337 not an object type column
Cause: An attempt was made to use dotted notation on a non-ADT column; that is, "a.b.c" where "a" is not an object type.
Action: Either change the column type to an object type or do not perform this operation.

Does anyone have any idea about this? It seems to me that the problem is that the attribute 'userRights2' in object type 'process_t' doesn't meet the requirements.

Timo

Report message to a moderator

Re: Oracle 10g - Nested nested tables [message #169158 is a reply to message #168933]

Tue, 25 April 2006 06:57

timoomit
Messages: 5
Registered: April 2006

Junior Member

UPDATE:

I found out how to do it. I just had to mix the '.' notation with the 'NESTED TABLE ...(NESTED TABLE ...)' notation in some places. For example:

NESTED TABLE output STORE AS output_store (NESTED TABLE data STORE AS outputdata_store, NESTED TABLE prerequisite STORE AS outputPrereq_store, NESTED TABLE pProcedure.prerequisites STORE AS procPrereq2_store)

Thank you again for your help!

Regards,

Timo

Report message to a moderator

Previous Topic:	Auto Incriment
Next Topic:	Sql qry in Desc-1 order

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 17 20:43:38 CST 2026