luoora
asked on
how cursor use global temporary table?
I am trying to create a global temporary table and cursor can use that. Since temporary
table not exist yet, got an compile error saying table my_temp_table not
exist. (see pseudo code) below. is there any way to get around or should I
take a different approach like using collection? thanks,
//
create or replace procedure test
is
cursor my_cur is
select *
from my_temp_table;
begin
execute immediate 'create global temporary table my_temp_table as .....";
open my_cur for loop ....
end;
//
thanks,
_______
table not exist yet, got an compile error saying table my_temp_table not
exist. (see pseudo code) below. is there any way to get around or should I
take a different approach like using collection? thanks,
//
create or replace procedure test
is
cursor my_cur is
select *
from my_temp_table;
begin
execute immediate 'create global temporary table my_temp_table as .....";
open my_cur for loop ....
end;
//
thanks,
_______
ASKER
can you please be a little bit more specific?
only this procedure need to use this temporary table. also other user (not me) will call this procedure so somehow they need to bundle together. thanks,
only this procedure need to use this temporary table. also other user (not me) will call this procedure so somehow they need to bundle together. thanks,
create the table in the same schema as the procedure owner.
the contents of a global temporary table are only visible to the session that populated it.
That's the whole point of them.
By any chance, are you trying to translate sql server code to oracle?
temporary tables in sql server are completely different than in oracle.
In oracle you create it once, and then simply reuse it.
In sql server you create and destroy them all the time
the contents of a global temporary table are only visible to the session that populated it.
That's the whole point of them.
By any chance, are you trying to translate sql server code to oracle?
temporary tables in sql server are completely different than in oracle.
In oracle you create it once, and then simply reuse it.
In sql server you create and destroy them all the time
ASKER
thanks for your reply.
I understands your comments on global temporary table are only visible to the session that populated it. create the GTT myself won't work since I don't which box user will run my procedure or I don't want to recreate it every time after db restart.
by the way, I am not translating sql server code to oracle. thanks,
I understands your comments on global temporary table are only visible to the session that populated it. create the GTT myself won't work since I don't which box user will run my procedure or I don't want to recreate it every time after db restart.
by the way, I am not translating sql server code to oracle. thanks,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
why a c? If you needed more help, you only needed to ask. Please don't assign penalties without giving the volunteers (not just me) a chance.
No need to have the procedure create the table for itself.
That's the whole point of the GTT's they can pre-exist and take no space or other resources until you actually need to use them.