OraFAQ Forum: Server Utilities » Issues with SQL loader Control file

Home » RDBMS Server » Server Utilities » Issues with SQL loader Control file

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Issues with SQL loader Control file [message #166629]

Fri, 07 April 2006 05:41

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.

Report message to a moderator

Re: Issues with SQL loader Control file [message #166649 is a reply to message #166629]

Fri, 07 April 2006 06:40

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.

Report message to a moderator

Re: Issues with SQL loader Control file [message #166665 is a reply to message #166649]

Fri, 07 April 2006 07:37

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

Report message to a moderator

Re: Issues with SQL loader Control file [message #166691 is a reply to message #166665]

Fri, 07 April 2006 10:38

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

Previous Topic:	To change date format
Next Topic:	Data Loading into a table with Identity type column(Sequence generator used) Direct path

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jul 04 20:15:50 CDT 2024