Solved

Inconsistancy between data file and control file

Posted on 2011-09-09
8
436 Views
Last Modified: 2013-12-19
What are the possible scenarios where there may be inconsistancy between
data file and the control file.
0
Comment
Question by:gram77
  • 4
  • 4
8 Comments
 

Author Comment

by:gram77
ID: 36509049
What will be the effect on loading data if some columns of control file are commented?
will colD in data file get loaded into colF of control file?
data file      ControlFile
colA            colA
colB            colB
colC            colC
colD            colD (commented)
colE            colE (commented)
colF            colF
colG            colG


What will be the effect on loading data if some columns of data file are commented.
will colF of data file get loaded into colD of control file?
data file            ControlFile
colA                  colA
colB                  colB
colC                  colC
colD (commented)      colD
colE (commented)      colE
colF                  colF
colG                  colG


What will be the effect on loading data if both data file and control file are commented.
will colB in data file get loaded into colD in control file and
will colC in data file get loaded into colE in control file and
will colF in data file get loaded into colG in control file and
data file            ControlFile
colA                  colA
colB                  colB (commented)
colC                  colC (commented)
colD (commented)      colD
colE (commented)      colE
colF                  colF (commented)
colG                  colG
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36509933
This sounds a lot like a homework/exam question.

Please clarify.

If this isn't a homework question:  I do not understand what you mean by "control file are commented"
0
 

Author Comment

by:gram77
ID: 36510304
"control file are commented" means columns of control file are commented

We get data from a vendor that is entered into tables using control file.
The vendor keeps removing/ adding columns in the data file.

I need to verify if we are inserting right data into the table. ie. colA is not going into colB but goes into colA only.

There are times when some columns are removed by vendor from the data file, in such cases we need to comment out the columns in control file
as well so that there is a sync between the datafile and the control file. At times this may not be done properly and it is feared that
there is a mismatch between columns of data file and control file... I need to check such instances

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36510327
Still not understanding.

SQL Loader control file?

Can you show me an example of what 'commented' means?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:gram77
ID: 36510633
--SQLLDR ctl file
OPTIONS (DIRECT=FALSE, PARALLEL=FALSE, BINDSIZE=50000, ROWS=200, READSIZE=65536)
LOAD DATA
APPEND
INTO TABLE abc
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
ADR_UNDL_TICKER
ADR_SH_PER_ADR
DVD_CRNCY
EQY_DVD_SH_12M_NET
EQY_DVD_SH_12M
EQY_DVD_SH_LAST
--CPN
EQY_LAST_DPS_GROSS
EQY_DVD_PCT_FRANKED
EQY_DVD_TYP_LAST
EQY_DVD_FREQ
--CPN_FREQ
PFD_DVD_PAY_DT
DVD_RECORD_DT
DVD_DECLARED_DT
EQY_SPLIT_DT
EQY_SPLIT_RATIO
..
..
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36510675
I've never tried commenting out fields in SQL Loader.

If that doesn't cause a syntax error then data will get loaded in position order.

field position 1 in the datafile will get loaded into field position 1 in the control file.

In the example above whatever is in position 7 in the data file will be placed in EQY_LAST_DPS_GROSS.
0
 

Author Comment

by:gram77
ID: 36528190
There are times where the sequence of fileds in data file does not match with the sequence of fileds in control file.
Does this mean that wrong data is going into wrong columns of the table?

DataFile      ControlFile
colA            ColA
colB            ColB
colC            ColD
colD            colE
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 36530496
I'm not sure I am fully understanding the full question here.

If your datafile 'columns' do not positionally match the columns in the control file, the data likely isn't going where you think it is.

I don't know everything there is to know about sql loader but I believe it is a one-to-one deal between data fields and control file fields.

It should be a simple matter to create a quick test case to test it.

A simple table, three columns, a simple control file with some data.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now