Using the OracleJob Definition Type

This Definition Type requires the GLOBAL.System_Oracle Job Server, which can be created by following the instructions outlined in the Creating an OracleJob Job Server. The name of the Partition of the Job Definition must be an Oracle schema name as well (case-sensitive), except for the GLOBAL Partition. The Cronacle schema is used for Job Definitions in the GLOBAL Partition.

Requirements for Creating and Editing OracleJob Job Definitions

You need access to the credential with username = <partition-name>, endpoint=System_Oracle, Partition=GLOBAL, Credential type = JDBC.

Variables and Parameters

• Parameters to the script are used in the PL/SQL source using the standard PARAMETER syntax.
• Out Parameters are supported. You can assign a value to a Parameter like so: PARAMETER := 10
• Array Parameters are supported.

Parameter Mappings

• String Parameters are mapped to the VARCHAR2 datatype.
• Number Parameters are mapped to the NUMBER datatype.
• Date Parameters are mapped to the DATE datatype.
• Time Parameters are mapped to the DATE datatype.
• DateTime Parameters are mapped to the DATE datatype.
• DateTimeZone Parameters are mapped to the TIMESTAMP WITH TIMEZONE datatype.

REL Functions

REL functions can be used to dynamically set Parameters. A number of functions (PLSQL package) have been introduced that allow you to interact with Oracle databases. These functions necessitate a JDBC Job Server connected to an Oracle database or System_Oracle. For more information, see PL/SQL Redwood Expression Language Functions.

• booleanExpr(<connect_info>, <PLSQL_expression>, <arguments>)
• dateExpr(<connect_info>, <PLSQL_expression>, <arguments>)
• query(<connect_info>, <query>, <arguments>)
• numberExpr(<connect_info>, <PLSQL_expression>, <arguments>)
• stringExpr(<connect_info>, <PLSQL_expression>, <arguments>)
• clearCache()

The connection information <connect_info> is in the format user=<user> endpoint=<oracle_sid>. See below for an examlple.

Example

=PLSQL.stringExpr('user=scott endpoint=xe', '\'Greetings from Job \' || ?', JobId)

'Greetings from Job ' || ? is a PL/SQL expression returning a VARCHAR2 (or String in REL). It contains one bind variable placeholder, so one extra Parameter (JobId) must be specified.

The number of bind variable placeholders in the query/expression and bind values in the call are variable, but need to match. The first value is placed in the first placeholder, the second in the second placeholder, and so on.

Note: If a Job reaches status Error, the Out values of Parameters are not always set. You can use a Post Running Action to set them if required.

Returning an Exception

If your code exits with an exception, the Job will go into status Error. You can use raise_application_error(<exit_code>, <message>) in your code to raise an exception manually. For example: raise_application_error(-20000, 'An error occurred'). In this case, the exit code will be -20000.

Security

The System_Oracle Job Server may have a Job Server Parameter named DefaultRunAsUser, which will act as the default Run As User. The submitter of the process needs to have privileges on the Credential for this user. The Oracle database user matching the Partition of the Job Definition must at least have the CREATE SESSION and CREATE PROCEDURE privileges.

The syntax for specifying a user in the Run As User field is [{virtual}:]<user>[/<password>][@<endpoint>]. Although you can specify a password in the Run As User field, this is not recommended, because if the password changes, all Job Definitions using the password will have to be modified. The same recommendation applies to the use of the JCS_PASSWORD and JCS_USER field, though for the latter only if you specify the password. If you leave the endpoint empty, the Job Server of the process is used.

Example:

• scott@System_Oracle
• scott
• {virtual}:scotty
• scott/tiger@System_Oracle

Username and Password in Parameters

Using the following parameters, you can specify the username and password for the user.

• JCS_USER
• JCS_PASSWORD

Tip: You may specify the password in the JCS_USER parameter; in this case, make the parameter a Password parameter so the password is not displayed in clear text. This should always be done for JCS_PASSWORD.

Credentials

The OracleJob Definition Type is integrated with the Credential system. The Run As User field can be populated with the Oracle database user.

If the Run As User field is left empty, the value of the DefaultRunAsUser Job Server Parameter will be used.

To use a particular username and password:

• Create a JDBC credential set for each required login, with the following attributes for each set:
  • Endpoint: Set to System_Oracle or another JDBC Job Server connected to an Oracle database.
  • Real User: The Oracle database username.
  • Password: The Oracle database password.
  • Virtual User (optional): An alternative name to find the username.

The syntax for specifying a credential in the Run As User field is [{virtual}:]<user>[@<endpoint>].

Using the Owner

Use the {forcejobowner} or {forcejobowner} syntaxes to specify the owner in the Run As User field. In this case, the submitter must have access to a corresponding credential with the same username or virtual username; the RunMyJobs username of the submitter must match the Oracle username or match the virtual username of a valid OracleJob JDBC credential.

The syntax is as follows:

• [{virtual}:]{forcejobowner}[@<endpoint>]
• [{virtual}:]{tryjobowner}[@<endpoint>]
• [{virtual}:]{tryjobowner}[@<endpoint>]:[{virtual}]<user>[/<password][@<endpoint>]

The following table contains examples for using the owner and the potential consequences of a missing Credential:

Note that {forcejobowner} and {tryjobowner} syntaxes are not supported in the JCS_USER Job Definition parameter.

Examples

Fill a variable with the output of a function.

declare
p1 number;
begin
select jcs.current_job_ID into p1 from dual;
dbms_output.put_line(p1);
end;

See Also

• PL/SQL Redwood Expression Language Functions