?
Solved

How do you execute a SQLPLUS batch file?

Posted on 2008-06-25
15
Medium Priority
?
8,674 Views
Last Modified: 2008-08-27
Hi,

      I have a batch file that I want to connect to a specific database and run administrative sql statements.
I tried running the following script but only get to the SQL> prompt. How do I
Connect to a SID
Run some admin commands
Connect to another SID
Run some admin commands
Code is below....


CD..
CD..
CD..
CD..
Set Oracle_SID=TESTSID
SQLPLUS SYSTEM/password
Drop user SCOTT cascade;
/
Drop user TSMSYS cascade;
/
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;

Open in new window

0
Comment
Question by:missymadi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 22

Expert Comment

by:DrSQL
ID: 21866424
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!
0
 

Expert Comment

by:Winelover
ID: 21867392
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
}

0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 21867605
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.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:missymadi
ID: 21867736
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
}

Open in new window

0
 
LVL 22

Accepted Solution

by:
DrSQL earned 300 total points
ID: 21867831
missymadi,
     I'm not sure what you think the "/" character does, nor the "{}".  Comments are started with a REMARK command, with "--" or with "/*" to start them and "*/" to end them.  Also, there's no need for a RUN statement - that actually runs the SQL statement that's in the buffer.  The "/", by itself, also runs the current SQL - not what you probably intended.  At the beginning of your script there IS no SQL in the buffer (unless you have a login.sql file).  You also missed some semicolons (on the revokes).  Finally, I don't see anywhere that you changed to another database - wasn't that your original question?  I'd suggest you might want this:

-- 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

Open in new window

0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 200 total points
ID: 21867865
Not quite.  

1. You don't need three "@" characters in the line that calls SQL*Plus, just two, like this:
sqlplus SYSTEM/test@testSID @C:\IASCRIPTS\CDSPROD.sql

2.  You need a space after the end of the tns alias before the "@" character that preceeds your *.SQL file name (and the full path if you aren't already in the directory where the *.SQL file is).

3. You don't need the word "run" in your *.sql file.

4. What are the "/V0..." lines intended to do?  That is not valid SQL syntax.

5. You don't need the { characters.  They can be used in Oracle RMAN scripts, but not in SQL*Plus scripts.

6. You do need a semi-colon ";" at the end of each SQL command, like this:
Revoke execute on UTL_FILE from Public;
Revoke execute on UTL_TCP from Public;
Revoke execute on UTL_HTTP from Public;


0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 21867885
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).
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 21867896
It's ok, I missed the triple "@".
0
 

Author Comment

by:missymadi
ID: 21867955
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.

       
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 21868023
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.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 21868069
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.
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 21868087
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!
0
 

Author Comment

by:missymadi
ID: 21868830
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?
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 21868846
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!
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 21869440
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question