Re: PL/SQL: select into local table variable

You can't bulk collect into a table of records. Try using a two tables instead.

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

"Ralf Jonas" <ralf.jonas_at_gmx.de> wrote in message news:9stmif$14qann$1_at_ID-6634.news.dfncis.de...
> 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 - 07:35:59 CST