8 Creating and Using PL/SQL Jobs

8.2.1 How to Define a PL/SQL Stored Procedure with the Correct Signature

The PL/SQL stored procedure that you call from Oracle Enterprise Scheduling Service must have a specific signature and include specific procedure parameters, as follows:

PROCEDURE my_proc(request_handle IN VARCHAR2);

The request_handle parameter is an opaque value representing an execution context for the Oracle Enterprise Scheduling Service request being executed.

Example 8-1 shows a sample HELLO_WORLD stored procedure for use with Oracle Enterprise Scheduling Service.

create or replace procedure HELLO_WORLD( request_handle in varchar2 )
as
    v_request_id  number := null;
    v_prop_name  varchar2(500) := null;
    v_prop_int  integer := null;
begin
    -- Get the Oracle Enterprise Scheduling Service request ID being executed.
    begin
        v_request_id := ess_runtime.get_request_id(request_handle);
    exception
        when others then
            raise_application_error(-20000,
                'Failed to get request id for request handle ' ||
                request_handle || '. [' || SQLERRM || ']');
    end;
 
    -- Retrieve value of an existing request property.
    begin
        v_prop_name := 'mytestIntProp';
        v_prop_int := ess_runtime.get_reqprop_int(v_request_id, v_prop_name);
    exception
        when others then
            rollback;
            raise_application_error(-20001,
                'Failed to get request property ' || v_prop_name ||
                ' for Oracle Enterprise Scheduling Service request ID ' || v_request_id ||
                '. [' || SQLERRM || ']' );
    end;
 
    -- Update an existing request property with a new value.
    -- This procedure is responsible for commit/rollback of the update operation.
    begin
        v_prop_name := 'myJobdefProp';
        ess_runtime.update_reqprop_varchar2(v_request_id, v_prop_name,
                                            'myUpdatedalue');
        commit;
    exception
        when others then
            rollback;
            raise_application_error(-20002,
                'Failed to update request property ' || v_prop_name ||
                ' for Oracle Enterprise Scheduling Service request ID ' || v_request_id ||
                '. [' || SQLERRM || ']' );
    end;
end helloworld;
/

8.2.2 Handling Runtime Exceptions in an Oracle Enterprise Scheduling Service PL/SQL Stored Procedure

Oracle Enterprise Scheduling Service uses an asynchronous execution model for PL/SQL stored procedure job types. Oracle Enterprise Scheduling Service does not directly call the PL/SQL stored procedure, but instead uses the Oracle Enterprise Scheduling Service in the Oracle Database. When a PL/SQL stored procedure request is ready to execute, Oracle Enterprise Scheduling Service creates an immediate, run-once Oracle Enterprise Scheduling Service job that is owned by the Oracle Enterprise Scheduling Service runtime schema user associated with the container instance executing that executes the application associated with the PL/SQL stored procedure. The PL/SQL stored procedure is called using dynamic SQL when the Oracle Enterprise Scheduling Service job runs. After the PL/SQL stored procedure completes, either by a successful return or by raising an exception, the Oracle Enterprise Scheduling Service job completes.

In the PL/SQL stored procedure, you can handle exceptions and other issues by raising a RAISE_APPLICATION_ERROR exception. The RAISE_APPLICATION_ERROR requires that the error code from the PL/SQL stored procedure range from -20000 to -20999. The PL/SQL stored procedure can use RAISE_APPLICATION_ERROR if it needs to raise an exception. RAISE_APPLICATION_ERROR requires that the error code range from -20000 to -20999.

Table 8-1 indicates the Oracle Enterprise Scheduling Service state based on the result of the PL/SQL stored procedure.

8.2.3 How to Access Job Request Information In PL/SQL Stored Procedures

Oracle Enterprise Scheduling Service provides a PL/SQL package, ESS_RUNTIME to perform certain operations that you may need when you are working in a PL/SQL stored procedure. You can use these procedures perform job request operations and to obtain job request information for an Oracle Enterprise Scheduling Service runtime schema. For example, you can use these runtime procedure to submit requests and retrieve and update request information associated with an Oracle Enterprise Scheduling Service job request.

The following sample code shows use of an ESS_RUNTIME procedure:

v_request_id := ess_runtime.get_request_id(request_handle);

This request obtains the request ID associated with a job request.

Certain procedures in the ESS_RUNTIME package require a request handle parameter and provide information on an executing request (these should only be called from the PL/SQL stored procedure that is executing the PL/SQL stored procedure request). You can call some procedures in the ESS_RUNTIME package from outside of the context of an executing request; these procedures may include a request id parameter.

8.2.4 What You Need to Know When You Define a PL/SQL Stored Procedure

You need to know the following when you create an use a PL/SQL stored procedure with Oracle Enterprise Scheduling Service:

• It is not required that the PL/SQL stored procedure exist when the Oracle Enterprise Scheduling Service request is submitted, but the PL/SQL stored procedure must exist and be callable by the Oracle Enterprise Scheduling Service runtime schema user when the request is ready to run.

• The PL/SQL stored procedure must exist on the same database as the Oracle Enterprise Scheduling Service Runtime schema.

8.3.1 How to Grant PL/SQL Stored Procedure Permissions

Before the DBA grants permissions, the DBA must determine the Oracle Database and the Oracle Enterprise Scheduling Service run time schema that is associated with the deployed Java EE application that is going to submit the Oracle Enterprise Scheduling Service PL/SQL stored procedure request.

Use the following definitions when you grant PL/SQL stored procedure permissions:

ess_schema: specifies the Oracle Enterprise Scheduling Service runtime schema associated with the Java EE application.

user_schema: specifies the name of the application user schema.

PROC_NAME: specifies the name of the PL/SQL stored procedure associated with the Oracle Enterprise Scheduling Service job request.

To grant Oracle Database permissions:

1. In the Oracle Database grant execute on the ESS_RUNTIME package to the application user schema. For example:

   GRANT EXECUTE ON ess_schema.ess_runtime to user_schema;
   

2. In the Oracle Database, create a private synonym for the ESS_RUNTIME package. This is a convenience step that allows the PL/SQL stored procedure to reference the ESS_RUNTIME as simply ESS_RUNTIME rather than using the full schema_name.ESS_RUNIME. For example:

   create or replace synonym user_schema.ess_runtime for ess_schema.ess_runtime;
   

3. In the Oracle Database, grant execute on the PL/SQL stored procedure to the Oracle Enterprise Scheduling Service runtime schema user.

   GRANT EXECUTE ON user_schema.proc_name to ess_schema;
   

   For example, if the Oracle Enterprise Scheduling Service runtime schema is TEST_ORAESS, the application user schema is HOWTO, and the PL/SQL procedure is named HELLO_WORLD, the DBA operations needed would be:

   GRANT EXECUTE ON test_oraess.ess_runtime to howto;
   create or replace synonym howto.ess_runtime for test_oraess.ess_runtime;
   GRANT EXECUTE ON howto.hello_world to test_oraess;
   

8.3.2 What You Need to Know About Granting PL/SQL Stored Procedure Permissions

The first two steps shown for DBA tasks for granting permissions on the ESS_RUNTIME package are only required if the ESS_RUNTIME package is referenced by a PL/SQL procedure. These two steps are not required if the ESS_RUNTIME package is never used from that application user schema. The third step shown is always required since it allows Oracle Enterprise Scheduling Service to call the user defined PL/SQL stored procedure.

All PL/SQL stored procedures in a given application user schema that are used for Oracle Enterprise Scheduling Service PL/SQL stored procedure jobs should always be associated with the same (single) Oracle Enterprise Scheduling Service Runtime schema. While this is not technically required, it greatly simplifies the DBA setup and does not require the PL/SQL stored procedure to explicitly specify the Oracle Enterprise Scheduling Service Runtime schema if the procedure references the ESS_RUNTIME.

8.4.1 How to Create a PL/SQL Job Type

An Oracle Enterprise Scheduling Service JobType object specifies an execution type and defines a common set of properties for a job request. A job type can be defined and then shared among one or more job definitions. Oracle Enterprise Scheduling Service supports three execution types:

• JAVA_TYPE: for job definitions that are implemented in Java and run in the container.

• SQL_TYPE: for job definitions that run as PL/SQL stored procedures in a database server.

• PROCESS_TYPE: for job definitions that are binaries and scripts that run as separate processes.

When you specify the JobType you can also specify properties that define the characteristics associated with the JobType. Table 8-2 describes the SystemProperties that are appropriate for a PL/SQL stored procedure job type.

When you create and store a PL/SQL job type, you do the following:

• Use the JobType constructor and supply a String name and a JobType.ExecutionType.SQL_TYPE argument.

• Set the appropriate properties for the new JobType.

• Obtain the metadata pointer, as shown in Section 6.2, "Accessing the Metadata Service". Use the Metadata Service addJobType() method to store the JobType in metadata.

• Use a MedatdataObjectId that uniquely identifies metadata objects in the metadata repository, and, using a unique identifier the MetadataObjectID contains the fully qualified name for a metadata object.

See Section 8.4.3, "Using a PL/SQL Stored Procedure with an Oracle Enterprise Scheduling Service Application" for sample code.

8.4.2 How to Create and Store a Job Definition for PL/SQL Job Type

To use PL/SQL with Oracle Enterprise Scheduling Service, you need to create and store a job definition. A job definition is the basic unit of work that defines a job request in Oracle Enterprise Scheduling Service. Each job definition belongs to one and only one job type.

Section 8.4.3, "Using a PL/SQL Stored Procedure with an Oracle Enterprise Scheduling Service Application" shows how to create a job definition using the job definition constructor and the job type.

8.4.3 Using a PL/SQL Stored Procedure with an Oracle Enterprise Scheduling Service Application

Example 8-2 shows sample code in which job type and job definition application metadata are created for a SQL job type.

import oracle.as.scheduler.JobType;
import oracle.as.scheduler.JobDefinition;
import oracle.as.scheduler.MetadataService;
import oracle.as.scheduler.MetadataServiceHandle;
import oracle.as.scheduler.MetadataObjectId;
import oracle.as.scheduler.ParameterInfo;
import oracle.as.scheduler.ParameterInfo.DataType;
import oracle.as.scheduler.ParameterList;
 
void createDefinition( )
{
   MetadataService metadata = ...
   MetadataServiceHandle mshandle = null;
 
   try
   {
      ParameterInfo pinfo;
      ParameterList plist;
 
      mshandle = metadata.open();
      
      // Define and add a PL/SQL job type for the application metadata.
      String jobTypeName = "PLSQLJobDefType";
      JobType jobType = null;
      MetadataObjectId jobTypeId = null;
 
      jobType = new JobType(jobTypeName, JobType.ExecutionType.SQL_TYPE);
 
      plist = new ParameterList();
      pinfo = SystemProperty.getSysPropInfo(SystemProperty.PROCEDURE_NAME);
      plist.add(info.getName(), pinfo.getDataType(), "HOWTO.HELLO_WORLD", false);
      pinfo = SystemProperty.getSysPropInfo(SystemProperty.PRODUCT);
      plist.add(pinfo.getName(), pinfo.getDataType(), "HOW_TO_PROD", false);
      jobType.setParameters(plist);
 
      jobTypeId = metadata.addJobType(mshandle, jobType, "HOW_TO_PROD");
 
      // Define and add a job definition for the application metadata.
      String jobDefName = "PLSQLJobDef";
      JobDefinition jobDef = null;
      MetadataObjectId jobDefId = null;
 
      jobDef = new JobDefinition(jobDefName, jobTypeId);
      jobDef.setDescription("Demo PLSQL Job Definition " + jobDefName);
 
      plist = new ParameterList();
      plist.add("myJobdefProp", DataType.STRING, "myJobdefVal", false);
      jobDef.setParameters(plist);
 
      jobDefId = metadata.addJobDefinition(mshandle, jobDef, "HOW_TO_PROD");
   }
   catch (Exception e)
   {
      [...]
   }
   finally
   {
      // always close metadata service handle in finally block
      if (null != mshandle)
      {
         metadata.close(mshandle);
         mshandle = null;
      }
   }
}