Home » RDBMS Server » Server Utilities » SQLLDR retaining DB column value (Oracle 11)
SQLLDR retaining DB column value [message #621572] Thu, 14 August 2014 08:15 Go to next message
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.
Re: SQLLDR retaining DB column value [message #621573 is a reply to message #621572] Thu, 14 August 2014 08:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://docs.oracle.com/database/121/SQLRF/statements_7002.htm#SQLRF54465
Re: SQLLDR retaining DB column value [message #621586 is a reply to message #621572] Thu, 14 August 2014 12:37 Go to previous messageGo to next message
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.
Re: SQLLDR retaining DB column value [message #621587 is a reply to message #621586] Thu, 14 August 2014 12:53 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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.
Re: SQLLDR retaining DB column value [message #621589 is a reply to message #621572] Thu, 14 August 2014 13:00 Go to previous messageGo to next message
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.
Re: SQLLDR retaining DB column value [message #621592 is a reply to message #621589] Thu, 14 August 2014 13:08 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 Go to previous messageGo to next message
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.
Re: SQLLDR retaining DB column value [message #624188 is a reply to message #624170] Thu, 18 September 2014 14:08 Go to previous messageGo to next message
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.
Re: SQLLDR retaining DB column value [message #624190 is a reply to message #624188] Thu, 18 September 2014 14:12 Go to previous messageGo to next message
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?
Re: SQLLDR retaining DB column value [message #624192 is a reply to message #624190] Thu, 18 September 2014 14:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: SQLLDR retaining DB column value [message #624193 is a reply to message #624192] Thu, 18 September 2014 14:17 Go to previous messageGo to next message
Member2014
Messages: 33
Registered: July 2014
Location: USA
Member
Oh ok. I will try that out. Thanks!
Re: SQLLDR retaining DB column value [message #624571 is a reply to message #624193] Tue, 23 September 2014 10:01 Go to previous messageGo to next message
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!
Re: SQLLDR retaining DB column value [message #624572 is a reply to message #624571] Tue, 23 September 2014 10:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks to let us know.

Re: SQLLDR retaining DB column value [message #624575 is a reply to message #624571] Tue, 23 September 2014 10:52 Go to previous messageGo to next message
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 It's Lalit, not Lalith. Usually people from Southern part of India tend to add a trailing "h" in the first name 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 Go to previous message
Member2014
Messages: 33
Registered: July 2014
Location: USA
Member
Sry about that Lalit.. !! Smile
Previous Topic: Import error ORA-31694
Next Topic: expdp ora-39127 lbacsys missing
Goto Forum:
  


Current Time: Thu Mar 28 17:32:58 CDT 2024