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.

Friday, October 31, 2008

Run a SQL Query from DOS

This is a DOS batch file that will be run at the DOS prompt. You must supply the user, password, and the SQL query name you wish to run. For example:

Since the SQL query must have an 'EXIT' statement at the end, I've actually had this batch file call a two-line query that calls the query you requested, and then exits. This batch file assumes that all your code is in a directory called 'Data', and that the output of your query can be displayed in Notepad, and that the spool file name matches the query name with an 'TXT' extension.

SqlFromDos.Bat
Rem -+---------------------------------------
Rem -| Turn off the display
Rem -+---------------------------------------

@Echo Off

Rem -+---------------------------------------
Rem -| Pick up the variables typed
Rem -| i.e., SqlFromDos Scott Tiger Emptest
Rem -+---------------------------------------

Set User=%1
Set Pass=%2
Set Query=%3

Rem -+---------------------------------------
Rem -| Apply the variables to the login
Rem -+---------------------------------------

Sqlplus %User%/%Pass% @C:\Data\RunFromDos %Query%

Rem -+---------------------------------------
Rem -| When SQL*Plus is done, return to
Rem -| DOS, clear the screen, and open
Rem -| Notepad to view the report
Rem -+---------------------------------------

cls
Notepad C:\Data\%Query%.txt

This is called by SqlFromDos.Bat;


RunFromDos.Sql
@&1
Exit

This is called by RunFromDos.Sql:


EmpTest.Sql
Set Echo Off Linesize 200 Pagesize 60 Trimspool On
Spool Emptest.Txt
Select *
From Emp;
Spool Off

No comments:

Post a Comment