How to use Nested Table in a Sql statement of a Pl/Sql code.

I need a solution related to, Using Nested Table (or Pl/Sql Table) in Sql statement within the same Pl/SQl code. I want to use a Nested Table (defined in Pl/Sql code) in a SQL statement in the same Pl/Sql code.

I had defined a Nested Table Type at Package Level.

TYPE pl_geotabtype IS TABLE OF number(8); pl_geotab pl_geotabtype;

Then I had declared Pl/Sql variable of this Table Type in one of the Stored Procedure of the Package.

pl_geotab pl_geotabtype:=pl_geotabtype();

Then I had populated this Nested Table, from DataBase Tables. Now I want to use this Nested Table (pl_geotab) in one Dynamic Sql statement of my Procedure.This Dynamic Sql is stored in Pl/Sql varchar2 variable(pl_geoquery). The resultset of this Dynamic Sql is to be passed through Ref Cursor(detail) to Java Client.

For Ex:

/* Variable pl_geoquery holds the Query in which I want to use the
Nested Table*/
pl_geoquery:=' select * from geopolitical_object where object_id in (select * from TABLE (pl_geotab as pl_geotabtype))';

/* detail is the Ref Cursor */

open detail for pl_geoquery;

But Statement: (select * from TABLE (pl_geotab as pl_geotabtype) does'nt works.

Actually I want to use my Nested Table(pl_geotab ) in "IN" clause of Sql statement(pl_geoquery).

So can somebody suggest me a solution for this.

Thanks&Regards
Anupam. Received on Mon Sep 10 2001 - 12:30:36 CEST