Re: NESTED_TABLE_ID unavailable in PL/SQL?

From: Adrian Billington <billiauk_at_yahoo.co.uk>
Date: 18 Jun 2004 06:52:22 -0700
Message-ID: <dee17a9f.0406180552.2de509e4@posting.google.com>

It doesn't work in 10g either. Your workarounds are to use either a view or dynamic SQL, as follows:-

 9i> INSERT INTO t VALUES ( nt_table( nt_type(1) ) );

1 row created.

 9i>
 9i> CREATE VIEW tvw
  2 AS

  3     SELECT nested_table_id
  4     FROM   TABLE( SELECT nt FROM t );

View created.

 9i>
 9i> DECLARE
  2 x VARCHAR2(100);
  3 BEGIN
  4 SELECT nested_table_id INTO x FROM tvw;   5 END;
  6 /

PL/SQL procedure successfully completed.

 9i>
 9i> DECLARE
  2 x VARCHAR2(100);
  3 BEGIN

  4     EXECUTE IMMEDIATE
  5        'SELECT nested_table_id FROM TABLE (SELECT nt FROM t)'
  6     INTO x;

  7 END;
  8 /

PL/SQL procedure successfully completed.

 9i>
 9i> DECLARE

  2     TYPE aat_ids IS TABLE OF VARCHAR2(100)
  3        INDEX BY PLS_INTEGER;
  4     aa_ids aat_ids;
  5  BEGIN
  6     EXECUTE IMMEDIATE
  7        'SELECT nested_table_id FROM TABLE (SELECT nt FROM t)'
  8     BULK COLLECT INTO aa_ids;

  9 END;
 10 /

PL/SQL procedure successfully completed.

I prefer a view because it keeps all the dependencies intact and until 10g, the Native Dynamic SQL will soft parse on every execute. In 10g, it has been optimized to behave like static SQL and DBMS_SQL ( i.e. parse once, execute many ).

HTH
Adrian Received on Fri Jun 18 2004 - 08:52:22 CDT