Link to home
Start Free TrialLog in
Avatar of IT_ETL
IT_ETL

asked on

Run master SQLLDR scripts in UNIX environment

I got two SQLLDR scripts below. I will combine this two scripts and create a master SQLLDR script (master_script.ctl). The database server is in UNIX. All the source data files are in specified location below (inside scripts) in UNIX server. So, the master script needs to run at the UNIX command prompt and at the end of run data will be loaded into database tables TEST.TABLE_A & TEST.TABLE_B.

Do I need to write a shell script in order to execute this master_script.ctl?  Is there another option. Please provide an example.......

load data
infile '/u02/loaddata/natcity/V_STATUS_LOGIN_NCC.TXT'
replace into table TEST.TABLE_A
(
Login_ID                  position(1:9),
Password                  position(10:17),
Create_Date                  position(18:27)
);

load data
infile '/u02/loaddata/natcity/V_STATUS_HIST_NCC.TXT'
replace into table TEST.TABLE_B
(
Member_ID                        position(1:9),
Member_Status                  position(10:11),
Change_Method_Code                  position(12:15)
);

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

You can only use one CONTROL file at a time so you can't combine them

The unix script will then have 2 separate calls to sqlldr.

Based on the other question you have these would go below the 'here' script in the other question ( http://w.e-e.com/tdayN2 )


From both these questions, I would look at using Oracle External Tables.

http://www.orafaq.com/node/848
Avatar of IT_ETL

ASKER

I got multiple control files and sample data files in UNIX server. I need to write a shell script that will load data using SQLLDR into different tables in database. For example file1.ctl, file2.ctl, file3.ctl, file4.ctl, file5.ctl and so on. Could you give me an example of shell script?
I'm not sure I understand.

Just add 5 lines to a script file:

sqlldr user/password control=file1.ctl log=file1.log
sqlldr user/password control=file2.ctl log=file2.log
sqlldr user/password control=file3.ctl log=file3.log
sqlldr user/password control=file4.ctl log=file4.log
sqlldr user/password control=file5.ctl log=file5.log
Avatar of IT_ETL

ASKER

What part of the question you didn't understand? If you didn't understand then how did you answer the question.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of IT_ETL

ASKER

Your answer is correct. Thanks