OraFAQ Forum: Server Utilities » how to call control file

Home » RDBMS Server » Server Utilities » how to call control file

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

how to call control file [message #181993]

Wed, 12 July 2006 09:04

mape
Messages: 298
Registered: July 2006
Location: Slovakia

Senior Member

Hi

From this line I call control file load.ctl ->
sqlldr userid=xx/xx@yy control=load.ctl log=xx.log

and load.ctl looks here

LOAD DATA
INFILE '$2_$3_file.txt'
APPEND
INTO TABLE MAPE_$2_$3
FIELDS TERMINATED BY " " OPTIONALLY ENCLOSED BY '"'
(STH)

where $2 and $3 should be a parameters, for exmaple numbers.
But I dont know how to do it?

Thanks for help

[Updated on: Wed, 12 July 2006 09:22]

Report message to a moderator

Re: how to call control file [message #182343 is a reply to message #181993]

Fri, 14 July 2006 07:35

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

Senior Member
Account Moderator

if is just the the input datafile, you can load it dynamically
using commad line
sqlldr userid=scott/tiger control=myctl.ctl data=mydatafile.csv

But to pass the table_name as parameter,
>>INTO TABLE MAPE_$2_$3
you need to rebuild the controlfile every time(search the forum. There are numerous examples to do this in a script).
Or
have a standard table MAPE_STAGING.
Always load into this table.
Then transfer the data to the table you want to.

Report message to a moderator

Re: how to call control file [message #182377 is a reply to message #182343]

Fri, 14 July 2006 08:35

mape
Messages: 298
Registered: July 2006
Location: Slovakia

Senior Member

Hi

Soryy, but I cannot find the examples how to rebuild the controlfile every time when is call it?

Thanks

Report message to a moderator

Re: how to call control file [message #182382 is a reply to message #182377]

Fri, 14 July 2006 08:46

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

Senior Member
Account Moderator

Several examples (for different OS, scripting tools) are available.
http://www.orafaq.com/forum/m/73220/42800/?srch=sqlldr+table+name+as+input#msg_73220

Concept is simple.
Have a template control file (template.ctl).
Everytime you load data,
edit the template file, change the table_name value, save as another control file (leave the template as is).
load this new control file

Report message to a moderator

Re: how to call control file [message #182383 is a reply to message #182382]

Fri, 14 July 2006 08:48

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

Senior Member
Account Moderator

The same concept is used to dynaically pass the name of table, input_file_name as a record in table or whatever.
All you need is a template file.
copy the template to workingfile. Replace the values you want.
Load the working file. Discard the working file.

Report message to a moderator

Previous Topic:	Control file fields interchange
Next Topic:	"WWUTL_API_SecurityTransport" abnormality

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jul 04 19:28:51 CDT 2024