Link to home
Start Free TrialLog in
Avatar of j2911

asked on

Oracle Temporary Table question

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,  
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;
Avatar of Sean Stuber
Sean Stuber

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
Avatar of David VanZandt
David VanZandt
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of j2911


please close this question - sorry but much has happened since I posted this question and it needs to be closed
did you get an answer or are you just picking a random answer to make the abandonded question notifications stop?