how cursor use global temporary table?

Posted on 2008-10-08
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

cursor my_cur is
select *
from my_temp_table;


execute immediate 'create global temporary table my_temp_table as .....";

open my_cur for loop ....


Question by:luoora
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
LVL 74

Expert Comment

ID: 22670549
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.

Author Comment

ID: 22670619
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,
LVL 74

Expert Comment

ID: 22670806
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
Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part


Author Comment

ID: 22670980
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,
LVL 74

Accepted Solution

sdstuber earned 250 total points
ID: 22671007
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.
LVL 74

Expert Comment

ID: 22724319
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.

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

632 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