PL/SQL: select into local table variable

Hi,

I have the problem to select more than one column into a local table.

This works:

create or replace procedure test as
  type tIDTabelle is table of ptvpln.id%type index by binary_integer;   LocalTable tIDTabelle;
begin
  LocalTable.Delete;
  select id bulk collect into LocalTable from ptvpln where...; end test;

This works not:

create or replace procedure test as
  type rStructure is record
  (
    OldID ptvpln.id%type;
    NewID ptvpln.id%type;
);

  type tIDTabelle is table of rStructure index by binary_integer;   LocalTable tIDTabelle;
begin
  LocalTable.Delete;
  select id, old_id bulk collect into LocalTable from ptvpln where...; end test;

Instead I have to fill the table like this:

create or replace procedure test as
  type rStructure is record
  (
    OldID ptvpln.id%type;
    NewID ptvpln.id%type;
);

  type tIDTabelle is table of rStructure index by binary_integer;   LocalTable tIDTabelle;
  Index number(10) := 1;
begin
  LocalTable.Delete;
  for xRecord in (select id, old_id bulk from ptvpln where...) loop     LocalTable(Index).NewID := xRecord.id;     LocalTable(Index).OldID := xRecord.old_id;     Index := Index + 1;
  end loop;
end test;

Furthermore: Is there any chance to use local tables in conjunction with real tables, i.e. in a join?

...
  select id from LocalTable, ptvpln where ptvpln.old_id = LocalTable.id; ???

Yes, I'm aware of the existence of global temporary tables, but they are far too slow for this special case.

Many thanks in advance
Ralf Received on Wed Nov 14 2001 - 06:02:36 CST