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

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

Create a Backup Script

This script will create a DOS batch file that copies data files, control files, log files, and the .ora files. Figure 1 shows the instructions displayed at the SQL prompt after BKU.SQL is run. Figure 2 shows the directory created, and the DOS batch file in it. Figure 3 shows a sample DOS batch file.

Define OnOff = OFF
rem +------------------------------------------------------------------+
rem | Script ID:    bku.sql                                         1.1
rem |
rem | Purpose:      Backup up important files on the database
rem |        
rem | Developer:    Lynn Tobias
rem | Script Date:  7/30/2008
rem |
rem | Input File(s): none
rem |
rem | Table(s) Used: Dual, Dba_Data_Files, V$Logfile, V$Controlfile
rem |         
rem | Called by:     n/a
rem |
rem | Calls:         MkDirBat.Bat     (DOS) Checks directory created
rem |                BkUp-&Today..Bat (DOS) the batch file with items to copy
rem |
rem | Variables:     xFile    - the name of the group of files used as
rem |                           documentation in the dos batch file
rem |                BkUp_Dir - the backup directory entered
rem |                Today    - date to put in the batch file
rem |                OnOff    - set up for debuggin purposes.  When you want to
rem |                           see what's happening, set to ON
rem |                CopyName - Alias for any filename retrieved. Set once to A60
rem |
rem | Output:        MkDirBat.Bat
rem |
rem | Revisions: Dvl Date   Ver Comment/Change
rem |            --- ------ --- ----------------------------------+
rem |            lct  080808 1.1 - Changed _Date to &Today
rem |                            - stop script after batch file created
rem |                            - get directory name before creating batch file
rem |                            - added instructions at end
rem |                            - combined xcopy for all *.ora files
rem +------------------------------------------------------------------+

Clear Screen
Column CopyName Format A60

Ttitle 'REM '                                                            skip 1-
       'REM ***********************************************************' skip 1-
       'REM * ' xFile                                                    skip 1-
       'REM ***********************************************************' skip 1-
       'REM '

--+- 1 -----------------------------------------------------------------
--| Write MkDirBat.Bat. Couldn't figure how else to get it to pause so we
--| had a chance to see if the 'md' worked. (MD doesn't work it if already
--| exists -- some cleanup might need to be done.)
--+---------------------------------------------------------------------
Set Term On
Accept Bkup_Dir Prompt 'Enter Backup directory (i.e., d:\ or c:\bkup): '

Set Echo &OnOff Term &OnOff Pagesize 0 Verify &OnOff Feedback &OnOff

Spool mkdirbat.bat Replace
Select 'md ' || '&Bkup_Dir'
             ||  chr(10)
             || 'pause'
From Dual;
Spool Off

--+- 2 -----------------------------------------------------------------
--| Run the batch file just created to show status on the make directory.
--| This will display an error in the Dos window if this directory exists.
--+---------------------------------------------------------------------

Host MkDirBat

--+- 3 -----------------------------------------------------------------
--| Get todays date for the batch file name. Start spooling.
--+---------------------------------------------------------------------

Set Term Off Pagesize 9999 Heading Off

Column Today New_Value Today
Select To_Char(Sysdate,'mmddyy') Today
From Dual;

Spool BkUp-&Today..Bat

--+- 4 -----------------------------------------------------------------
--| Start writing out the file names to be copied: Data Files
--+---------------------------------------------------------------------

Define xFile = 'Data Files'

Select 'Copy',
       File_Name  As CopyName,
       '&Bkup_Dir'
From Dba_Data_Files;

--+- 5 -----------------------------------------------------------------
--| Log Files
--+---------------------------------------------------------------------

Define xFile = 'Redo Log Files'

Select 'Copy',
       Member     As CopyName,
       '&Bkup_Dir'
From V$Logfile;

--+- 6 -----------------------------------------------------------------
--| Control Files
--+---------------------------------------------------------------------

Define xFile = 'Control Files'

Select 'Copy',
        Name       As CopyName,
       '&Bkup_Dir'
From V$Controlfile;

--+- 7 -----------------------------------------------------------------
--| Copy the *.ORA files: Password (PWD), Init, SPFile, TNSNames
--| (Done this way because there can be more than one with the same name.
--+  Xcopy saves under the various directories so nothing gets overwritten.)
--+---------------------------------------------------------------------

Define xFile = '*.ORA'

Select 'xcopy c:\oracle\*.ora /s &Bkup_Dir'
from dual;

--+- 8 -----------------------------------------------------------------
--| Spool off and further instructions
--+---------------------------------------------------------------------

Spool Off

Host move BkUp-&Today..Bat &Bkup_Dir

Set Term On
Prompt
Prompt +--------------------------------------------------------------------------+
Prompt | Your DOS Batch File has been moved the backup directory                  |
Prompt | To continue with process:                                                |
Prompt +--------------------------------------------------------------------------+
Prompt | 1) Conn sys/sys as sysdba                                                |
Prompt | 2) SHUTDOWN IMMEDIATE                                                    |
Prompt | 3) Open a DOS window                                                     |
Prompt | 4) Change to directory specified in script with (cd &Bkup_Dir)    
Prompt | 5) Run batch file. Type the first portion of the file name (BkUp-&Today) |
Prompt | 6) Have a good night! ;-)                                                |
Prompt +--------------------------------------------------------------------------+
Prompt

--+- 9 -----------------------------------------------------------------
--| Clean up
--+---------------------------------------------------------------------

Undefine xFile BkUp_Dir Today
Ttitle Off
Set Heading On Feedback On Verify On
host del MkDirBat.Bat