Solved

SQL loader

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 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.

829 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