How to work with SQL*Plus and the Oracle DBMS

We recommend using our SQL*Plus job for working with SQL*Plus and the Oracle DBMS.
This is one of our JITL Jobs by Topic: Database jobs that are provided with JobScheduler.

Use JOE to create an SQL*Plus job like this:

• Open JOE's Job Wizard
• Select SOSSQLPlusJob in the list of available jobs and enter a job name
• Goto "next" to set the parameters for the database connection etc.

• See the reference documentation for the SQL*Plus job to get more information about the job parameters.

• After you have finished the wizard you can insert the SQL*Plus script in the script tab of the job. 

• Alternatively you can put the SQL*Plus script in an external file and add this filename to the sql_script_file parameter.

Example:

<?xml version="1.0" encoding="ISO-8859-1"?>
<job  title="Start SQL*Plus client and execute an SQL*Plus script" order="no">
    <description >
        <include  file="jobs/SOSSQLPlusJob.xml"/>
    </description>

    <params >
        <param  name="db_url" value="test"/>
        <param  name="db_user" value="test"/>
        <param  name="db_password" value="test"/>
    </params>

    <script  language="java" java_class="sos.scheduler.db.SOSSQLPlusJobJSAdapterClass">
        <![CDATA[
WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT FAILURE

prompt Hello World;
/
exit;
        ]]>
    </script>

    <run_time />
</job>