| How to load data from an excel sheet to a global temporary table using SQLLDR [message #228441] |
Tue, 03 April 2007 01:41  |
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( 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 #228459 is a reply to message #228446] |
Tue, 03 April 2007 02:23  |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
