visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: Oracle :: Oracle non standard CREATE AS SELECT, empty table after creation !
By: Nono Great Britain  Date: 10/04/2008 09:30:57  English  Points: 20 Status: Answered
Quality : Excellent
According to SQL standards, the purpose of CREATE TEMPORARY TABLE ... AS SELECT ... is to fill in a temporary table with data! And keep the table only in the current user session.

I'm trying this :

create temporary table toto2 as select * from product; ERREUR à la ligne 1 : ORA-00933: La commande SQL ne se termine pas correctement



How to create a temporary table in Oracle ?
By: VGR Date: 10/04/2008 09:42:11 English  Type : Answer
the Oracle syntax for this is

create global temporary table toto2 as select * from product;



you've to add GLOBAL TEMPORARY

you've also something to add to achieve the expected result ;-)

If you create a [GLOBAL] TEMPORARY table, Oracle creates the table but it is EMPTY !!!

demonstration :

SQL> select count(*) from product; Je Avr 10 COUNT(*) ---------- 10 SQL> create global temporary table toto2 as select * from product; Table créée. SQL> select * from toto2; aucune ligne sélectionnée SQL> desc toto2; Nom NULL ? Type ----------------------------------------- -------- ---------------------------- PRODID NUMBER(6) DESCRIP VARCHAR2(30) SQL> @describe toto2 COLONNE DTYPE DLEN DNUL ---------- ---------- ---------- ---- PRODID NUMBER 22 Y DESCRIP VARCHAR2 30 Y



The Oracle trick is to add ON COMMIT PRESERVE ROWS (how verbose... And non-standard ! )

CREATE GLOBAL TEMPORARY TABLE my_temp_table ( column1 NUMBER, column2 NUMBER ) ON COMMIT PRESERVE ROWS;



***BUT*** it is IMPOSSIBLE to achieve your result in one SQL query. You simply can't apply ON COMMIT PRESERVE ROWS to a CREATE AS SELECT !

demonstration :

create global temporary table toto3 as select * from product on commit preserve rows; * ERREUR à la ligne 1 : ORA-00933: La commande SQL ne se termine pas correctement create global temporary table toto3 as (select * from product) on commit preserve rows; * ERREUR à la ligne 1 : ORA-00933: La commande SQL ne se termine pas correctement



you've to create the table, then do an INSERT SELECT

How unconvenient... :/

Oracle seems VERY LIMITED for that very common task.
By: VGR Date: 10/04/2008 17:25:53 English  Type : Comment
and this is supposedly because a TEMPORARY table is living as long as the transaction (not the user/client session ! ) and as a DDL statement like CREATE TABLE implies an implicit COMMIT, the current transaction is closed and re-opened, and the TEMPORARY table disappears.

This is crazy stupid ;-)

Moreover, were Oracle consistent, the structure of the table wouldn't stay in place. It should be disposed of as were its data.

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 292.937040 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page