Home » RDBMS Server » Server Utilities » sql loader help (oracle 10g,9i,8i solaris)
sql loader help [message #517080] Fri, 22 July 2011 06:47 Go to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Hi,

I have 780(12*65) csv files generated from 65 databases.

Now I have to load this 780 csv files into 12 tables created in my database for some monitoring and reporting purpose.

to call the sql loader I am plannig to create 780 lines like below.

sqlldr abc@tns/pwd control='E:\html\ctl\html_broken_jobs_rpt.ctl' log='E:\htmlreport\log\html_broken_jobs_rpt.log'
sqlldr abc@tns/pwd control='E:\html\ctl\html_db_size_rpt.ctl' log='E:\htmlreport\log\html_db_size_rpt.log'
sqlldr abc@tns/pwd control='E:\html\ctl\html_fragmentation_rpt.ctl' log='E:\htmlreport\log\html_fragmentation_rpt.log'
sqlldr abc@tns/pwd control='E:\html\ctl\html_index_stats_rpt.ctl' log='E:\htmlreport\log\html_index_stats_rpt.log'
sqlldr abc@tns/pwd control='E:\html\ctl\html_invalid_object_rpt.ctl' log='E:\htmlreport\log\html_invalid_object_rpt.log'
sqlldr abc@tns/pwd control='E:\html\ctl\html_long_running_queries_rpt.ctl' log='E:\htmlreport\log\html_long_running_queries_rpt.log'
.
.
.


we know creating 780 control files is the difficult task.

So I have created only 12 control files. is there any mechanism to pass a varible (planning to declare it in the sqlldr line) to the infile clause like below in sql loader?

infile "E:\htmlreport\output\&a_html_broken_jobs_rpt.csv"

here a is the variable name. it will change every 12 csv files once.


or

is there anyother way to achive this?

[Updated on: Fri, 22 July 2011 06:51]

Report message to a moderator

Re: sql loader help [message #517081 is a reply to message #517080] Fri, 22 July 2011 06:50 Go to previous messageGo to next message
Littlefoot
Messages: 19636
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As far as I can tell, SQL*Loader's control file can't accept input (CSV) file name as a parameter. What people do is to dynamically create control file (and include different CSV file names into every control file).
Re: sql loader help [message #517084 is a reply to message #517080] Fri, 22 July 2011 06:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59196
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If the control and data files for the same table are the same ones (but the data themselves)
then you need only 1 control file per table and pass the name of the data file on the command
line and not inside the control file.

Regards
Michel
Re: sql loader help [message #517096 is a reply to message #517084] Fri, 22 July 2011 08:14 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
The below csv files created for 1 database and this needs to loaded in the below tables mentioned. so everytime "APP0111_1_SAS" only will change in the csv file name for every database. also data in the csv file will not be similar. Now can I pass it through command line?

Table names:
============

html_tablespace_rpt
html_maxextent_rpt
html_table_stats_rpt
html_index_stats_rpt
html_fragmentation_rpt
html_tab_part_frag_rpt
html_db_size_rpt
html_schema_size_rpt
html_long_running_queries_rpt
html_long_running_sessions_rpt
html_invalid_object_rpt
html_broken_jobs_rpt


CSV file names:
=============== 

APP0111_1_SAS_html_tablespace_rpt
APP0111_1_SAS_html_maxextent_rpt
APP0111_1_SAS_html_table_stats_rpt
APP0111_1_SAS_html_index_stats_rpt
APP0111_1_SAS_html_fragmentation_rpt
APP0111_1_SAS_html_tab_part_frag_rpt
APP0111_1_SAS_html_db_size_rpt
APP0111_1_SAS_html_schema_size_rpt
APP0111_1_SAS_html_long_running_queries_rpt
APP0111_1_SAS_html_long_running_sessions_rpt
APP0111_1_SAS_html_invalid_object_rpt
APP0111_1_SAS_html_broken_jobs_rpt


Re: sql loader help [message #517097 is a reply to message #517096] Fri, 22 July 2011 08:21 Go to previous messageGo to next message
Michel Cadot
Messages: 59196
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sqlldr ... data=...

Regards
Michel
Re: sql loader help [message #517100 is a reply to message #517097] Fri, 22 July 2011 08:42 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Quote:
sqlldr ... data=...


I can't get you. can you pls give me some more detail? I want to load these 12 csv files into 12 tables . what I need to specify in the column to accept a variable that will change everytime?
Re: sql loader help [message #517104 is a reply to message #517100] Fri, 22 July 2011 10:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7988
Registered: November 2002
Location: California, USA
Senior Member
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1057632370697
Re: sql loader help [message #517129 is a reply to message #517104] Fri, 22 July 2011 14:33 Go to previous messageGo to next message
Littlefoot
Messages: 19636
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
DATA?!?
I'd swear that I never, never heard of it ./fa/1606/0/ . Where was I living all this years? ./fa/1604/0/

Thank you for opening my eyes (as they were, obviously, wide shut).
Re: sql loader help [message #517136 is a reply to message #517129] Fri, 22 July 2011 15:19 Go to previous message
joy_division
Messages: 4515
Registered: February 2005
Location: East Coast USA
Senior Member
I've been using SQL*Loader for 20 years and I never heard of it either! Maybe it didn't exist in V5 when I started.
Previous Topic: "SYS.DBMS_EXPORT_EXTENSION" does not exist
Next Topic: exporting huge amount of data
Goto Forum:
  


Current Time: Tue Sep 23 09:35:26 CDT 2014

Total time taken to generate the page: 0.12097 seconds