how to use oracle package to get rid of Global Temp table

Question

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)

Answer 1

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.

Answer 2

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.