Avatar of j2911
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,  
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;
Oracle Database

Avatar of undefined
Last Comment
Sean Stuber

8/22/2022 - Mon
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
ASKER CERTIFIED SOLUTION
David VanZandt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
j2911

ASKER
please close this question - sorry but much has happened since I posted this question and it needs to be closed
Sean Stuber

did you get an answer or are you just picking a random answer to make the abandonded question notifications stop?
Your help has saved me hundreds of hours of internet surfing.
fblack61