?
Solved

Inconsistancy between data file and control file

Posted on 2011-09-09
8
Medium Priority
?
454 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 77

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

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
 

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 77

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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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.

719 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