Common Executable Features

The features described here are applicable to most of the executables.

Internal variables

Most of the executable classes support the following internal variables. These variables are replaced with the appropriate value at runtime.

For example, the {$threadCount} and {$threadNumber} variables are used with the ORA_HASH function to divide the rows to delete into thread buckets.

DELETE FROM CX_CONVERTED_ID STGx
WHERE TABLE_NAME = '{targetTable}'
AND NOT EXISTS (SELECT 1 FROM CISADM.{targetTable}
                WHERE {targetPrimaryKeyColumn0} = STG.CX_ID)
AND ORA_HASH(CX_ID, ({$threadCount}-1)) = ({$threadNumber}-1)

Parameter variables

The SQL-based executable classes, ServiceWorkEntryExecutor, RerunnableServiceWorkEntryExecutor, SQLSetWorkEntryExecutor, SQLCursorWorkEntryExecutor, and SQLScriptWorkEntryExecutor, support the parameter variable feature described in this section.

You can substitute a parameter value into generated SQL or a script at runtime by specifying the parameter ID prefixed by # and placed between {...}. You can then set the parameter value in the Task, and that will replace the {#...} characters at runtime.

This allows for the creation of a single Executable for an SQL template or statement that can be slightly modified for different tasks requiring the same basic SQL statement.

For example, to add an optional hint to an SQL statement template, a placeholder can be embedded in the SQL:

delete {#sqlHint} from {targetTable}

On the Executable, define an sqlHint parameter with a default value or a space if you want the default value to be an empty string. For example,

sqlHint = /*+ parallel(4) */

The sqlHint parameter can be overridden on a Task definition that uses this Executable.

When the statement is generated from the template, the {#sqlHint} placeholder will be replaced by /*+ parallel(4) */, or whatever value was specified for sqlHint on the Task. One task might have a parallel hint, whereas another task could have a different parallel degree or a completely different hint.

The same approach can be used in an executable SQL script to parameterize some parts of it. For example, the following PL/SQL anonymous block gathers table statistics on the staging schema STGADM. To account for cases where the schema name is not STGADM, a parameter called schemaName is created with a default value of STGADM, and the {#schemaName} placeholder is coded into the PL/SQL.

declare
    l_rows_affected number := 0;
begin
    for rec in (select distinct table_name from cxt_touched_table order by table_name)
    loop
        DBMS_STATS.gather_table_stats('{#schemaName}', rec.table_name);
        l_rows_affected := l_rows_affected + 1;
    end loop;
    ? := l_rows_affected;
end;

On the task that invokes this executable, the parameter value for schemaName could be changed to another schema name if required, or the default value of STGADM would be used if no task parameter is specified.

Ampersand variables

An SQL value can be substituted into generated SQL or a script at runtime by specifying an SQL expression prefixed by & and placed between {...}. The SQL value selected by the expression will replace the {&...} characters at runtime.

For example, to include the current date in a download task’s file path, the filePath parameter can be defined as:

filePath = {&to_char(sysdate,’YYYYMMDD’)}

Groovy variables

Any executable can call a standalone Groovy method to replace a value in a parameter or Executable Code Block. The placeholder syntax in a parameter or code block is {g:zzz}, where zzz is a Groovy method to be invoked for the value.

The Groovy method itself must be placed in the Task Data Block and identified with the groovy parameter definition in the Data Block.

For example, to inject a date with a special format into a file name, add the following code to your Task Data Block:

filePath = /my-files/my-dated-file-{g:getJulianDate}.csv
groovy =
    def getJulianDate() {
        def sdf = new SimpleDateFormat('yyDDD')
        return sdf.format(new Date())
    }

The filePath parameter in the above example can also be specified in the parameter area of the Task. However, the Groovy code specified by the groovy parameter can only be specified in the Data Block.

When Groovy methods are executed to obtain substitution values in this manner, they are instantiated in their own isolated objects. If these methods happen to be inside a Groovy class that is also used for something like a file upload, they will not be executed in the object of that Groovy class and, therefore, will not have access to any instance variables of that object. However, these methods access the DIH environment as described in the Groovy environment.

Groovy environment

CommonEnv methods

In any Groovy method, the common environmental variables can be accessed through the following CommonEnv methods:

Utility methods

The following utility methods are also provided: