Solved

how cursor use global temporary table?

Posted on 2008-10-08
6
3,488 Views
Last Modified: 2013-12-07
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,
_______
0
Comment
Question by:luoora
  • 4
  • 2
6 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
create the global temporary table first (now) and then write your procedure.

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.
0
 

Author Comment

by:luoora
Comment Utility
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,
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:luoora
Comment Utility
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,
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
Comment Utility
you have to know which box your procedure will run on.  It's a procedure, so it runs inside the database.  create the table in that same database.  You won't have to recreate it after db restart.
You create it once and it's done.  

If you won't be doing the install into the database yourself.  Then simply put the create table and the create procedure statements into the same install script.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now