Global Temporary Taable

I am using a global temporary table with "on commit preserve rows" in
order to pass a ref cursor back to the calling program.  The data is
returning fine.  However, I want to drop the table after the cursor is
returned.  If I insert a drop table statment inside of the procedure I
am getting the following error:

ORA-14452: attempt to create, alter or drop an index on temporary
table already in use.  If I close the session and go through TOAD I
can see the table but still can't drop it.  I get the same error.

I've researched via the internet and understand in a perfect
environment a "real" table is the way to go.  However, receiving
strong pushback from the customer.

Thanks For your help
0
neil2391 (1)
6/25/2004 5:47:08 PM
comp.databases.oracle.server 21915 articles. 0 followers. Post Follow

4 Replies
214 Views

Similar Articles

[PageSpeed] 46
On 25 Jun 2004 10:47:08 -0700, neil@blueslate.net (Neil) wrote:

>I've researched via the internet and understand in a perfect
>environment a "real" table is the way to go

In a 'perfect' environment you don't need permanent temporary tables.
Fix your database design and/or your queries.


--
Sybrand Bakker, Senior Oracle DBA
0
gooiditweg2 (295)
6/25/2004 6:30:17 PM
Neil wrote:

> However, I want to drop the table after the cursor is
> returned.

Please explain WHY you want to DROP the table.  

If I understand Oracle's intent properly, GTTs are intended to have a
permanent definition, supplied by the DBA ideally ONCE in the life of the
database).  The term TEMP is relative to the data, not the definition.

Without understanding your requirements, to me it appears that your design
is fundementally in conflict with the intent and implementation of temp
tables.

/Hans
0
forbrich (430)
6/25/2004 6:46:51 PM
Neil wrote:
> 
> I am using a global temporary table with "on commit preserve rows" in
> order to pass a ref cursor back to the calling program.  The data is
> returning fine.  However, I want to drop the table after the cursor is
> returned.  If I insert a drop table statment inside of the procedure I
> am getting the following error:
> 
> ORA-14452: attempt to create, alter or drop an index on temporary
> table already in use.  If I close the session and go through TOAD I
> can see the table but still can't drop it.  I get the same error.
> 
> I've researched via the internet and understand in a perfect
> environment a "real" table is the way to go.  However, receiving
> strong pushback from the customer.
> 
> Thanks For your help

The term "global temporary table" is used because 

a) the table is permanently defined (hence the term "temporary")
b) the data is local to your session (hence the term "local")

:-)

Seriously though, a couple of things 

i) you don't need a temp table to pass a ref cursor around.  ref cursor
point to a resultset which does not need to "exist" as a temporary table

ii) if you are heading down the gtt route - you just populate it, use
the data, and then forget about the data.  It vanishes as soon as you
exit the session.  

hth
connor

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald@yahoo.com

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------
0
6/26/2004 2:30:56 AM
On 25 Jun 2004, neil@blueslate.net wrote:
> I am using a global temporary table with "on commit preserve
> rows" in order to pass a ref cursor back to the calling
> program.  The data is returning fine.  However, I want to drop
> the table after the cursor is returned.  If I insert a drop
> table statment inside of the procedure I am getting the
> following error:
> 
> ORA-14452: attempt to create, alter or drop an index on
> temporary table already in use.  If I close the session and go
> through TOAD I can see the table but still can't drop it.  I
> get the same error.
> 
> I've researched via the internet and understand in a perfect
> environment a "real" table is the way to go.  However,
> receiving strong pushback from the customer.

Just stop dropping the table.  Leave it there.  GTTs are used for
session-based data but they aren't to be constantly dropped.
They are tables, but their data is temporary.

If you have a connection pool, then you will need to clear it out
every call.

-- 
Galen Boyer
0
galenboyer (300)
6/26/2004 4:17:04 AM
Reply:
Similar Artilces:

global, globals(), _global ?
Using global variables in Python often raises chaos. Other languages use a clear prefix for globals. * you forget to declare a global * or you declare a global too much or in conflict * you have a local identical variable name and want to save/load it to/from the global with same name * while you add code, the definition of globals moves more and more apart from their use cases -> weirdness; programmers thinking is fragmented * using globals()['xy'] is 'stringy non-program-code' Thus, since long time at the head of my bigger modules I often put... _global = sys.modul...

Global variables considered harmful?
--nextPart52506328.PINYBkrvoZ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hello all, I have a Ruby script here that acts like a regular Unix program, including= =20 accepting a large number of command-line options/arguments. After processin= g=20 some are simple flags (ie: nil or 1) and some are text/numeric arguments.= =20 Not all are required, and some must be initialized with a default value. I= =20 have currently implemented them as globals, as their values are needed=20 anywhere in my script, from clas...

Bill Gates says mobiles can improve global health
http://bit.ly/b7PeKy <quote> "The mobile phone is pretty interesting for lots of things," Gates said in his keynote address at the mobile health event. "There's a whole lot of opportunities. I think we have to approach these things with some humility though," added Gates, noting that much of the developing world still has no internet or data connections. "I do think there's absolutely a role (for mobile technology to impact global health) but I think we have to hold ourselves to some pretty tough metrics to see if it's really maki...

JNI and Global references
Hi all Does anybody know when using JNI how can you get the limit number of global references in a process. Is there a way to control it. Thanks On 22 Jun 2005 07:37:52 -0700, yshamama@magicsoftware.com wrote: > Does anybody know when using JNI how can you get the limit number of > global references in a process. > > Is there a way to control it. I can think of no way to control it. Correct me if I'm wrong, but your question seems to indicate that you use an extremely large number of global references. (Why else would you feel that you need to limit their number?) If t...

Why $GLOBALS ?
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hello everyone, well I use PHP for a long time and currently I have one question: Why is there this $GLOBALS varianble? http://www.php.net/manual/en/reserved.variables.globals.php It is always there, but why ? Is it a good thing to use this or not ? Any comments about this will be nice. regards, j. ke�ler -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.11 (GNU/Linux) iEYEARECAAYFAkpwOMcACgkQE++2Zdc7EtfBpgCdEAxWAyRxZ6rZ7onWlVAAe82W QOwAn1rtBcFYBDTzhOi/T6Nzh+eqdexB =9n5J -----END PGP SIGNATURE----- j.ke�ler escribi�: > well I use PHP fo...

global array allocation
Hi All, As we know the stack size for a process in fixed to 4KB/8KB in linux kernel. This means we should not declare automatic variable more than this size as it goes in process stack. I want to know where is the global variable/array allocated for a process in kernel? and how much size can be allocated? Thanks kushneeraj kushneeraj <kushneeraj@gmail.com> writes: > As we know the stack size for a process in fixed to 4KB/8KB in linux > kernel. Each process (or thread) on Linux has a kernel stack of either 8 or 4K associated with it, which is used while the pr...

Temporary call Atmel bootloader
Hi, I'm using a AT89C51ED2. Sometimes the application needs to start the Atmel ISP bootloader, e.g. to enable re-programming the flash. Normally this is done by the following API procedure: void __api_start_bootloader (void) { __api_wr_BSB (0xFF); __api_wr_SBV (0xFC); // stop application peripherals T2CON=0x00; TL2=0x00; TH2=0x00; BDRCON=0x00; TCON=0x00; SCON = 0x00; TMOD = 0x00; PCON = 0x00; TCON = 0x00; EA = 0; MAP_BOOT; __API_JMP_BOOTLOADER(); } The pr...

Lack of temporaries
I thought I understand fundamental rules of C++ until I wrote this simple p= rogram: #include <iostream> using namespace std; #define exampleOne class MyType { public: MyType() { cout << "def ctor" << endl; } #ifdef exampleOne MyType(const MyType& x) { cout << "copy ctor"; } #endif #ifdef exampleTwo MyType(MyType&& x) { cout << "move ctor" << endl; } #endif }; MyType fun() { MyType mt; return mt; } int main() { MyType m =3D fun(); } which includes tw...

problem initializing a global variable
I want to have function addresses in a global varible and I also want to refer to it in the function. how do I do that? e.g., this typedef int fun_t (void); fun_t *foo = &bar; int bar (void) { printf("%ul\n",foo); return 1; } will not work because bar() is not defined yet, and this: typedef int fun_t (void); int bar (void) { printf("%ul\n",foo); return 1; } fun_t *foo = &bar; will not work because foo is not defined yet. so, is this typedef int fun_t (void); fun_t *foo; int bar (void) { printf("%ul\n",foo); ...

global or $GLOBALS
Hi! Just two short questions..: 1) In a function I want to get the value of a global variable declared outside the function. Should I use "global $variable" or $GLOBALS[variable]? Are there any differences in performance or other aspects which should make me choose the one thing or the other? 2) Generally, is it better to pass arguments to the function instead? Ole J ojorus wrote: > Hi! > Just two short questions..: Hi, > > 1) In a function I want to get the value of a global variable declared > outside the function. Should I use "global $variable&qu...

"proper" way to handle "global" data
If I have some piece of data that is used at arbitary times by anarbitary method in the call stack and what ever it's value is is thevalue globally (may change during the life of the executable)... forexample if I have a testing framework that sets some defaults (likewhat type of test to run) at start up (the value comes from thecommand line or has a hard coded value if not given on the commandline)... what is the "right" way of doing this... I also need to setthis to behavioral values like (make a dry run over a tree of testswithout actually running the test [used to count the s...

Where can I declare Global Variables
Noob problem. I prefer to keep all my scripts in an external '.js' file. I am currently loading the external '.js' file from the header. Problem is I would like to declare a global variable in the external file, but I keep getting an error about the object does not exist. Can someone tell me where or how to declare a global variable in an external file that is available after the page is loaded. Thanks in advance. rick wrote: > Noob problem. I prefer to keep all my scripts in an external '.js' file. > I am currently loading the external '.js' file fr...

If global optimum is needed, what is the best strategy?
Hi all, I know searching for global optimum is a hard problem, if not a mission impossible. Now for my non-linear least square optimization (constrained) problem, if I really want to obtain the global optimum, what should be the best strategy and searching procedure? I am looking for a step-by-step strategy, combining the use of multiple solvers. For example, my plan is to use a stochastic solver, such as the Simulated Annealing method to first do a coarse level optimization, and then use the parameter values found at the coarse level search as initial guess values for a finer gradi...

New Global Whitelist vs. Old Content Control Whitelist
Hello! PM 4.41 on Win XP Pro. What a fantastic release! Spamhalter, mail merge, default folder sizes--*all* are so useful. Many thanks to David Harris and his team! I have an old whitelist (distribution list) which is identified in a content control definition ("Basic Spam Detection", based on the spambust.dat file). I have been adding addresses to this whitelist for a long time. Does it make sense to take the addresses from the content control whitelist and put them in the global whitelist instead? (The docs indicate that if an address is in the global whitelist, it ...

Iran slams Wiki-release as US psywar
Iran slams Wiki-release as US psywar - WIKILEAKS is replacing those BIN LADEN communiques of CIA (the global ELITE) intended to threaten MASSES CIA is the criminal agency of the global elite. They want to destroy the middle class from the planet and also create a global tyranny of a police state. http://presstv.ir/detail/153128.html http://presstv.ir/detail/153128.html http://presstv.ir/detail/153128.html Iran slams Wiki-release as US psywar Mon Nov 29, 2010 12:56PM Share | Email | Print Iran's President Mahmoud Ahmadinejad has questioned the recent 'leaked' do...

overloading global dereference operator?
Hi! I'm diving into the some more ++ specific aspects of c++ (and finally accepting that c++ is more than "a plus added to c" :), so that means using namespaces, templates, std::strings, lists, vectors, operator overloading and what not.. And i was wondering if there is a way to override the global dereference operator, so to be able to check the address that one tries to dereference. This gives the ability to throw an exception when one would try to dereference NULL for instance. I have tried several different ways to do it, but gcc doesn't like what i'm asking of him....

Modules, global variables and such
Hello, I have the following problem. I had a script that contained only one global variable which was called $DEBUG. I just used it to print out various think or do some checks in debugging mode print "number of objects $n_obj\n" if $DEBUG ; check_sanity if $DEBUG ; The program grew and now I splitted it up into several modules. Question: what to do with the debug variable? * I could make it local to all of the functions, but I would really hate passing it through to each of the functions separately. Firstly, IMO this is one of the few cases where global variables are...

OT: Sun Microsystems And NCR Sign Global Alliance
http://online.wsj.com/ # # Sun Microsystems And NCR Sign Global Alliance # # SANTA CLARA, Calif. -- Sun Microsystems Inc. (SUNW) and NCR Corp. # (NCR) expanded their relationship to offer products and services # for retail, financial and telecommunications customers worldwide. # # As part of the global alliance, NCR and Sun will work together # to offer systems for targeted vertical markets. NCR will sell # and deliver installation and support services for Sun's entry # to mid-range servers, storage systems and through complementary # infrastructure service...

Global Operators New/Delete and Visibility Across Translation Units
Hello, I have overloaded the global new, delete, new[] and delete[] operators inside a "static library". The library uses private memory allocation routines that must not be accessible from other translation units. Other translation units are therefore expected to define and use their own new/delete operators. When I link to this library the global new/delete operators conflict, and although no linker warnings are generated, the result is that translation units access each others memory allocation routines. How can I limit the scope of these operator definitions? Apparently it is ...

bash; globals; no global; what did I do wrong?
Why isn't __SVNROOT being globally set. What'd I do wrong Thank you ======================================== C_SVNROOT='SVNROOT' __SVNROOT= function svne_verify_svnroot (){ local iam='svne_verify_svnroot' __SVNROOT=$(eval echo \$$C_SVNROOT) echo value was JUST set $__SVNROOT >/dev/stderr <<<<<<== OK HERE return 0 } function setsvn { local iam='setsvn' if ! svnroot=$(svne_verify_svnroot) ; then echo $svnroot return 1 fi; echo Still $__SVNROOT should be set >/dev/stderr &...

global a b != global a; global b in this circumstance
I had to quickly hack a script and came across a "gotcha" that wasn't obvious because of a well-placed catch. Here is a script that shows the problem: #---------------------------- set x 1 set y1 "y1" proc ok {} { global x global y$x puts "$x [set y$x]" } proc fail {} { global x y$x puts "$x [set y$x]" } ok fail #---------------------------- Regards E Wilson ewilsonmail@gmail.com wrote: > I had to quickly hack a script and came across a "gotcha" that wasn't > obvious because of a well-placed catch. > > Here...

global or local
Do you guys know if the PROC NLP procedure provides the global minimum or just the first minimum it arrives at (local) like EXCEL? ...

how to integrate dll created in clarion to a main application(with global variables)
Hi all, We want to integrated different modules of application, using same dictionary and database. All these applications are using global variables. We had created dll for each module, with global variable. While integrating, it gives errors , saying variable repetitions. (We had integrated a demo application, without global variable.) We followed the approch, mentioned in the Clarion help file. Can we declare a variable without memory allocation? Please help.. Jimmy ...

Load global temporary table
Hi all, I have a fixedwidth .DAT file and a DB2 table, I have to create a flat file by pulling data from both file and DB2 table. I tried using IMPORT/LOAD utilities to load to a global temporary table and thought of joining it and extracting, but IMPORT/LOAD are not allowed on temporary table. Can you please suggest me how to go about it... thanks in advance, kore Can you Import it to a real TABLE, and then export with a join? B. As per my requirements I am not supposed to use a real table. Please suggest me..... vijay_dwi@yahoo.com wrote: > As per my requ...

Which book shall I read to learn parameter passing, memory allocating for global, local variable?
Which book shall I read to learn parameter passing, memory allocating for global, local variable? ...