Executing Procedures in Dynamic Substitution Variables

Dynamic substitution variables can return values and also perform other activities such as retrieving a sequence or logging their own timestamp references.

Executing database procedures from substitution variables expands the versatility of dynamic substitution variables. A reference to a dynamic substitution variable can return values and also perform other activities such as retrieving a sequence or logging its own timestamp reference.

If dynamic substitution variable procedures perform lengthy operations, they will hold up other processes that are waiting for the SQL to finish. If lengthy procedures are needed, they should be executed through a job.

Sample SQL for Building a Procedure

The sample SQL below builds a database procedure called test_proc and gives the Applications Manager database login execute authority:

CREATE or REPLACE PROCEDURE test_proc (field1 IN varchar2,

                                       field2 IN varchar2,

                                       field3 OUT varchar2) IS

BEGIN

  field3 := field1||'='||field2;

END;

   /

   grant execute on test_proc to Applications Manager

   /

Rules

Keep in mind the following rules when defining a procedure:

• A procedure must not contain more than one output field.
• If the procedure does not contain any output field, the dynamic substitution variable will return a NULL.
• A procedure may contain any number of input fields or none.
• The procedure should not perform lengthy operations.
• Be sure to grant execute on any procedures that are called with a synonym.
• The result output should not exceed 100 characters in length.

Required User Option

To use database procedures when defining substitution variables, you must have the Create Procedure SubVars user option assigned to you by your Applications Manager administrator.

Executing a Database Procedure in a Dynamic Substitution Variable

To execute a procedure in a dynamic substitution variable:

1. Open the Substitution Variables Selector window and click Add.

Applications Manager displays the Substitution Variables window shown below.



A Substitution Variable Procedure can return values and perform operations.

2. Enter a name for the variable.
3. Select data type 'Procedure.'
4. Leave the Value field blank.
5. Enter the procedure call statement, for example:

prod.test_proc('log.{requestor}','{run_id}',:result)

To return a value, the output argument of the procedure must be called :result as shown above. The example procedure uses two replacement values: the Requestor ID and the run ID. It will return a string that concatenates (combines) the two. The result would return 'log.SQLOPER=4022.00' for a task that was requested by SQLOPER and whose run ID was 4022.

6. To save the definition and close the window, click OK.