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 Chapter 14, "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 Chapter 14, "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 Enterprise Scheduler (part of the Oracle Database). When a PL/SQL stored procedure job request is ready to execute, Oracle Enterprise Scheduler creates an immediate, run-once Oracle Enterprise Scheduler job. This Oracle Enterprise Scheduler job is owned by the Oracle Enterprise Scheduler runtime schema user associated with the container instance that executes the application that specifies the PL/SQL stored procedure. Finally, when the Oracle Enterprise Scheduler job runs, the PL/SQL stored procedure is called using dynamic SQL. After the PL/SQL stored procedure completes, either by a successful return or by raising an exception, the Oracle Enterprise Scheduler job completes.
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 8-1 shows a sample HELLO_WORLD stored procedure for use with Oracle Enterprise Scheduler.
Example 8-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,
'myUpdatedalue');
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;
/
Oracle Enterprise Scheduler uses an asynchronous execution model for PL/SQL stored procedure job types. Oracle Enterprise Scheduler does not directly call the PL/SQL stored procedure, but instead uses the Oracle Enterprise Scheduler in the Oracle Database. When a PL/SQL stored procedure request is ready to execute, Oracle Enterprise Scheduler creates an immediate, run-once Oracle Enterprise Scheduler job that is owned by the Oracle Enterprise Scheduler 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 Scheduler job runs. After the PL/SQL stored procedure completes, either by a successful return or by raising an exception, the Oracle Enterprise Scheduler 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 Scheduler state based on the result of the PL/SQL stored procedure.
Table 8-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 by the Oracle Enterprise Scheduler 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 Scheduler Runtime schema.
After you create the PL/SQL stored procedure that you want to use with Oracle Enterprise Scheduler a DBA needs to 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:
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;
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;
In the Oracle Database, grant execute on the PL/SQL stored procedure to the Oracle Enterprise Scheduler runtime schema user.
GRANT EXECUTE ON user_schema.proc_name to ess_schema;
For example, if the Oracle Enterprise Scheduler 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;
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 Scheduler 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 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 Chapter 6, "Using the Metadata Service".
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 schema.name format. This property must be specified for either the job type or job definition.
The SQL_JOB_CLASS system property specifies an Oracle Enterprise Scheduler job class to be assigned to the Oracle Enterprise Scheduler job used to execute an SQL job request. This property does not need to be specified unless the Oracle Enterprise Scheduler job used for a request should be associated with a particular Oracle Database resource consumer group or have affinity to a database service.
Oracle Enterprise Scheduler uses an Oracle Enterprise Scheduler job to execute the PL/SQL stored procedure for a SQL job request. An Oracle Enterprise Scheduler job class can be associated with the job when that job needs to have affinity to a database service or is to be associated with an Oracle Database resource consumer group. The Oracle Enterprise Scheduler job owner must have EXECUTE privilege on the Oracle Enterprise Scheduler job class in order to successfully create a job using that job class.
If the SQL_JOB_CLASS system property is not specified, a default Oracle Enterprise Scheduler job class is used for the Oracle Enterprise Scheduler job. The default job class is associated with the default resource consumer group. It will belong to the default service, which means it will have 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 8-2 describes the SystemProperties that are appropriate for a PL/SQL stored procedure job type.
Table 8-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 Enterprise Scheduler job class to be assigned to the Oracle Enterprise 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 Section 6.2, "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 Section 8.4.3, "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:
Once 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.Section 8.4.3, "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.
Example 8-2 shows sample code in which job type and job definition application metadata are created for a SQL job type.
Example 8-2 Oracle Enterprise Scheduler Program Using PL/SQL Stored Procedure
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;
}
}
}