Page History

...

...

SQL script as an External File 

PL/SQL code can be defined directly inside the Job xml as value of command  parameter but is generally better stored on the file system. JITL jobs can be configured to read PL/SQL scripts from the file system by defining the script path as a value for the command  parameter i.e. 

In Following example the PL/SQL code is save at filesystem in C:\app\executables\plsqlsqls\get_orderbooking_datecycle.sql sql and subsequently refereed by the command  command_script_file  parameter.

<?xml version="1.0" encoding="ISO-8859-1"?>

<job  title="Start SQL*Plus client and execute a sql*plus script" order="no">
    <settings >
        <log_level ><![CDATA[debug9]]></log_level>
    </settings>
    <description >
        <include  file="jobs/SOSSQLPlusJob.xml"/>
    </description>
    <params >
        <param  name="BCY_DATE" value="28.07.2014"/>
        <param  name="PN_YEAR_PERIOD" value="2014"/>
        <param  name="PN_YEAR_PREV_PERIOD" value="2013"/>


        <param  name="command_script_file" value="C:\app\executables\sqls\get_booking_cycle.sql"/>

        <include  file="C:\Program Files\sos-berlin.com\jobscheduler\djsmp10_1.7.4274_4274\scheduler_data\config\live\common_settings\database\database_connection_sqlplus.parameter.xml"/>
    </params>
    <script  language="java" java_class="sos.scheduler.db.SOSSQLPlusJobJSAdapterClass"/>
    <run_time />
</job>

...

• JobChain

  Code Block
  language xml title JITL-PLSQL.job_chain.xml collapse true
  <?xml version="1.0" encoding="ISO-8859-1"?> <job_chain orders_recoverable="yes" visible="yes"> <job_chain_node state="execute_plsql" job="JITL-PLSQL" next_state="sucess" error_state="error"/> <job_chain_node state="sucess"/> <job_chain_node state="error"/> </job_chain>

• Job

  Code Block
  language xml title JITL-PLSQL.job.xml collapse true
  <?xml version="1.0" encoding="ISO-8859-1"?> <job title="Execute PL/SQL procedure" order="yes"> <settings > <log_level ><![CDATA[debug9]]></log_level> </settings> <description > <include file="jobs/JobSchedulerPLSQLJob.xml"/> </description> <params > <!-- Parameter can be passed by task or as order param --> <param name="date_mask" value="YYYYMMDD_HH24MI"/> <!-- Database connection parameters i.e. db_url, db_user, db_password --> <include live_file="../common_settings/database/database_connection.params.xml" node=""/>    <!-- dbms_output to JobScheduler Order parameter parser regex --> <param name="variable_parser_reg_expr" value="^SET\s+([^\s]+)\s*IS\s+(.*)$"/> </params> <script language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/> <run_time /> </job>

• Order : get_order_date

  Code Block
  language xml title JITL-PLSQL,get_order_date.order.xml collapse true
  <?xml version="1.0" encoding="ISO-8859-1"?> <order title="Calculate Order Date"> <params > <!-- PL/SQL script file --> <param name="command" value="C:/app/executables/plsql/get_last_booking_date.sql"/> </params> <run_time let_run="no"> <period single_start="08:00"/> </run_time> </order>

• Order : get_last_booking_date

  Code Block
  language xml title JITL-PLSQL,get_last_order_date.order.xml collapse true
  <?xml version="1.0" encoding="ISO-8859-1"?> <order title="Calculate last booking date"> <params > <!-- PL/SQL script file --> <param name="command" value="C:/app/executables/plsql/get_last_booking_date.sql"/> </params> <run_time let_run="no"> <period single_start="11:00"/> </run_time> </order>

Return parameters created by the

...

JobSchedulerSQLPlusJob

The JobScheduler automatically creates the following order parameters, which will be available to subsequent job steps as order parameters. 

...

• The sql_error parameter contains all the error messages generated during the PL/ SQL script execution. This parameter will be empty if no errors occur.

std_out_output

• The std_out_output parameter contains all the messages spooled to stdout by SQL Script.

std_err_output

• The std_out_output parameter contains all the messages spooled to stdout by PL/ SQL . Script.

exit_code

• The exit_code will be 0 If script ended without any error 
• If std_error_output is not empyt the exit code will be set to 99
• If either SP2 or an ORA- error occurs the exit code will be set to 98

See also:

• How to work with PL/SQL and the Oracle DBMS
• How to run Oracle Stored Procedures using PL/SQL
• The JobSchedulerPLSQLJob JobSchedulerSQLPlusJob JITL job documentation