SQL & PL/SQL

Hey everyone,

I have a nested table defined in a package, and I'd like to select from it. This is what I have now to define my nested table as an SQL type:

create or replace
TYPE VALUES_RECORD IS OBJECT (
    "TIMESTAMP" DATE,
    "VALUE" VARCHAR(255 BYTE)
  );

create or replace
TYPE VALUES_TABLE IS TABLE OF VALUES_RECORD;

and inside my package, I have a simple query:

... some code to create a VALUES_TABLE table (temp_values_table) and fill it with data, and declare nested table of varchar 2...

  select value bulk collect into selected_values_varchar
    from TABLE(temp_values_table)
    where timestamp > to_date('01-01-2011', 'dd-mm-yyyy');

... some more code dealing with the values in selected_values_varchar table...

My problem with this is that I have to define my nested table type globally, which is unneeded since it will be used exclusively in this package.

Has anyone had the same issue, and is there a work around that allows me to query a nested table defined inside a package? The only other option I see is to make a bunch of nested for loops that would get me the same results as a simple query.

Thanks so much.

Edited by: Username on 02-Jun-2011 07:50, fixed formatting

Edited by: Username on 02-Jun-2011 07:52