?
Solved

CREATING GLOBAL TEMPORARY TABLES in oracle9i

Posted on 2003-03-06
6
Medium Priority
?
1,402 Views
Last Modified: 2012-06-22
I want it to be created if table doesn't exist.
what is the corrent syntax?
0
Comment
Question by:kako
[X]
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
  • 3
  • 2
6 Comments
 
LVL 2

Expert Comment

by:Datamonkey
ID: 8078398
the answer is almost in the question:
CREATE GLOBAL TEMPORARY TABLE TESTTAB(TESTCOL NUMBER(10));

if it doesn't exist it will be created, if it already existed then you will simply get ORA-955.
0
 

Author Comment

by:kako
ID: 8078436
well, I'll change the question

what is the correct syntax to avoid this error?
0
 
LVL 15

Expert Comment

by:andrewst
ID: 8078960
I think you have probably misunderstood the point of global temporary tables.  They are to be created ONCE, PERMANENTLY just like a normal table.  You do not create and drop them at runtime.  It is the DATA in the table that is temporary, not the table definition itself.

If you did have a valid reason to attempt to create a table that might already exist, you would have to use PL/SQL if you wanted to avoid the error.  However, CREATE TABLE is not valid in PL/SQL so you would then have to use EXECUTE IMMEDIATE to do the create table statements - making it relatively complex.

Here is one way:

SQL> declare
  2    procedure run_ddl( p_ddl in varchar2 )
  3    is
  4      e_already_exists exception;
  5      pragma exception_init( e_already_exists, -955 );
  6    begin
  7      dbms_output.put_line( p_ddl );
  8      execute immediate p_ddl;
  9      dbms_output.put_line( ' - succeeded' );
 10    exception
 11      when e_already_exists then
 12        dbms_output.put_line( ' - failed, table exists already' );
 13    end;
 14  begin
 15    run_ddl( 'create table t( id number )' );
 16    run_ddl( 'create table t( id number )' );
 17* end;
SQL> /
create table t( id number )
 - succeeded
create table t( id number )
 - failed, table exists already

PL/SQL procedure successfully completed.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:kako
ID: 8079040
I'm working under java.
several clients might need to use the same temporary table.
I dont want second one's code to fail while issuing
"create global ...." if first user already have created it.
of course I can catch exceptions and handle the situation,
but I hoped there would be some syntax, like
"create or replace".


0
 
LVL 15

Accepted Solution

by:
andrewst earned 200 total points
ID: 8079114
Reread my first paragraph:

"I think you have probably misunderstood the point of global temporary tables.  They are to be created ONCE, PERMANENTLY just like a normal table.  You do not create and drop them at runtime.  It is the DATA in the table that is temporary, not the table definition itself."

So the issue of one user's "create global" failing because another has already done it just doesn't arise - users don't CREATE global temporary tables, they just USE them.

The data a user inserts into a global temporary table is private to their current session.  No other user can see, update or delete that data.  No other user's data can be seen, updated or deleted by this user.

The data is deleted AUTOMATICALLY by Oracle either:
a) when the user does a COMMIT or ROLLBACK.  This is the default.
b) when the user's session is disconnected - if the GTT was created with the "ON DELETE PRESERVE ROWS" option.

And there is no "CREATE OR REPLACE" syntax for tables (global temporary or otherwise).
0
 

Author Comment

by:kako
ID: 8079280
WOW!!

I thought that temporary table
is dropped as soon it's creator close session

Thank you
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

765 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