Solved

SQL loader

Posted on 2014-02-19
5
455 Views
Last Modified: 2014-02-24
This was an interview question asked:

When I am trying to load only few columns in the table, I would mention those specific columns in control file to load the data. What if new column is added to the table. Would the sqlloader fail...

Could someone answer this
0
Comment
Question by:d27m11y
[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
  • 3
5 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39871746
Try it?

set up the table:
drop table tab1 purge;
create table tab1(col1 char(1), col2 char(1), col3 char(1));

Open in new window


Create a control file:
LOAD DATA
INFILE *
APPEND INTO TABLE tab1
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
        col1,
	col2
)
begindata
1,2
a,b

Open in new window


Then run sql loader:
sqlldr scott/tiger control=q.ctl

Open in new window


Answer:  It loads just fine...
0
 
LVL 23

Expert Comment

by:David
ID: 39871876
Slight has the answer -- though my guess is that it depends -- if the new column(s) were all NULL, then it might be okay.  With non-null data, however, your control file would have to specify the change.

(
        col1,
      col2,
         newcoln
)
begindata
1,2,someval
a,b,someval
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39871903
Excellent catch!  I didn't think about possible constraints with the new column.
0
 

Author Comment

by:d27m11y
ID: 39872022
When new column is added to the table, we are not using that in control file and not loading any data in the table for that specific column using sqlloader at that point of time. Would that control file still valid, I mean would sqlloader still run..
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39872081
>>would sqlloader still run

Tough question:  Answer is, yes and no.

If the new column has a constraint like a "not null" and the control file didn't account for that column, then it would run but it wouldn't load any rows.

So:  Yes, it would run but No, no rows would be loaded.  The produced LOG file would tell you that no rows were loaded but the command line would tell you that two rows were 'processed'.

You would not know if the processed rows were successful just from the command line output.  It would take looking at the log file to confirm.

Again, simple test case to test it.

Run my initial test case as-is.  Check the q.log file and table contents.

Then drop and recreate the table like this:
create table tab1(col1 char(1), col2 char(1), col3 char(1) not null);

Open in new window


Rerun the sqlldr command.  Compare the cmd window output then compare the table and log file contents.

It literally took me two minutes to come up with the test case I posted.
0

Featured Post

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!

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

740 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