Defining Dynamic Substitution Variables

Values for dynamic subvars are generated by a SQL statement when the subvar is evaluated. The SQL for dynamic subvars can run against the Applications Manager database or any database you define for JDBC use. Dynamic subvars that run against the Applications Manager database do not require a login and are referred to as plain subvars.

Values for dynamic subvars are generated by a SQL statement when the subvar is evaluated. The SQL for dynamic subvars can run against the Applications Manager database or any database you define for JDBC use. Dynamic subvars that run against the Applications Manager database do not require a login and are referred to as plain subvars. You can use dynamic substitution variables in conditions to evaluate the state of a database and control job/process flow execution. For example, you can check for the number of rows in a table, and delay the job if the count is less than 50. A dynamic substitution variable that returns the date of the first day of the month is shown below.

Dynamic substitution variables use SQL statements to generate values.

Procedure

To define a dynamic substitution variable:

  1. From the Substitution Variables Selector window, click New.

    2. Applications Manager opens the Substitution Variables window shown above.

    For information on using selector windows, see Adding, Editing, and Deleting Applications Manager Objects.

  2. Complete the fields using the information in the following table.
    Field Description

    Name

    The name must start with a '#' sign. Names cannot contain blanks or start with numbers. They can be up to 30 characters long.

    Type

    The data type, used to validate entries when this substitution variable is used in prompts. When you include a Date type substitution variable in a prompt, the Date output format in the data type selected for the subvar must match the Date input format in the data type selected for the prompt. For more information on data types, see Defining Data Types.

    Value

    Applications Manager ignores this field when you enter the SQL statement. For information on this field, see Defining Static Substitution Variables.

    Login

    The login for the database you wish to run the SQL statement against. If the login is set to 'No Selection', Applications Manager will use the Oracle database login used to connect to the Applications Manager database. Subvars that connect to the Applications Manager database are referred to as plain subvars. If the login you wish to use is not included in the drop-down list, have your Applications Manager administrator check its definition.

    To evaluate dynamic substitution variables, data types, or reports on a database other than the Applications Manager database, the login object must be defined for JDBC use. For more information, see the Administration Guide.

    Use Requestor's Login

    Uses the login assigned to the user who requests an object that includes this subvar. This option is useful if you want to evaluate the subvar based on either a user's view of a database or on different databases depending upon the user. This check box is only active for dynamic substitution variables where a login is specified. If a login is not selected in the requestor's user definition, the login set in the Login field on this window will be used.

    Replacement Values

    Replacement values retrieve information about Applications Manager stored in the Applications Manager database. You can add replacement values to your SQL statement by highlighting a value and clicking Insert. For more information on replacement values, see Replacement Value Descriptions.

    SQL Statement

    The statement should query a database and extract a single (character type) value that will be used for the variable. If the value is a number, you must use the to_char function to convert it to a character. The value returned by the SQL statement must be no longer than 100 characters. The SQL statement cannot be longer than 2000 characters.

    When the Applications Manager automation engine is processing conditions, it stops in the cycle to evaluate each SQL statement. Therefore, it is important that you write concise SQL statements.

    Check SQL

    Applications Manager runs the SQL statement as the Applications Manager Oracle user. The value returned by the SQL statement is displayed below the SQL statement. For more information on modifying data, see Executing Procedures in Dynamic Substitution Variables.