Home » RDBMS Server » Server Utilities » Issues with SQL loader Control file
Issues with SQL loader Control file [message #166629] |
Fri, 07 April 2006 05:41 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
d_indrani
Messages: 8 Registered: November 2005 Location: Bangalore, India
|
Junior Member |
|
|
Hi Friends,
I have one table with 4 columns. I have written a control file to load data which will accept if 4 values are passed. If 3 are passed it will reject to load that data but if 5 vals will be there it will load first 4 values and ommit 5thone.
I want such one so that it will reject loading in case 5 fields too.
Please share your ideas on this issue.
Thanks in Adv.
|
|
|
Re: Issues with SQL loader Control file [message #166649 is a reply to message #166629] |
Fri, 07 April 2006 06:40 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/42800.jpg) |
Mahesh Rajendran
Messages: 10707 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
I cannot reproduce your case.
As long as you maintain the DELIMITERS, it will work.
In this case table DEPT has only 3 columns and you can see the the values '4thcol' is not loaded.
oracle@mutation#cat dept.data
10,ACCOUNTING,NEW YORK,4thcol
20,RESEARCH,,4thcol
30,,CHICAGO,4thcol
,OPERATIONS,BOSTON
,,lastcol
,secondcol,
oracle@mutation#sqlldr userid=scott/tiger control=dept.ctl
SQL*Loader: Release 9.2.0.4.0 - Production on Fri Apr 7 07:38:37 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 6
oracle@mutation#query mutation scott.dept
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH
30 CHICAGO
OPERATIONS BOSTON
lastcol
secondcol
6 rows selected.
|
|
|
Re: Issues with SQL loader Control file [message #166665 is a reply to message #166649] |
Fri, 07 April 2006 07:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
d_indrani
Messages: 8 Registered: November 2005 Location: Bangalore, India
|
Junior Member |
|
|
Hi Mahesh,
Thanks for your quick response. You are very right in this case.
I will explain you my requirement. I have a Table with 3 columns.
Now I want, if there are 4/5 (more than 3)values in data file, then this record should get rejected while loading(sqlldr).
Is there any command which I can use inside the control file so that it will accept only those records which all are having 3 values only i.e neither less than 3 nor more than 3.In these both cases loading should get failed.
It should not ignore the 4thcol and insert data into the table, instead it should reject the record.
Thanks
Indrani
|
|
|
Re: Issues with SQL loader Control file [message #166691 is a reply to message #166665] |
Fri, 07 April 2006 10:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/42800.jpg) |
Mahesh Rajendran
Messages: 10707 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
I am not aware of any method within sqlldr.
But with simple script, the job can be done
Just fix your inputfil before loading using sqlldr.
This can be completely automated.
#dept.data is original input file
#filtered_dept.data is the fixed file.
#Load this file using sqlldr
oracle@mutation#cat dept.data
10,ACCOUNTING,NEW YORK
20,RESEARCH,,4thcol,5col
30,,CHICAGO,4thcol,,6col
,OPERATIONS,BOSTON,
,,lastcol
,secondcol,
oracle@mutation#awk -F',' 'NF ~/3/' dept.data >filtered_dept.data
oracle@mutation#cat filtered_dept.data
10,ACCOUNTING,NEW YORK
,,lastcol
,secondcol,
[Updated on: Fri, 07 April 2006 10:39] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Sat Sep 07 06:06:29 CDT 2024
|