OraFAQ Forum: Server Utilities » SQLLDR retaining DB column value

Home » RDBMS Server » Server Utilities » SQLLDR retaining DB column value (Oracle 11)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQLLDR retaining DB column value [message #621572]

Thu, 14 August 2014 08:15

Member2014
Messages: 33
Registered: July 2014
Location: USA

Member

I am using SQLLDR to load the data in a file into a database table. The Table has 22 columns out of which the first 20 columns come from the incoming data file and the last two columns values are computed as a part of the code and updated in the table.

The control file is as below

OPTIONS (
        ERRORS=1000,
        DIRECT=TRUE,
        READSIZE=104857600,
        BINDSIZE=104857600,
        ROWS=25000
        )
LOAD DATA
INFILE '-' "str X'0A'"
DISCARDMAX 1
TRUNCATE INTO TABLE STG_TABLE
FIELDS TERMINATED BY X'7C'
TRAILING NULLCOLS
 (
col1,
col2,
....
col20
)

Since I have added two new columns to the stg_table (i.e. col21, col22), I will need to modify the control file.
Can anyone suggest how I can modify the control file to retain the database value of col21, col22 while loading the file to the DB?

Thanks.

Report message to a moderator

Re: SQLLDR retaining DB column value [message #621573 is a reply to message #621572]

Thu, 14 August 2014 08:28

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://docs.oracle.com/database/121/SQLRF/statements_7002.htm#SQLRF54465

Report message to a moderator

Re: SQLLDR retaining DB column value [message #621586 is a reply to message #621572]

Thu, 14 August 2014 12:37

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Member2014 wrote on Thu, 14 August 2014 09:15

Since I have added two new columns to the stg_table (i.e. col21, col22), I will need to modify the control file.
Can anyone suggest how I can modify the control file to retain the database value of col21, col22 while loading the file to the DB?
.

Your question makes no sense. sqlldr "loads" data into a table. There is no mechanism to update any data.

Please look at external tables (http://docs.oracle.com/cd/B19306_01/server.102/b14215/et_concepts.htm) if you want a more robust solution.

Report message to a moderator

Re: SQLLDR retaining DB column value [message #621587 is a reply to message #621586]

Thu, 14 August 2014 12:53

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Or, create a database trigger which would set (just for example)

:new.col_21 := :new.col_1 * :new.col_2;  -- a computation, right?

(that might slow loading down; how many records are you loading?), or - a simpler solution - perform UPDATE after loading is done.

Report message to a moderator

Re: SQLLDR retaining DB column value [message #621589 is a reply to message #621572]

Thu, 14 August 2014 13:00

Member2014
Messages: 33
Registered: July 2014
Location: USA

Member

The problem I have is that incoming file had 20 columns and the database table has 22 columns. I was looking for an option to load all columns to the database table and RETAIN the values of the last two columns.
The TRAILING NULLCOLS option overwrites the value of the last two columns in the DB to be NULL, which was my problem.

I found the solution. There is an option "field list" in sqlldr using which we can specify the column list in the DB to which the data will be inserted into.

Report message to a moderator

Re: SQLLDR retaining DB column value [message #621592 is a reply to message #621589]

Thu, 14 August 2014 13:08

Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA

Senior Member

I think you are confused. SQL*Loader does not update existing rows. All it does is load. It can append new rows to table or empty the table (replace/truncate) and load rows.

SY.

[Updated on: Thu, 14 August 2014 13:08]

Report message to a moderator

Re: SQLLDR retaining DB column value [message #624170 is a reply to message #621592]

Thu, 18 September 2014 12:28

Member2014
Messages: 33
Registered: July 2014
Location: USA

Member

Thanks BlackSwan, joy_division, Littlefoot and Solomon Yakobson for your suggestions. Apologies for the late response.

SY- you are right, we can only do a truncate or append when using SQLLDR and not update. We ended up using transient tables to load data and then merge the data to the actual production tables.
This way data was retained in case incoming values were null.

Report message to a moderator

Re: SQLLDR retaining DB column value [message #624188 is a reply to message #624170]

Thu, 18 September 2014 14:08

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

I have a thought, as you want the two computed columns to automatically get populated based on the values of the other columns, why not have them as VIRTUAL COLUMNS. They are meant for computed values after all.

Report message to a moderator

Re: SQLLDR retaining DB column value [message #624190 is a reply to message #624188]

Thu, 18 September 2014 14:12

Member2014
Messages: 33
Registered: July 2014
Location: USA

Member

Hmm, I have used virtual columns in DB tables. Can we use virtual columns in SQLLDR definition as well?

Report message to a moderator

Re: SQLLDR retaining DB column value [message #624192 is a reply to message #624190]

Thu, 18 September 2014 14:16

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You don't need it, just define the columns that are in the file and the 2 last ones will automatically be there in the table.
It is not necessary you name ALL the columns of the table in the control file, you just need to name the columns that are in the file.

Report message to a moderator

Re: SQLLDR retaining DB column value [message #624193 is a reply to message #624192]

Thu, 18 September 2014 14:17

Member2014
Messages: 33
Registered: July 2014
Location: USA

Member

Oh ok. I will try that out. Thanks!

Report message to a moderator

Re: SQLLDR retaining DB column value [message #624571 is a reply to message #624193]

Tue, 23 September 2014 10:01

Member2014
Messages: 33
Registered: July 2014
Location: USA

Member

Thanks Lalith and Michel. Adding virtual columns reduced the code and we did not need the stage tables. This was a big help!

Report message to a moderator

Re: SQLLDR retaining DB column value [message #624572 is a reply to message #624571]

Tue, 23 September 2014 10:03

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks to let us know.

Report message to a moderator

Re: SQLLDR retaining DB column value [message #624575 is a reply to message #624571]

Tue, 23 September 2014 10:52

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

I too thank you for the feedback. But my name doesn't have a trailing "h" Smile

Smile

It's Lalit, not Lalith. Usually people from Southern part of India tend to add a trailing "h" in the first name Smile

Smile

Anyway, regarding the virtual column concept, you might also have to look into performance aspects. But, now I would leave it up to you to figure it out(only if required). Else, you can come back again.

All the best!

[Updated on: Tue, 23 September 2014 13:16]

Report message to a moderator

Re: SQLLDR retaining DB column value [message #624577 is a reply to message #624575]

Tue, 23 September 2014 11:58

Member2014
Messages: 33
Registered: July 2014
Location: USA

Member

Sry about that Lalit.. !! Smile

Smile

Report message to a moderator

Previous Topic:	Import error ORA-31694
Next Topic:	expdp ora-39127 lbacsys missing

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 16:50:09 CST 2025