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

We now celebrate our 5 years of existence and break our frequentation records... Here are the stats of those 5 years.

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

 Add This Article To:
 del.icio.usDel.icio.us  diggDigg  googleGoogle  spurlSpurl
 blinkBlink  wongWong  simpySimpy  yahooY! MyWeb 
EContact
browser fav
page generated in 4551.295040 milliseconds

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