CODEWITHSUNDEEP
oracle-databasenested-table
tgrabus
tgrabus

Reputation: 113

How to select columns from nested table which points to object references

I have type:

CREATE OR REPLACE TYPE something AS OBJECT(
   name VARCHAR2(100),
   nestedObjects objects
);

where objects type is table of references:

CREATE TYPE objects IS TABLE OF REF object;

and object is:

CREATE OR REPLACE TYPE object AS OBJECT (
   number NUMBER
);

then I create table:

CREATE TABLE tab_something OF something
   NESTED TABLE nestedObjects STORE AS tab_nestedObjects;

and I want select numbers from objects contained in something table, I try:

SELECT ts.name, cursor(
   select deref(object).number 
   from TABLE(ts.nestedObjects) object
) FROM tab_something ts;

or:

SELECT DEREF(object).number 
FROM tab_something ts, TABLE(ts.nestedObjects) object;

but it doesn't work. Oracle say 'object' identifier does not valid.

When nestedObjects consist of objects then it work well because there is no need to deal with references. but when it points to object references it does not work. Ho can I deal with it?

THANKS FOR HELP!

Upvotes: 0

Views: 2226

Answers (1)

tgrabus
tgrabus

Reputation: 113

Ok I resolved:

SELECT DEREF(VALUE(object)).number 
FROM tab_something ts, TABLE(ts.nestedObjects) object;

Upvotes: 2

Related Questions