Home » SQL & PL/SQL » SQL & PL/SQL » How to load data from an excel sheet to a global temporary table using SQLLDR
How to load data from an excel sheet to a global temporary table using SQLLDR [message #228441] Tue, 03 April 2007 01:41 Go to next message
agnimitapal
Messages: 2
Registered: April 2007
Location: kolkata
Junior Member
Hi,
I need to load data from an excel sheet into a temporary table so that the rows can be deleted after commit.
The script for the table creation is:

create global temporary table TEMP
(
LST_NM VARCHAR2(60) not null,
FST_NM VARCHAR2(30),
PH_NUM VARCHAR2(10),
SRC_CD NUMBER(3) not null,
LST_UPD_ID VARCHAR2(Cool not null
)
on commit delete rows;

In the control file, we are mentioning this temporary table into which the data will be loaded as follows:-

OPTIONS (rows=1)
LOAD DATA
APPEND
INTO table TEMP
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
LST_NM "TRIM(:LST_NM)",
FST_NM "TRIM(:FST_NM)",
PH_NUM "TRIM(:PH_NUM)",
SRC_CD "TRIM(:SRC_CD)",
LST_UPD_ID "TRIM(:LST_UPD_ID)"
)

But when I am trying to run the control file using sqllder, I am facing this problem :- SQL*Loader-280: table TEMP is a temporary table.

Please help!
Re: How to load data from an excel sheet to a global temporary table using SQLLDR [message #228446 is a reply to message #228441] Tue, 03 April 2007 01:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What would be the use of loading them into a temporary table? You would not be able to see them, because you can only see the data from the session you inserted them.
And to add to that, you have on commit delete rows.
sqlloader commits, whence deletes all rows...

[Updated on: Tue, 03 April 2007 01:59]

Report message to a moderator

Re: How to load data from an excel sheet to a global temporary table using SQLLDR [message #228448 is a reply to message #228441] Tue, 03 April 2007 02:00 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
AFAIK - you can NOT load data to GTT.
Can you explain the reason for it. IMHO even when you load the data - nobody (except SQL*Loader session) may access it anyway.
I suggest defining your file as external table instead of using SQL*Loader.

HTH.
Michael.
Re: How to load data from an excel sheet to a global temporary table using SQLLDR [message #228449 is a reply to message #228441] Tue, 03 April 2007 02:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use a GTT, use an external table (follow the link and the link in the link...).

Regards
Michel
Re: How to load data from an excel sheet to a global temporary table using SQLLDR [message #228459 is a reply to message #228446] Tue, 03 April 2007 02:23 Go to previous messageGo to next message
agnimitapal
Messages: 2
Registered: April 2007
Location: kolkata
Junior Member
Hi,
Actually my main requirement is to insert/update data from the excel sheet into 2 tables, say T1 and T2.
Before inserting/updating, I need to check for a few conditions based on which i would either insert or update the tables. Therefore I first need to load the data into a temporary table and write a trigger, which, after inserting a row into the temporary table would check for the conditions and in turn insert or update in the 2 tables. As per my requirement, all the rows of the temporary table should be deleted after commit since it should not contain the data of the excel sheet.
This is why I need the temporary table. Please help.
Re: How to load data from an excel sheet to a global temporary table using SQLLDR [message #228489 is a reply to message #228459] Tue, 03 April 2007 03:51 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't need a GTT.
Use an external table and check your requirements on it.
I'm pretty sure you can do all you have to do in a couple of SQL statements.

Regards
Michel
Previous Topic: Temporary table
Next Topic: Behaviour of Timestamp DataType
Goto Forum:
  


Current Time: Sun Dec 04 00:29:03 CST 2016

Total time taken to generate the page: 0.12599 seconds