← Creating the OracleJob Process ServerPackages →

Using the OracleJob Definition Type

This definition type requires the GLOBAL.System_Oracle process server, which can be created by following the instructions outlined in the Creating an OracleJob Process Server. The name of the partition of the process definition must be an Oracle schema name as well (case-sensitive), except for the GLOBAL partition. The Cronacle schema is used for process definitions in the GLOBAL partition.

Requirements for Creating and Editing OracleJob Process Definitions

The Redwood Server user needs 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; just assign a value to the parameter using the regular method. For example, 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 process server connected to an Oracle database or System_Oracle. See the PL/SQL Redwood Expression Language Functions topic.

  • 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 example below.

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 place holder, therefore one extra parameter (JobId) needs to be specified.

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

note

When a process 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 process will reach status Error; you can use raise_application_error(<exit_code>, <message>); into your code to raise an exception. For example: raise_application_error(-20000, 'An error occurred');, in this case the exit code will be -20000.

Security

The System_Oracle process server may have a process server parameter 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 process 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 may specify a password in the Run As User field this is not recommended, as all process definitions using the password will have to be modified once the password changes. The same recommendation applies to the use of the JCS_PASSWORD and JCS_USER field, for the latter only if you specify the password. If you leave the endpoint empty, the process server of the process is used.

Example:

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

Username and Pawword 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 filled with the Oracle database user.

If the Run As User field is left empty, the value of the DefaultRunAsUser process server parameter will be used.

To use a particular username and password you need to set up the following:

  • Create a JDBC credential set for each required login, with the following attributes for each set:
    • Endpoint: Set to System_Oracle or another JDBC process 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 Redwood Server 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:

Run As User fieldConsequence
{forcejobowner}@System_Oracleif there is no credential with the username of the submitter the process fails
{tryjobowner}if there is no credential with the username of the submitter the value of process server parameter DefaultRunAsUser is used. The process server where the process runs specifies the Endpoint.
{tryjobowner}@System_Oracle:SYSJCSif there is no credential with the username of the submitter, the SYSJCS credential is used

Note that {forcejobowner} and {tryjobowner} syntaxes are not supported in the JCS_USER process 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

OracleJob

← Creating the OracleJob Process ServerPackages →