Oracle Temporary Table question

j2911
j2911 used Ask the Experts™
on
Is this how you create an Oracle Temporary table?  If not can you tell me how?

create global temporary table SHIP_INFO_TEMP
on commit preserve rows as
with myXML as (
select test_id||'-'||ship_seq_nbr as scnro_id, TRKNG_NBR,  
xmltype(BASELINE)
xmlcol from IAT.ship_info
where isvalid(baseline) = 1)
select scnro_id, TRKNG_NBR, TRKNG_NBR,
extractvalue(xmlcol,'//MNBR') "MN",
extractvalue(xmlcol,'//P1') "P1",
extractvalue(xmlcol,'//RECPCNTRYCD') "Dest",
extractvalue(xmlcol,'//RECPNM') "Recipient_Name",
extractvalue(xmlcol,'//SNDRCNTRYCD') "Origin",
extractvalue(xmlcol,'//SHIPPING_DEVICE') "Software",
extractvalue(xmlcol,'//SVCTYPCD') "Service"
from myXML;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

Commented:
normally I don't create a temporary table with the CTAS syntax

you have a syntax error in that you are selecting TRKNG_NBR  twice, but other than that it should be valid.


you'd only do that once though.  If you needed to fill the temp table a second time, you'd just do an insert.

Oracle temp tables aren't like sql server where you create and drop them repeatedly.  in Oracle you create them once and then simply reuse them, almost like a normal table, except the data is in TEMP and is only visible to your own session and goes away at the end of your session
Oracle Database Administrator III
Commented:
Hello j, may I recommend you save your points next time and search first for previously asked questions --  one of many answers for you is at http://www.experts-exchange.com/Database/Oracle/Q_22073308.html#a18019419.

And, are you aware of the free syntax documentation available from Oracle:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7002.htm.

Temporary Table Example:

The following statement creates a temporary table today_sales for use by sales representatives in the sample database. Each sales representative session can store its own sales data for the day in the table. The temporary data is deleted at the end of the session.

CREATE GLOBAL TEMPORARY TABLE today_sales
   ON COMMIT PRESERVE ROWS
   AS SELECT * FROM orders WHERE order_date = SYSDATE;

Author

Commented:
please close this question - sorry but much has happened since I posted this question and it needs to be closed
Most Valuable Expert 2011
Top Expert 2012

Commented:
did you get an answer or are you just picking a random answer to make the abandonded question notifications stop?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial