Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL loader

Posted on 2014-02-19
5
Medium Priority
?
464 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
  • 3
5 Comments
 
LVL 78

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 78

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 78

Accepted Solution

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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 Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

886 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