0

I have a sample query like below:

INSERT INTO my_gtt_1 (fname, lname) (select fname, lname from users)

In my effort to getting rid of temporary tables I created a package:

create or replace package fname_lname AS

Type fname_lname_rec_type is record (
fname varchar(10),
lname varchar(10)
);

fname_lname_rec fname_lname_rec_type

Type fname_lname_tbl_type is table of fname_lname_rec_type;

function fname_lname_func
(
   v_fnam in varchar2, 
   v_lname in varchar2
)return fname_lname_tbl_type pipelined;

being new to oracle...creating this package took a long time. but now I can not figure out how to get rid of the my_gtt_1

how can i say...

INSERT INTO <newly created package> (select fnma, name from users)
APC
  • 144,005
  • 19
  • 170
  • 281
john
  • 493
  • 1
  • 8
  • 12
  • How are you using the temporary table at the moment, and why do you want to get rid of it? In this very simple case you might be better off with a view, but that might not always be appropriate. But I'm not sure this package will do what you want - as APC notes, you can only pass in one set of values at the point you use it. You also don't currently do anything with the values that are passed. It sounds like you want the function to perform the query on the user table and return a PL/SQL table holding the results, rather than have a SQL temporary table. – Alex Poole Jun 14 '10 at 16:34
  • You should also look at this, which explains it all far better than I ever could: http://stackoverflow.com/questions/2918466/ – Alex Poole Jun 14 '10 at 16:43

2 Answers2

0

You need to call the pipelined function using the TABLE() syntax:

select * 
from table (select fname_lname.fname_lname_func(fnma, name) 
            from users
            where user_id = 123 )
/

Note that the sub-query on USERS must return a single row from that table.

APC
  • 144,005
  • 19
  • 170
  • 281
0

You don't select into packages. You could declare a variable of your table type and bulk collect into that, if you intend to use it in code. I also question your need for a pipelined function. If you're just using the global-temporary table as a springboard for another query, you could probably just use a WITH clause instead. We need a better idea of the bigger picture to recommend a particular technique. Global temporary tables are not inherently bad, either.

kurosch
  • 2,292
  • 16
  • 17