run batch file from windows to do sql jobs

[sameer11]

I have a .dat file and the data from the file is loading into few tables and there are few .sql file to be run to update or merge data into few other tables.

How can I use a batch file to run the batch script from window and not save or hard code the user/password in the batch file?

Can I login once and do the jo if I have 2 .dat files?




Thanks in Advance,

Sameer

 

[Welshbird]

I do a similar thing using this:

@echo off
title Load Ad-Hoc into Oracle
cd \
cd adhoc

REM  First the Ad-Hoc zip file has to be copied to:
REM  c:\adhoc overwriting the original file
REM
unzip -o -q c:\adhoc\AH__0031_0001.zip
REM
REM  That bit is done then....
REM
REM  Now to try and load the files into the load
REM  Tables in ORACLE
sqlldr user/password@instance control = c:\adhoc\ctl\acomp1.ctl 
sqlldr user/password@instance control = c:\adhoc\ctl\acomp2.ctl 
sqlldr user/password@instance control = c:\adhoc\ctl\acomp3.ctl 
sqlldr user/password@instance control = c:\adhoc\ctl\acomp4.ctl 
sqlldr user/password@instance control = c:\adhoc\ctl\acomp5.ctl 
sqlldr user/password@instance control = c:\adhoc\ctl\dat10.ctl 
sqlldr user/password@instance control = c:\adhoc\ctl\dat11.ctl  
sqlldr user/password@instance control = c:\adhoc\ctl\dat12.ctl  
REM
REM  Now to make these into one table with the correct codes
cd ctl
sqlplus user/password@instance @make_table.sql
cd ..

And I know there is a way of hiding the user and password - but I'm not sure how! MAybe someone else can help with that bit.

Hope this bit helps at least.



Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Welshbird]

Oh, and CTL files look like this

        load data
          INFILE 'C:\cpld\dat_mth0611_F24' 
          INFILE 'C:\cpld\dat_mth0611_F23' 
          INFILE 'C:\cpld\dat_mth0611_F22' 
          INFILE 'C:\cpld\dat_mth0611_F21' 
          INFILE 'C:\cpld\dat_mth0611_F20' 
          INFILE 'C:\cpld\dat_mth0611_F19' 
          INFILE 'C:\cpld\dat_mth0611_F18' 
          INFILE 'C:\cpld\dat_mth0611_F17' 
          INFILE 'C:\cpld\dat_mth0611_F16' 
          INFILE 'C:\cpld\dat_mth0611_F15' 
          INFILE 'C:\cpld\dat_mth0611_F14' 
          INFILE 'C:\cpld\dat_mth0611_F13' 
          INFILE 'C:\cpld\dat_mth0611_F12' 
          INFILE 'C:\cpld\dat_mth0611_F11' 
          INFILE 'C:\cpld\dat_mth0611_F10' 
          INFILE 'C:\cpld\dat_mth0611_F09' 
          INFILE 'C:\cpld\dat_mth0611_F08' 
          INFILE 'C:\cpld\dat_mth0611_F07' 
          INFILE 'C:\cpld\dat_mth0611_F06' 
          INFILE 'C:\cpld\dat_mth0611_F05' 
          INFILE 'C:\cpld\dat_mth0611_F04' 
          INFILE 'C:\cpld\dat_mth0611_F03' 
          INFILE 'C:\cpld\dat_mth0611_F02' 
          INFILE 'C:\cpld\dat_mth0611_F01' 
          TRUNCATE
          into table CPLD_FACT
          FIELDS TERMINATED BY ';' 
          OPTIONALLY  ENCLOSED BY '"' 
          TRAILING NULLCOLS
         		(Month_Num,
         		 prod_code,
         		 Inst,  
         		 Unit,          
						 valued,       
	           Date_Loaded sysdate)

if you are loading more than one flat file into a table

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[slaing]

Sameer -

Here's a very simple suggestion - if it's not the sort of thing you're looking for then please post a few more specific questions.

As you've mentioned 'window' and 'batch' then I'll assume for now you're using a DOS script file.

You can pass command line parameters to DOS scripts using %1, %2 etc, so your script file sameer.cmd might look like

sqlplus %1/%2@MYDB @c:\myscript1.sql
sqlplus %1/%2@MYDB @c:\myscript2.sql

and you could call it from DOS (or Windows Scheduler)with

C:>sameer.cmd scott tiger

(add in your own sqlldr commands for loading the .dat files)

If using Windows scheduler then obviously your password is being stored, but it should be easier to secure than having it stored in a script file.


Steve

 

[slaing]

Another thought !

If the objective is just to avoid having passwords stored where they might be seen, you could use OS Authentication in Oracle and avoid the situation completely.

Briefly (in case you haven't come across these) -

If your windows (of unix) username is sameer, you create a login in Oracle called OPS$SAMEER with the 'IDENTIFIED EXTERNALLY' option. If you're logged on to the computer as sameer then you just use

set oracle_sid=MYDB
sqlplus / @myscript.sql

(OPS$ is the default value for the OS_AUTHENT_PREFIX system parameter)


Steve

 

[Welshbird]

http://www.jlcomp.demon.co.uk/faq/hide_password.html

This article might be useful to you too.

Let us know how this helps, or if you need more.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]