Working with SQL Script Jobs

In a SQL Script job, you can specify any of the following in the SQL Script field of the Parameters page:

A fully qualified SQL script file, for example:

@/private/oracle/scripts/myscript.sql

Make sure that the script file is installed in the appropriate location on all targets.
A PL/SQL script, using syntax supported by SQL*Plus, for example, one of the following:

EXEC plsql_block;

or:
```
DECLARE

   local_date DATE;

BEGIN

   SELECT SYSDATE INTO local_date FROM dual;

END;

/
```
Any directives supported by SQL*Plus.
The contents of the SQL script itself, up to a maximum of 4 KB in size.

Target properties can be used in the SQL Script field. Click Show Available Target Properties to view the properties you can use for the target. Target properties are case-sensitive.

You can enter optional parameters to SQL*Plus in the Parameters field.

Returning Error Codes from SQL Script Jobs

The SQL Script job internally uses SQL*Plus to run a user's SQL or PL/SQL script. If SQL*Plus returns 0, the job will return a status of Succeeded. If it returns any other value, it will return a value of job status of Failed.

By default, if a SQL script runs and encounters an error, it may still result in a job status of Succeeded because SQL*Plus still returned a value of 0. In order to make such jobs return a Failed status, you can use SQL*Plus EXIT to return a non-zero value.

The following examples show how you can return values from your PL/SQL or SQL scripts. These in turn, will be used as the return value of SQL*Plus and hence provide a way to return the appropriate job status (Succeeded or Failed). Refer to your SQL*Plus User's Guide and Reference for more information on returning EXIT codes.

Example 1

WHENEVER SQLERROR EXIT SQL.SQLCODE
select column_does_not_exist from dual;

Example 2

-- SQL*Plus will NOT return an error for the next SELECT statement
SELECT COLUMN_DOES_NOT_EXIST FROM DUAL;

WHENEVER SQLERROR EXIT SQL.SQLCODE;

BEGIN

  -- SQL*Plus will return an error at this point

  SELECT COLUMN_DOES_NOT_EXIST FROM DUAL;

END;
/

WHENEVER SQLERROR CONTINUE;

Example 3

variable exit_code number;


BEGIN

 DECLARE

 local_empno number(5);

 BEGIN

  -- do some work which will raise exception: no_data_found

  SELECT 123 INTO local_empno FROM sys.dual WHERE 1=2;

 EXCEPTION

  WHEN no_data_found THEN

    :exit_code := 10;

  WHEN others THEN

    :exit_code := 2;

  END;

 END;

/

exit :exit_code;

Example 4

-- interactive mode only

col test new_value bye

SELECT 123 test

FROM dual;

exit &bye;

Specifying Targets for a SQL Script Job

A SQL Script job can be run against database and cluster database target types. You select the targets to run the job against by clicking Add in the Databases section. The Add Targets page displays. Click Help on the Add Targets page for more information about selecting database targets that the job will run against.

Specifying Host and Database Credentials for a SQL Script Job

In the Host and Database Credentials section, you specify the host credentials and database credentials. Host credentials will be used by the Management Agent to launch the SQL*Plus executable; database credentials will be used to connect to the target database and run the SQL script. The job can use either the preferred credentials for hosts and databases, or you can specify other credentials that will override those preferred credentials.

If you want to use the preferred credentials for the targets for your SQL Script job, click Use Preferred Credentials and choose either Normal or Privileged in the Credential Type field. The credentials used for both host and database will be the ones you have specified under your Database Preferred Credentials. If you choose Normal, then your Normal database preferred credentials will be used; if you choose Privileged, then the SYSDBA preferred credentials will be used. For both cases, the host credentials associated with the database target will be used. Each time the job executes, it will pick up the current values of your preferred credentials.

To override the preferred credentials for all the targets, click Override Preferred Credentials and then enter the preferred credentials you want the job to use on all the targets.

To view preferred credentials for databases, click Show Preferred Credentials to display the Preferred Credentials page (right-click the link and open it in a new window to enable you to return easily to the Create Job page), then click the Set Credentials icon for Databases to view preferred credentials for the database and its associated host. Enterprise Manager displays the Database Preferred Credentials page. Click Help on the Database Preferred Credentials page for more information about database preferred credentials.

Working with SQL Script Jobs

Returning Error Codes from SQL Script Jobs

Specifying Targets for a SQL Script Job

Specifying Host and Database Credentials for a SQL Script Job

Related Topics