SQL Script

Overview

The SQLScriptWorkEntryExecutor class executes the SQL and PL/SQL statements supplied in the Code Blocks of an Executable. This executable also provides a custom SQL executor to run any SQL statement as part of a conversion run.

Select NONE Target Level mapping and no Target Types for this executable class. The statements to execute must be specified in the Code Blocks text field only.

Statements can be logically separated into different code sequences and executed in that order. Multiple statements can also be combined in one code sequence. If combined, each statement must be terminated by a ; . However, for statements using WITH clauses or PL/SQL statements that can include semi-colons, they are terminated by a / on a separate line.

The rows-affected count for each statement is added to the rows affected count for the task and will be displayed in the Run Summary. A PL/SQL procedure can return a custom rows-affected count through the ? = syntax. For example:

declare
    l_rows_affected number := 0;
begin
    for rec in (select table_name, owner
               from cx_rmb_tables
               where table_class = 'CONFIG')
    loop
        execute immediate 'INSERT INTO '||rec.table_name||
                      ' SELECT * FROM '||rec.owner||'.'||rec.table_name;
        l_rows_affected := l_rows_affected + 1;
        commit;
    end loop;
    ? := l_rows_affected;
end;
/

Pseudo statements

If you want to ignore SQL errors for a portion of the script, you can use the set errors off statement. To cancel the error suppression, use the set errors on statement. These statements do not need to be terminated by a ;.

For example, to drop a table which may or may not exist:

set errors off
drop table MY_TEMP_TABLE cascade constraints;
set errors on
create table MY_TEMP_TABLE (...);

Embedded source queries

In addition to database tables, views, and synonyms, source queries can be embedded and accessed like regular database views. Source query names must be enclosed in {} and be in uppercase. For example:

update cx_adj u
set bill_id = (select nvl(s.bill_id,' ') from {PAYMENT_CORRECTIONS_QUERY} s
              where s.custinvpy = u.cx_adj_id);

In this example, PAYMENT_CORRECTIONS_QUERY is a source query and will be substituted into the update statement at runtime.

Parameters

This executable class accepts the same Parameters in ServiceWorkEntryExecutor with the following additional parameter:

For more information on other variables and syntax that can be used, see Common executable features.