How do you execute a SQLPLUS batch file?

missymadi,
    You would include the SID in your logon.  You can use separate sqlplus scripts or you can use the CONNECT command to switch databases within a single script:

sqlplus SYSTEM/password@TESTSID @script1.sql
sqlplus SYSTEM/password@ANOTHERSID @script2.sql
sqlplus SYSTEM/password@TESTSID @script3.sql

or, within the original script (see cconnect, below):
Drop user SCOTT cascade;
/
Drop user TSMSYS cascade;
/
connect system/password@THEOTHERSID
Alter PROFILE default LIMIT
Password_Reuse_time 365
Password_Reuse_max 10;
/
Revoke execute on UTL_FILE from Public;
Revoke execute on UTL_TCP from Public;
Revoke execute on UTL_SMTP from Public;

Good luck!

I believe you want to have a command like sqlplus user/password@SID @path and filename of script in your batch file.

In the script you call from the batch file (the file after the 2nd @ in the above command line) start it with:
run
{
command
command
}

Since you said "batch file" I assume your server O/S is Windows, not UNIX or Linux.  For Oracle on Windows, I find it easiest to do this with two (or more) separate files:
1. the batch file that has the O/S commands and calls SQL*Plus
2. a *.SQL file that has the Oracle commands (these can include the username, password and tns alias for the Oracle login or you can put the username, password and tns alias into the batch file line that calls SQL*Plus.

The line in your batch file that calls SQL*Plus must pass the name of the *.SQL file to run and this must be preceeded by the "@" character, as in the examples from DrSQL  (Note that this is separate from the "@"character that preceedes the tns alias.)

The last line in the *.SQL file should be:
exit;

to return control to the batch file, so if you have additional commands in the batch file they will get executed.

Yes this is a Windows XP box.

I now have the following in my batch file.
CD..
CD..
CD..
CD..
sqlplus SYSTEM/test@testSID@C:\IASCRIPTS\@CDSPROD.sql

RUN
/V0002541
{
Alter Profile default LIMIT
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 10;
}
/
{
Alter Profile Monitoring_Profile LIMIT
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 10;
}
/
/V0002539
{
Revoke execute on UTL_FILE from Public
Revoke execute on UTL_TCP from Public
Revoke execute on UTL_HTTP from Public
Revoke execute on UTL_SMTP from Public
Revoke execute on DBMS_LOB from Public
Revoke execute on DBMS_SQL from Public
Revoke execute on DBMS_OBFUSCATION_TOOLKIT from Public
}
/
/V0002529
{
Alter user CTXSYS identified by test account lock password expire;
Alter user DIP identified by test account lock password expire;
Alter user DMSYS identified by test account lock password expire;
Alter user EXFSYS identified by test account lock password expire;
Alter user MDDATA identified by test account lock password expire;
Alter user MDSYS identified by test account lock password expire;
Alter user OLAPSYS identified by test account lock password expire;
Alter user ORDPLUGINS identified by test account lock password expire;
Alter user ORDSYS identified by test account lock password expire;
Alter user OUTLN identified by test account lock password expire;
Alter user SCOTT identified by test account lock password expire;
Alter user WMSYS identified by test account lock password expire;
Alter user XDB identified by test account lock password expire;
}
/
/V0003444
{
DROP USER SCOTT CASCADE;
DROP USER ANONOMOUS CASCADE;
}
/
/V0003439
{
Revoke create job FROM OEM_Advisor;
Revoke create role, create view, alter, unlimited tablespace FROM Repuser;
Revoke unlimited tablespace FROM TSMSYS;
Revoke administrator SQL T FROM OEM_ADVISOR;
}
/
/V0002553
{
Alter profile MONITORING_PROFILE LIMIT
Failed_login_attempts 3;
}
/
{
Alter profile MONITORING_PROFILE LIMIT
Idle_time 15;
}
/
/V0003865
{
Alter system set global_names=true;
}
 
{
EXIT
}

Select allOpen in new window

I'm not trying to restate DrSQL's work, he just beat me to it and posted his while I was composing mine.  I didn't see his latest comment before I posted my latest one (prior to this).

It's ok, I missed the triple "@".

Hello DRSQL,

       Yes, I was trying to comment out with /.  Thanks for the correction!

        Could you elaborate on "At the beginning of your script there IS no SQL in the buffer (unless you have a login.sql file)".   -just want to understand what this means.......

         At this point I am trying to get at least one script to run against the Oracle db. Then I'll add the other SIDs.

       

To get one script running. make it a very simple script.  Then add complexity later after you succeed in calling SQL*Plus from a batch file, logging in to Oracle, and running a small *.sql file.

"At the beginning of your script there IS no SQL in the buffer (unless you have a login.sql file)"  This just means that the SQL buffer (in SQL*Plus) is empty when your script starts.  If you run SQL*Plus interactively instead, you usually do have a SQL command in the buffer that you can re-execute if you want to.

I'm just thinking abiout your question title a bit more, where you say "execute a SQLPLUS batch file".  But those are two different things.  The Windows O/S can execute batch files (like UNIX or LInux can execute shell script files) but SQL*Plus executes *.SQL files that contain SQL, SQL*Plus and/or PL/SQL commands, not O/S commands.

That's why I find it simplest to keep the O/S commands in a batch file, and keep the SQL commands in a *.SQL file.

In UNIX or Linux it is a bit easier to embed SQL commands in a shell script and have the O/S execute just one file with both O/S and Oracle commands.  It is possible to do this WIndows also, but I find it much harder to test and debug that way.

missymadi,
    When you start sqlplus it checks for the existence of two files - a login.sql and a glogin.sql.  These contain sqlplus and sql commands that will customize your sqlplus environment.  A popular login.sql is:

set timing on
set pagesize 20
column dbname new_value dbname
select name dbname from v$database;
set sqlprompt "&&dbname> "


Which would leave the "select name dbname from v$database" in the sqlbuffer and your RUN command would re-execute that.

Good luck!

Thanks for the information.

I have a file that I need to delete on the OS. Can I do this using SQLPLUS? Or do I have to return back to the batch file via EXIT and delete a file?

missymadi,
    No, you can perform O/S command from within sqlplus scripts.  This is done with the HOST command:

HOST del c:\bad\tempfile.dat

Goos luck!

Yes, you can use the "host" command interactively in SQL*PLUS (or in a *.SQL script that SQL*Plus is running) to do operating system tasks.  Just keep in mind when you do that, that SQL*Plus will get a "success" indicator back from the O/S regardless of whether what you attempt to do in the O/S actually succeeds or fails.

That's another reason I like separate files,  to keep O/S commands in an O/S script file (a batch file on Windows) and SQL commands in a *.SQL file.