• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1415
  • Last Modified:

CREATING GLOBAL TEMPORARY TABLES in oracle9i

I want it to be created if table doesn't exist.
what is the corrent syntax?
0
kako
Asked:
kako
  • 3
  • 2
1 Solution
 
DatamonkeyCommented:
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
 
kakoAuthor Commented:
well, I'll change the question

what is the correct syntax to avoid this error?
0
 
andrewstCommented:
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!

 
kakoAuthor Commented:
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
 
andrewstCommented:
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
 
kakoAuthor Commented:
WOW!!

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

Thank you
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now