Solved

SQL loader

Posted on 2014-02-19
5
446 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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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 76

Expert Comment

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

Author Comment

by:d27m11y
Comment Utility
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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
>>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

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

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

771 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

10 Experts available now in Live!

Get 1:1 Help Now