Schedule query execution vie SQL*PLUS 2

[Valeriya]

Hi all!

I have a basic question. I have a query that takes two dates as parameters that needs to be scheduled vie SQL*Plus. What is the command that I would need to use? What are the steps that I would need to take? Basically, I need the output to be spooled to an Excel file (or csv, if Excel is not an option). I've never used SQL *Plus so I'm a bit confused here.

Thank you very much!

Valeriya

 

[Thargy]

Val,

welcome to the oracle corner of Tek-Tips.
The short answer is "you can't achieve what you want with sqlplus". However, all is not lost.

First of all, sqlplus is a scripting language/environment, and is invoked either manually or from an operating system file. It has no built-in capability to schedule jobs to run at predetermined times or intervals, which is why I said "you can't". If your operating system does have this capability, then create a batch file to invoke sqlplus, and pass some parameters. Then use your operating system's scheduler to launch the batch file when required. Below is a windows batch file which I use often, and does just this.

@echo off 
cls 
echo Build Winpath and Load SD
echo -------------------------

if [%1]==[] (goto instructions)
if [%2]==[] (goto instructions)


REM If the client and server code pages don't align, chaos ensues
REM This is hard-coded to 437, if server alters, script must too.
REM Set code page to match server
CHCP 437

rem Create TableSpaces, users and roles
CD MAKE_SCRIPTS
SQLPLUS -S "SYS/%2@%1 AS SYSDBA" @MAKE_DB.SQL


rem Create Winpath Schema
CD..\BUILD_SCRIPTS
SQLPLUS -S WINPATH/WINPATH@%1 @BUILD_ALL_TABLES.SQL
@VERSION_STAMP\VERSION_STAMP.SQL

CD ..\UPGRADE_SCRIPTS
SQLPLUS -S WINPATH/WINPATH@%1 @UPGRADE_WINPATH.SQL

CD ..\STATIC_DATA_SCRIPTS
SQLPLUS -S WINPATH/WINPATH@%1 @LOAD_SD.SQL

echo.
NOTEPAD.EXE BUILD_ALL_TABLES.LOG

CD..

echo.
echo Build, static data load and upgrade complete.
pause
goto end

:instructions
echo Batchfile usage:

echo BUILD_WINPATH.BAT {db Name} {SYS Password}

echo.
echo Syntax error, build did not run, Press any key to try again
echo.
pause

:end

echo.

This batch file invokes sqlplus with two parameters, the database name and password. These are referenced as %1 and %2 in the file.
If you're happy with how this works, then just edit out all the bits you don't need, and replace the file name with your own particular one.

Regards

T

Grinding away at things Oracular

 

[jimirvine]

To schedule a task:Doc
To output to csv Thread

 

[Valeriya]

Hi guys,

Thank you very much for the valuable information! I was able to schedule and output to a CSV file.

 

[jimirvine]

Excellent. You know it would be really helpful if you could post what you did. That way, if anyone else requires a similar task, if they come to this thread the will have a ready made example (Obviously obfuscate any proprietary code or data)