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,'//MNB R') "MN",
extractvalue(xmlcol,'//P1' ) "P1",
extractvalue(xmlcol,'//REC PCNTRYCD') "Dest",
extractvalue(xmlcol,'//REC PNM') "Recipient_Name",
extractvalue(xmlcol,'//SND RCNTRYCD') "Origin",
extractvalue(xmlcol,'//SHI PPING_DEVI CE') "Software",
extractvalue(xmlcol,'//SVC TYPCD') "Service"
from myXML;
create global temporary table SHIP_INFO_TEMP
on commit preserve rows as
with myXML as (
select test_id||'-'||ship_seq_nbr
xmltype(BASELINE)
xmlcol from IAT.ship_info
where isvalid(baseline) = 1)
select scnro_id, TRKNG_NBR, TRKNG_NBR,
extractvalue(xmlcol,'//MNB
extractvalue(xmlcol,'//P1'
extractvalue(xmlcol,'//REC
extractvalue(xmlcol,'//REC
extractvalue(xmlcol,'//SND
extractvalue(xmlcol,'//SHI
extractvalue(xmlcol,'//SVC
from myXML;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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