Solved

SQL loader

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

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 76

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

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.

Question has a verified solution.

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

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…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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.
This video shows how to recover a database from a user managed backup

932 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