This chapter describes how to create PL/SQL stored procedures for use with Oracle Enterprise Scheduler, and describes Oracle Database tasks that you need to perform to use PL/SQL stored procedures with Oracle Enterprise Scheduler.
After you create a PL/SQL procedure and define a job definition, you can use the Oracle Enterprise Scheduler runtime service to submit a job request for a PL/SQL procedure.
This chapter includes the following sections:
Introduction to Using PL/SQL Stored Procedure Job Definitions
Creating a PL/SQL Stored Procedure for Oracle Enterprise Scheduler
Performing Oracle Database Tasks for PL/SQL Stored Procedures
For information about how to use the Runtime Service, see Using the Runtime Service.
Oracle Enterprise Scheduler lets you run job requests of different types, including: Java classes, PL/SQL stored procedures, and process requests that run as a forked process. To use Oracle Enterprise Scheduler with PL/SQL stored procedures you need to do the following:
Create or obtain the PL/SQL stored procedure that you want to use with Oracle Enterprise Scheduler.
Load the PL/SQL stored procedure in the Oracle Database and grant the required permissions and perform other required DBA tasks.
Use Oracle JDeveloper to create job type and job definition objects and store these objects with the Oracle Enterprise Scheduler application metadata.
Use Oracle JDeveloper to create an application with Oracle Enterprise Scheduler APIs that runs and submits a PL/SQL stored procedure.
Finally, after you create an application that uses the Oracle Enterprise Scheduler APIs you use Oracle JDeveloper to deploy and run the application.
At runtime, after you submit a job request you can monitor and manage the job request. For more information, see Using the Runtime Service.
Oracle Enterprise Scheduler uses an asynchronous execution model for PL/SQL stored procedure job requests. This means that Oracle Enterprise Scheduler does not directly call the PL/SQL stored procedure, but instead uses Oracle Database Scheduler (an Oracle Database feature). When a PL/SQL stored procedure job request is ready to execute, Oracle Enterprise Scheduler creates an immediate, run-once Oracle Database Scheduler job. This Oracle Database Scheduler job is created by the Oracle Enterprise Scheduler runtime schema user associated with the container instance that executes the PL/SQL request, and is owned by the application procedure owner. The Oracle Database Scheduler job procedure is a PL/SQL wrapper procedure owned by the Oracle Enterprise Scheduler runtime schema user. Finally, when the Oracle Database Scheduler job runs, the wrapper procedure calls the application stored procedure using dynamic SQL. After the PL/SQL stored procedure completes, either by a successful return or by raising an exception, the Oracle Database Scheduler job finishes and creates an event that informs Oracle Enterprise Scheduler that the remote executable finished.
When you want to use a PL/SQL stored procedure with Oracle Enterprise Scheduler, the PL/SQL procedure must have certain characteristics to work with an Oracle Enterprise Scheduler application and a DBA must assure that certain Oracle Database permissions are assigned to the PL/SQL stored procedure.
Creating a PL/SQL stored procedure involves the following steps:
Define the PL/SQL stored procedure that has the correct signature for use with Oracle Enterprise Scheduler
Perform the required DBA tasks to make the PL/SQL stored procedure available to Oracle Enterprise Scheduler
The PL/SQL stored procedure that you call from Oracle Enterprise Scheduler 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 Scheduler request being executed.
Example 11-1 shows a sample HELLO_WORLD stored procedure for use with Oracle Enterprise Scheduler.
Example 11-1 HELLO_WORLD PL/SQL Stored Procedure
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 Scheduler 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 Scheduler 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,
'myUpdateValue');
commit;
exception
when others then
rollback;
raise_application_error(-20002,
'Failed to update request property ' || v_prop_name ||
' for Oracle Enterprise Scheduler request ID ' || v_request_id ||
'. [' || SQLERRM || ']' );
end;
end helloworld;
/
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 must raise an exception. RAISE_APPLICATION_ERROR requires that the error code range from -20000 to -20999.
Table 11-1 indicates the Oracle Enterprise Scheduler state based on the result of the PL/SQL stored procedure.
Table 11-1 Terminal States for PL/SQL Stored Procedure Results
| Final State | Description |
|---|---|
|
|
If the PL/SQL stored procedure returns normally, without raising an exception, the request state transitions to the |
|
|
If the PL/SQL stored procedure returns with an exception, the request state is based on the SQL error code of the exception. The request transitions to the |
|
|
If the PL/SQL stored procedure returns with an exception, the request state is based on the SQL error code of the exception. The request transitions to the Return codes in the range -20920 to -20929 result in an |
Oracle Enterprise Scheduler 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 Scheduler runtime schema. For example, you can use these runtime procedure to submit requests and retrieve and update request information associated with an Oracle Enterprise Scheduler 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.
You need to know the following when you create an use a PL/SQL stored procedure with Oracle Enterprise Scheduler:
It is not required that the PL/SQL stored procedure exist when the Oracle Enterprise Scheduler request is submitted, but the PL/SQL stored procedure must exist and be callable when the request is ready to run.
The PL/SQL stored procedure must exist on the same database as the Oracle Enterprise Scheduler Runtime schema.
After you create the PL/SQL stored procedure that you want to use with Oracle Enterprise Scheduler a DBA must load the PL/SQL stored procedure in the Oracle Database and grant the required permissions.
Before the DBA grants permissions, the DBA must determine the Oracle Database and the Oracle Enterprise Scheduler runtime schema that is associated with the deployed Java EE application that is going to submit the Oracle Enterprise Scheduler PL/SQL stored procedure request.
Use the following definitions when you grant PL/SQL stored procedure permissions:
ess_schema: specifies the Oracle Enterprise Scheduler 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 Scheduler job request.
To grant Oracle Database permissions:
As an example, if the Oracle Enterprise Scheduler runtime schema is TEST_ESS, the application user schema is HOWTO, and the PL/SQL procedure is named HELLO_WORLD, the DBA operations are:
GRANT EXECUTE ON test_ess.ess_runtime to howto; CREATE OR REPLACE SYNONYM howto.ess_runtime for test_ess.ess_runtime; GRANT EXECUTE ON test_ess.ess_job to howto; CREATE OR REPLACE SYNONYM howto.ess_job for test_ess.ess_job; GRANT EXECUTE ON test_ess.ESS_SCHJOB_PROC to howto;
The 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. The two steps shown for DBA tasks use to grant permissions on the ESS_JOB package are only required if the ESS_JOB package is referenced by a PL/SQL procedure. The step shown for the ESS_SCHJOB_PROC procedure is always required since it allows the Oracle Enterprise Scheduler wrapper procedure to be called.
All PL/SQL stored procedures in a given application user schema that are used for Oracle Enterprise Scheduler PL/SQL stored procedure jobs should always be associated with the same (single) Oracle Enterprise Scheduler 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 Scheduler runtime schema if the procedure references the ESS_RUNTIME.
To use PL/SQL stored procedures with Oracle Enterprise Scheduler you need to locate the Metadata Service and create a job definition. You create a job definition by specifying a name and a job type. When you create a job definition you also need to set certain system properties. You can then store the job definition and other associated objects using the Metadata Service.
For information about how to use the Metadata Service, see Using the Metadata Service .
Oracle Enterprise Scheduler uses an Oracle Database Scheduler job to execute the PL/SQL stored procedure for a SQL job request. An Oracle Database Scheduler job class can be associated with the job when that job must have affinity to a database service or is to be associated with an Oracle Database resource consumer group. The Oracle Database Scheduler job owner must have EXECUTE privilege on the Oracle Database Scheduler job class in order to successfully create a job using that job class.
You can use Oracle Enterprise Scheduler system properties to specify certain attributes for the Oracle Enterprise Scheduler job that calls the PL/SQL stored procedure.
These SystemProperty properties apply specifically to SQL job types; PROCEDURE_NAME, SQL_JOB_CLASS.
The PROCEDURE_NAME system property specifies the name of the PL/SQL stored procedure to be executed. The stored procedure name should have a owner.name format, where owner is the schema owner of the job procedure and name is the procedure name. This property must be specified for either the job type or job definition.
The SQL_JOB_CLASS system property specifies an Oracle Database Scheduler job class to be assigned to the Oracle Database Scheduler job used to execute an SQL job request. This property does not need to be specified unless the Oracle Database Scheduler job used for a request should be associated with a particular Oracle Database resource consumer group or have affinity to a database service.
If the SQL_JOB_CLASS system property is not specified, a default Oracle Database Scheduler job class created by Oracle Enterprise Scheduler is used for the Oracle Database Scheduler job. The default job class is associated with the default resource consumer group. It belongs to the default service, which means it has no service affinity and in an Oracle RAC environment any one of the database instances within the cluster might run the job. No additional privilege grant is needed for an Oracle Enterprise Scheduler SQL request to use that default job class.
An Oracle Enterprise Scheduler 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 Scheduler 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 11-2 describes the SystemProperties that are appropriate for a PL/SQL stored procedure job type.
Table 11-2 Oracle Enterprise Scheduler System Properties for a PL/SQL Stored Procedure Job Type
| System Property | Description |
|---|---|
|
|
Specifies the name of the stored procedure to run as part of PL/SQL job execution. For a |
|
|
Specifies an Oracle Database Scheduler job class to be assigned to the Oracle Database Scheduler job used to execute an SQL job request. This is an optional property for a |
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 Accessing the Metadata Service. Use the Metadata Service addJobType() method to store the JobType in metadata.
Use a MetadataObjectId 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 Using a PL/SQL Stored Procedure with an Oracle Enterprise Scheduler Application for sample code.
To use PL/SQL with Oracle Enterprise Scheduler, 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 Scheduler. Each job definition belongs to one and only one job type.
Note:
After you create a job definition with a job type, you cannot change the type or the job definition name. To change the type or the job definition name, you need to create a new job definition.
Using a PL/SQL Stored Procedure with an Oracle Enterprise Scheduler Application shows how to create a job definition using the job definition constructor and the job type.
This section 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;
}
}
}