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 Go to next message
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 #450635 is a reply to message #450634] Thu, 08 April 2010 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 67653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Input can be either in control file, either in outside file but it can't be both at the same time.
Load in 2 steps.

Regards
Michel
Re: How to use sqlldr if the data is in the control file as well as in the datafile [message #450655 is a reply to message #450634] Thu, 08 April 2010 09:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Quote:
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?

You are generating the control file dynamically.
Why not just create a separate file for CountryName and merge it with the csv?
For reasons obviously stated by Michel, you do not want to to have two datafiles.
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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> 

Re: How to use sqlldr if the data is in the control file as well as in the datafile [message #450677 is a reply to message #450676] Thu, 08 April 2010 12:44 Go to previous messageGo to next message
Michel Cadot
Messages: 67653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
/forum/fa/2115/0/ Once again! I was sure you will come with THE solution.

Regards
Michel
Re: How to use sqlldr if the data is in the control file as well as in the datafile [message #450680 is a reply to message #450677] Thu, 08 April 2010 12:57 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
That only solves the first requirement. As to the second requirement, it would help to see some sample data as was provided for the first requirement, as the verbal description was unclear. If the country is also in the data file, then just load the data file. If the country is only in the control file, then is there some other common column that relates them? Or is it dependent on matching order of rows or what? You may not be able to do what you want in one single load. You will probably need to load into staging tables, then insert into the target table using a select with a join of the staging tables.
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 Go to previous messageGo to next message
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 load if the data is in the control file as well as in the datafile [message #450727 is a reply to message #450722] Thu, 08 April 2010 23:46 Go to previous messageGo to next message
Michel Cadot
Messages: 67653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you repeat the same when Barbara perfectly answer the previous WITH AN EXAMPLE?

Regards
Michel
Re: How to load if the data is in the control file as well as in the datafile [message #450730 is a reply to message #450727] Fri, 09 April 2010 00:08 Go to previous messageGo to next message
anjanikumar
Messages: 5
Registered: April 2010
Location: Chennai
Junior Member
Sorry for the inconvinience.
yesterday, when i posted, my network connection has gone and i thought it was not posted and did it again. After that i came to know that it was already posted.
Thanks for the reply and Once again Sorry for the inconvinience.
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 Go to previous messageGo to next message
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 #450741 is a reply to message #450737] Fri, 09 April 2010 01:07 Go to previous messageGo to next message
Littlefoot
Messages: 21648
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As you dynamically create a control file, and if "Taiwan" is the only value in the BEGINDATA section of the control file, then - dynamically - place is into the loading section as a constant value.
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 Go to previous messageGo to next message
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
Re: How to use sqlldr if the data is in the control file as well as in the datafile [message #450789 is a reply to message #450766] Fri, 09 April 2010 03:56 Go to previous message
Littlefoot
Messages: 21648
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Where did you include a constant?

Should be something like this:
load data
infile 'test1.csv'
insert into table tbl_emp
fields terminated by "," 
optionally enclosed by '"'
trailing nullcols
(countryname constant 'TW',        --> this should be created with your Perl script
 empno, 
 empname, 
 sal, 
 deptno
)
Previous Topic: Export dump of large table
Next Topic: sql loader
Goto Forum:
  


Current Time: Mon Jan 25 00:36:39 CST 2021