| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: NESTED_TABLE_ID unavailable in PL/SQL?
How odd.
But what are you trying to achieve here - it's not easy to get any clues from this stripped down example.
Why not select the primary key from the owning table if you need a unique identifier.
Assume demo_nest is a table with a nested_table column called nest, then the following seems to be a viable approach - there doesn't seem to be any extra cost to doing things this way.
DECLARE
x VARCHAR2(100);
BEGIN
for r in (select d.id, n.v1 from demo_nest d, table(d.nest) n) loop
dbms_output.put_line(r.id || '-' || r.v1);
end loop;
end;
(Technically, if you wanted the nested_table_id, you could include sys_op_tosetid(nest) in the query as a column in the select list).
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st "Jacques-Henri" <nobody_at_nowhere.com> wrote in message news:caski7$uaq$1_at_s1.read.news.oleane.net...Received on Sun Jun 20 2004 - 12:21:57 CDT
> Any reason I can access the NESTED_TABLE_ID pseudocolumn in a simple SQL
> query, and not when using the same query in a PL/SQL bloc?
>
> Example:
>
> SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jun 17 19:18:27 2004
>
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
>
> Connected to:
> Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.1.0 - Production
>
> SQL> create type nt_type as object(nt_key number)
> 2 /
>
> Type created.
>
> SQL> create type nt_table as table of nt_type
> 2 /
>
> Type created.
>
> SQL> create table t (nt nt_table) nested table nt store as nt_store
> 2 /
>
> Table created.
>
> SQL> select nested_table_id from table (select nt from t)
> 2 /
>
> no rows selected
>
> SQL> begin
> 2 select nested_table_id from table (select nt from t);
> 3 end;
> 4 /
> select nested_table_id from table (select nt from t);
> *
> ERROR at line 2:
> ORA-06550: line 2, column 8:
> PL/SQL: ORA-00904: "NESTED_TABLE_ID": invalid identifier
> ORA-06550: line 2, column 1:
> PL/SQL: SQL Statement ignored
>
> SQL> ed
> Wrote file afiedt.buf
>
> 1 begin
> 2 declare
> 3 x varchar2(100);
> 4 begin
> 5 select nested_table_id into x from table (select nt from t);
> 6 end;
> 7* end;
> SQL> /
> select nested_table_id into x from table (select nt from t);
> *
> ERROR at line 5:
> ORA-06550: line 5, column 12:
> PL/SQL: ORA-00904: "NESTED_TABLE_ID": invalid identifier
> ORA-06550: line 5, column 5:
> PL/SQL: SQL Statement ignored
>
>
> SQL>
>
>
>
> Any workaround to this problem? TIA.
>
>
>
 |
 |