OraFAQ Forum: Server Utilities » How to handle Dynamic Filename with a single control file

Home » RDBMS Server » Server Utilities » How to handle Dynamic Filename with a single control file (oracle 9i)

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

How to handle Dynamic Filename with a single control file [message #434792]

Thu, 10 December 2009 15:20

tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia

Member

Hi,

Data file name will be dynamic,but data will be same and same control file to be used for loading.

Is that possible the single ctl file can accept the dynamic file name and filename is not known till the time of file generation but filename pattern is known?

Please suggest.

Report message to a moderator

Re: How to handle Dynamic Filename with a single control file [message #434794 is a reply to message #434792]

Thu, 10 December 2009 15:41

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

Senior Member
Account Moderator

One option might be renaming the file; for example, if control file contains "INFILE 'my_input_file.csv'" and you have created a file 'my_new_file_2009_12_10.csv', then first rename it to 'my_input_file.csv' and run SQL*Loader.

Or, pass input file name directly into the SQLLDR command line (something like

SQLLDR control=my_control.ctl DATA=my_new_file_2009_12_10.csv ...

(for more information, check the documentation).

Report message to a moderator

Re: How to handle Dynamic Filename with a single control file [message #434796 is a reply to message #434794]

Thu, 10 December 2009 15:49

tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia

Member

Hi,

Thanks for the options.

Is there anyway that control file will accept the dynamic filename, instead of changing filname & than passing the static filename as define in the control name?

Report message to a moderator

Re: How to handle Dynamic Filename with a single control file [message #434798 is a reply to message #434796]

Thu, 10 December 2009 15:53

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

Senior Member
Account Moderator

No, as far as I can tell (unless you dynamically create the control file itself).

Report message to a moderator

Re: How to handle Dynamic Filename with a single control file [message #434799 is a reply to message #434796]

Thu, 10 December 2009 16:19

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

As Littlefoot already said,
you can easily recreate the controlfile from the scratch with some scripting.
Ofcourse the "script" depends on your OS.

>>filename is not known till the time of file generation
Not a problem. Just let the file be generated in a specific directory.
Following is a simple script that will read all files in directory and load one by one. You can make it fancier by loading and moving/renaming the files or whatever.
http://www.orafaq.com/forum/m/74127/42800/?srch=sqlldr+dynamic#msg_74127

Report message to a moderator

Re: How to handle Dynamic Filename with a single control file [message #434835 is a reply to message #434799]

Fri, 11 December 2009 03:12

tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia

Member

Hi,

Thanks to all for the options.

Report message to a moderator

Previous Topic:	Export Error
Next Topic:	EXPORT taking more time

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 29 09:12:21 CDT 2024