Dynamic Parameter

Dynamic parameter is a job parameter whose value is determined at runtime.

For example, Bill Run job may run every 5th of the month to invoice all agreements that have come till 1st of the month. The job can be scheduled to run on the 5th day of every month. However, it needs a ‘1st of this month’ as input to determine which agreements are to be billed.

The scheduling service determines the value specified as a custom date formula or through a stored procedure and schedules the instance to run with the value. The parameter values for a scheduled job can be set dynamically whenever the scheduled (recurring) job starts. This is an attribute to job parameter at schedule time. User can either choose Custom Date Function or Procedure for that parameter.

To set dynamic parameter:

  1. Click Dynamic hyperlink against the date type parameter for any job at the time of scheduling jobs.

  2. A new pop up window appears for entering dynamic values.

  3. In the Dynamic pop up window, select the Type as Procedure or Custom Function from the drop-down.

  4. If Procedure Name is selected, then specify the Stored Procedure Name. Click the Save button to return the dynamic value for that parameter at each job run.

  5. If Custom Date Function is selected, then select Month, Day, Month Operator, Day Operator, from the drop-down and enter No of Month and No of Days in the respective fields. Click the Save button to execute the specified stored procedure to get the value for that parameter at each job run.

In Monitor Jobs screen, click Show Job Parameter Values link to view Request ID, Run Time, Job, parameter names and corresponding values used of the request for all dynamic parameters used at each job run.

Click to view Stored Procedure Template with Example:

The Procedure must have 5 parameters (2 in parameters and 3 out parameters)

Parameter Data Type details are as follows:

Parameter No    

Data Type    

Direction          

Description

1

Number

In

Schedule Request ID

2

Date

In

 Run Date

3

Varchar

Out

Return Value

4

Number

Out

Error Code

5

Varchar

Out

Error Message

The dynamic procedure should be written in the following format (all the parameters should be mandatory and should have the same name):

CREATE OR REPLACE PROCEDURE TEST_DYN_PARAMETER (

P_SCREQID NUMBER,

P_RUNAT DATE,

RET_VAL OUT VARCHAR2,

P_ERRCODE OUT NUMBER,

P_ERRMESG OUT VARCHAR2)

AS

BEGIN

RET_VAL := ADD_MONTHS(P_RUNAT,1);

P_ERRCODE := 0;

P_ERRMESG := '';

EXCEPTION WHEN OTHERS THEN

P_ERRCODE := SQLCODE;

P_ERRMESG := SQLERRM;

END;