NESTED_TABLE_ID unavailable in PL/SQL?

From: Jacques-Henri <nobody_at_nowhere.com>
Date: Thu, 17 Jun 2004 19:27:41 +0200
Message-ID: <caski7$uaq$1@s1.read.news.oleane.net>

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

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. Received on Thu Jun 17 2004 - 12:27:41 CDT