Home » RDBMS Server » Server Utilities » How to handle Dynamic Filename with a single control file (oracle 9i)
How to handle Dynamic Filename with a single control file [message #434792] Thu, 10 December 2009 15:20 Go to next message
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.
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 Go to previous messageGo to next message
Littlefoot
Messages: 20895
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).
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 Go to previous messageGo to next message
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?
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 Go to previous messageGo to next message
Littlefoot
Messages: 20895
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).
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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
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 Go to previous message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi,

Thanks to all for the options.
Previous Topic: Export Error
Next Topic: EXPORT taking more time
Goto Forum:
  


Current Time: Mon Dec 05 18:57:54 CST 2016

Total time taken to generate the page: 0.23961 seconds