On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Sunday, August 24, 2008

To Run a SQL Query From a DOS Batch File


This is one way. I haven't investigated doing the whole thing in one script. Also need to determine how to send the password into this rather than have it as part of the script.

The DOS batch file calls sqlplus and has the user/password, and the SQL script to be executed. Afte the SQL script has exited, this will clear the screen and type out the report from the spooled file:
@Echo Off
sqlplus scott/tiger @C:\emptest.sql
cls
type c:\emptest.txt



The SQL Script looks normal, but must have 'EXIT' at the end:
Set Linesize 200 Pagesize 50 Feedback Off  Trimspool On
Ttitle Left 'Employee Data Query Run From DOS Batch File' Skip 2

Spool C:\Emptest.Txt
Select Empno, Ename, Deptno
From Emp
Where DeptNo = 20;
Spool Off

Exit

No comments:

Post a Comment