Solved

How do you execute a SQLPLUS batch file?

Posted on 2008-06-25
15
8,594 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
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 22

Expert Comment

by:DrSQL
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:Mark Geerlings
Comment Utility
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
 

Author Comment

by:missymadi
Comment Utility
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 75 total points
Comment Utility
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 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 50 total points
Comment Utility
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 34

Expert Comment

by:Mark Geerlings
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 22

Expert Comment

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

Author Comment

by:missymadi
Comment Utility
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 34

Expert Comment

by:Mark Geerlings
Comment Utility
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 34

Expert Comment

by:Mark Geerlings
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:Mark Geerlings
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to take different types of Oracle backups using RMAN.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

771 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now