| SQL Loader [message #372223] |
Fri, 19 January 2001 10:28  |
Arch
Messages: 1
Registered: January 2001
|
Junior Member |
|
|
Hi
I am trying to load a data file using sqlloader. I have one column country_code which is not in the data file that I want to load. I want to pass this code as a parameter along with the data file. Is there any way in sql loder to do this? I can pass the file using data=input file but how do I pass the code? And if I can pass the code how do I read it in the script?
Thanks.
|
|
|
|
| Re: SQL Loader [message #372233 is a reply to message #372223] |
Fri, 19 January 2001 18:45  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
Similar to an earlier posting, I think you need to create the control file at runtime. You can create it from a SQL*Plus script (prompt commands), DBMS_OUTPUT in PL/SQL, or Print or Echo or whatever you choose (e.g. dos batch file, unix schell script etc)
Hi,
if you want to insert a constant date then
LOAD DATA
INFILE 'c:\beta\betacode.txt'
replace
INTO TABLE table1
FIELDS TERMINATED BY '~' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(id,
code#,
code_descr,
cdate "to_date('01/18/2001', 'MM/DD/YYYY')")
But you have to modify the ctl file manually every time.
if you want to automate the process
this is the sql+ script.....
It will basically get the sysdate from oracle
and use it as a constanct to make a ctl file
and it will run the control file to load the data...
modify the table name to your need...
set wrap off
set linesize 100
set feedback off
set pagesize 0
set verify off
set termout off
COLUMN TDATE NEW_VAL CONSDATE;
SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY') TDATE FROM DUAL;
spool c:\windows\desktop\test.ctl
prompt LOAD DATA
prompt INFILE *
prompt INTO TABLE &1
prompt REPLACE
prompt FIELDS TERMINATED BY '|'
prompt (
select decode(column_id,1,'',',') || lower(column_name)
from user_tab_columns
where table_name = 'TEST_TAB'
/
prompt "TO_DATE(&CONSDATE, 'MM/DD/YYYY')" )
SPOOL OFF;
host sqlldr control=c:\windows\desktop\test.ctl
bala
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
