Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


how cursor use global temporary table?

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

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


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 500 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

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…
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 set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

670 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