| Writting keyword to table [message #180449] |
Mon, 03 July 2006 10:41  |
gconner1997
Messages: 10
Registered: July 2006
Location: New York
|
Junior Member |
|
|
Hi,
Not sure how to describe this, so below my have redundant examples:
I'm trying to write the 'DATA' keyword contents into my table during the load.
My datafile is one column/field short. The last column in the table I want to load is myfilename. I use the trailing nullcols and wish to load that last column with the contents of the keywork DATA from the command line.
From the command line:
sqlldr <user/pwd@server> control=myctrl.ctl data="mydata.dat" log=....
What I want to do is pass the phrase "mydata.dat" to the control file into my table.
LOAD DATA
INFILE *
INTO TABLE emp
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(MYIDINFILE NULLIF (MYIDINFILE=BLANKS),
MYFILENAME "DATA"
)
I found a solution that suggests for instance
FILENAME "USER"
This works. I actually get my name in the field FILENAME during the load.
Logically I would think the line should be one of these:
myFileName "DATA"
myFileName "INFILE"
myFileName DATA
myFileName INFILE
myFileName "$4"
myFileName ":4"
myFileName "&4"
myFileName $4
myFileName :4
myFileName &4
but nothing works.
I know I can actually generate a constant from a pl/sql statement, or I can write a python script to append each line in the file I load with the name of the file,
but this would be so much easier if I could just capture the data on the command line.
Can this be done?
Thanks
--glenn
|
|
|
|
|
|
|
|
| Re: Writting keyword to table [message #180459 is a reply to message #180452] |
Mon, 03 July 2006 11:42  |
gconner1997
Messages: 10
Registered: July 2006
Location: New York
|
Junior Member |
|
|
Thanks Mahesh,
I have a program that generates the .ctl file, so I am getting the output I want. However, I just figured I could use data from the sqlldr command-line the same way I use a argv in c++ or python, or a $1, $2 in some shell scripts.
Here is an actual example that will work assuming you
replace the userid info with your info:
create table t_myid (
myid varchar2(10),
myfilename varchar2(50)
)
I create a file using VI:
vi myfile.dat:
a
b
c
I create a test control file
test.ctl:
INFILE *
APPEND INTO TABLE T_MYID
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
MYID NULLIF (MYID=BLANKS),
MYFILENAME "USER"
)
Then I run this:
sqlldr scott/scott@madeup control=test.ctl data=myfile.dat
Then I do a select from my table
select * from t_myid
I get this output:
myid myfilename
a SCOTT
b SCOTT
c SCOTT
Note the line MYFILENAME "USER" in the .ctl file
was replace with 'SCOTT'
Now instead of scott I'd like to load "myfile.dat"
from the command line, since the command line changes
so select * from t_myid: would return:
myid myfilename
a myfile.dat
b myfile.dat
c myfile.dat
instead of :
test.ctl:
INFILE *
APPEND INTO TABLE T_MYID
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
MYID NULLIF (MYID=BLANKS),
MYFILENAME "USER"
)
I would think this:
test.ctl:
INFILE *
APPEND INTO TABLE T_MYID
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
MYID NULLIF (MYID=BLANKS),
MYFILENAME "DATA"
)
:would work since sqlloader should replace the keyword DATA with MYFILE.DAT the
same way it would replace "USER" with SCOTT
Therefore in theory if myfile1.dat, myfile2.dat, and myfile3.dat were identical
running:
sqlldr scott/scott@madeup control=test.ctl data=myfile1.dat
sqlldr scott/scott@madeup control=test.ctl data=myfile2.dat
sqlldr scott/scott@madeup control=test.ctl data=myfile3.dat
would return:
myid myfilename
a myfile1.dat
b myfile1.dat
c myfile1.dat
a myfile2.dat
b myfile2.dat
c myfile2.dat
a myfile3.dat
b myfile3.dat
c myfile3.dat
Thanks
--glenn
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
