Home » RDBMS Server » Server Utilities » How to load if the data is in the control file as well as in the datafile (2 threads merged by bb) (Oracle 10g enterprise release 10.2.0.1.0, OS: Solaris)
| How to load if the data is in the control file as well as in the datafile (2 threads merged by bb) [message #450634] |
Thu, 08 April 2010 08:10  |
anjanikumar
Messages: 5
Registered: April 2010
Location: Chennai
|
Junior Member |
|
|
Hi,
I am using perl script to dynamically generate the control file.If I have data in the control file as well as in the
datafile, how would i write the control file in that case.
Is the below one correct?
load data
INFILE '*'
INFILE '/export/home/test/test.csv'
INSERT INTO TABLE EMP fields
terminated by "," optionally enclosed by '"'
trailing nullcols
( empno, empname, sal, deptno )
BEGINDATA
100,testName,10000,90
150,bestName,10000,90
900,lastName,10000,90
my test.csv contains the following data.
200,testName1,20000,20
300,testName2,20000,20
400,testName3,20000,20
500,testName4,20000,20
600,testName5,20000,20
when i run sqlldr, i am getting the following error.
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Apr 8 15:58:30 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL*Loader-284: Warning: Input data file /export/home/test/test.csv specified multiple times.
SQL*Loader-275: Data is in control file but "INFILE *" has not been specified.
Commit point reached - logical record count 4
Commit point reached - logical record count 5
Commit point reached - logical record count 9
Commit point reached - logical record count 10
And my DB is getting loaded with 10 records (2 times from the test.csv)
2nd requirement:
----------------
Is there any way that if my control file contains half of the data and my data file contains the other half of the data can i club this data into a logical record in the control file to populate the DB?
My exact 2nd requirement is, my DB contains 5 cols and for 1 col the data is common(countryName) which i have to pass to the control file dynamically and the .csv file contains the data for the other four cols. How could i combine these in the ctrl file and populate the DB?
so if the DB contains CountryName, empid, ename, sal and dept
I will get the CountryName to the ctrl file and csv contains the data for empid, ename, sal and dept. How would i combine these data into a logical record and populate the DB?
Thanks
Anjani Kumar
|
|
|
|
|
|
|
|
| Re: How to use sqlldr if the data is in the control file as well as in the datafile [message #450676 is a reply to message #450635] |
Thu, 08 April 2010 12:35  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You can have data in both the control file and an external file, as demonstrated below. You just need to remove the quotes from around the asterisk, otherwise it thinks the asterisk is a filename, adds the default dat extension, and is expecting a file called *.dat, instead of understanding that the * means that the data is at the bottom of the control file.
-- test.csv:
200,testName1,20000,20
300,testName2,20000,20
400,testName3,20000,20
500,testName4,20000,20
600,testName5,20000,20
-- test.ctl:
load data
INFILE *
INFILE 'test.csv'
INSERT INTO TABLE EMP2 fields
terminated by "," optionally enclosed by '"'
trailing nullcols
( empno, empname, sal, deptno )
BEGINDATA
100,testName,10000,90
150,bestName,10000,90
900,lastName,10000,90
-- table, load, and results;
SCOTT@orcl_11g> CREATE TABLE emp2 AS
2 SELECT empno, ename AS empname, sal, deptno
3 FROM emp WHERE 1 = 2
4 /
Table created.
SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
SCOTT@orcl_11g> SELECT * FROM emp2 ORDER BY empno
2 /
EMPNO EMPNAME SAL DEPTNO
---------- ---------- ---------- ----------
100 testName 10000 90
150 bestName 10000 90
200 testName1 20000 20
300 testName2 20000 20
400 testName3 20000 20
500 testName4 20000 20
600 testName5 20000 20
900 lastName 10000 90
8 rows selected.
SCOTT@orcl_11g>
|
|
|
|
|
|
|
|
| How to load if the data is in the control file as well as in the datafile [message #450722 is a reply to message #450634] |
Thu, 08 April 2010 23:38 |
anjanikumar
Messages: 5
Registered: April 2010
Location: Chennai
|
Junior Member |
|
|
HI,
I have a requirement to use sqlldr in a case that the data will be present in both the ctl file and in the datafile.
Is the following a properway to write the ctl file?
load data
INFILE '*'
INFILE '/export/home/krishna/test1.csv'
INSERT INTO TABLE TBL_EMP fields
terminated by "," optionally enclosed by '"'
trailing nullcols
( empno, empname, sal, deptno )
BEGINDATA
100,testName,10000,90
150,bestName,10000,90
900,lastName,10000,90
It is giving the following result.
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Apr 8 15:58:30 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL*Loader-284: Warning: Input data file /export/home/krishna/test1.csv specified multiple times.
SQL*Loader-275: Data is in control file but "INFILE *" has not been specified.
Commit point reached - logical record count 4
Commit point reached - logical record count 5
Commit point reached - logical record count 9
Commit point reached - logical record count 10
And the data in the ctl file is not getting loaded to the DB but the data in the csv file is getting loaded twice.
Is there any way to use sqlldr if half of the data is in ctl file and the other half is present in the csv file?
For eg: if my table is having fields, Country, empid, empname, sal, dept and out of them the value from country is same so that it comes from the ctl file and the remaining data will be present in the csv file. How to write the control file in that case.Please help me with a solution/suggestion.
Regards,
Anjani Kumar
|
|
|
|
|
|
|
|
| Re: How to use sqlldr if the data is in the control file as well as in the datafile [message #450737 is a reply to message #450680] |
Fri, 09 April 2010 00:51 |
anjanikumar
Messages: 5
Registered: April 2010
Location: Chennai
|
Junior Member |
|
|
Hi Barbara,
Thank you very much for the reply and the explanation.The first sloution is working fine.
As i said about my other requirement,
the country is not present in the data file.It is only in the control file and there is no other common column that relates them.
I will put it in this way.
To my table EMP that contains CountryName, empid, ename, sal and dept i want to pass the Countryname dynamically from the control file and empid, ename, sal and dept are present in the data file(.csv file)
(This is the reason i thought of the first approach for which you have provided the solution.)
NOw through the first approach i want to achieve some thing like as shown below.
load data
INFILE *
INFILE 'test1.csv'
INSERT INTO TABLE TBL_EMP fields
terminated by "," optionally enclosed by '"'
trailing nullcols
( countryname,empno, empname, sal, deptno )
BEGINDATA
Taiwan
Now this Taiwan should be added to the each logical record that will be prepared for the data from the csv file.
So, if the test1.csv contains the data(empno, empname, sal, deptno )as shown here
400,empName3,20000,20
500,empName4,20000,20
600,empName5,20000,20
and the logical record has to be prepared as
Taiwan,400,empName3,20000,20
Taiwan,500,empName4,20000,20
Taiwan,600,empName5,20000,20
Is there any way to achieve this?I hope i am clear now.
Thanks
Anjani Kumar
|
|
|
|
|
|
| Re: How to use sqlldr if the data is in the control file as well as in the datafile [message #450766 is a reply to message #450741] |
Fri, 09 April 2010 02:34 |
anjanikumar
Messages: 5
Registered: April 2010
Location: Chennai
|
Junior Member |
|
|
Hi,
I prepared the file dynamically but the data is not going to the DB. out of 10 rows from my csv file, nothing is going to DB if i generate the ctl file as follows.
load data
INFILE *
INFILE 'test1.csv'
INSERT INTO TABLE TBL_EMP fields
terminated by "," optionally enclosed by '"'
trailing nullcols
( countryname,empno, empname, sal, deptno )
BEGINDATA
TW
So, in place of countryname , Always the TW from BEGINDATA section should go to the DB (for whatever logical record sqlldr prepares, it should add TW in the begenning of that record.)and for the other columns the data should go from the csv file. Is there any way to do that? If so , could you please post me the syntax or correct the above code and post it?
I hope you got clearly what i am asking.....
Regards,
Anjani Kumar
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 23:47:02 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
