Direct SQL Update Utility Agent Action

This page is accessed via Business Process Automation > Agents and Milestones > Automation Agent.

This agent action executes a SQL DML statement or stored procedure directly against Oracle Transportation Management or related tables. Direct SQL Update supports insert, update, delete and merge statements. For the automatic update of caches, it is recommended that you use DML Returning. This mechanism asks the system to return a list of impacted records. Then the system broadcasts out any needed cache updates based on this list.

Merge does not support DML Returning. If you are using merge, you will need to use another option (e.g. SQL Select) to specify what records were modified.

Update supports DML Returning, but only if the update statement does not use sub-selects to update columns.

Note: Only child elements of the parent object for which the agent runs can be modified. For example, shipment events are not considered children of shipments.

A SQL statement example

update shipment set servprov_gid =

(select sa.servprov_gid

from ie_shipmentstatus iess, servprov_alias sa

where sa.alias = iess.servprov_alias_value and

sa.servprov_alias_qual_gid = iess.servprov_alias_qual_gid and iess.i_transaction_no = $i_transation_no)

where shipment _gid = $shipment_gid

 

A PL/SQL example

call shipment_event.update_servprov($shipment_gid, $i_transaction_no)

Note: The PL/SQL procedures that you enter should not include beginning and ending brackets { } that traditionally begin and end PL/SQL procedures.

If entering a DML statement, enter the statement in the SQL Statement field and select a Statement Type of "Plain SQL Statement". The statement should match one of the following three forms:

• insert into <table> ...
• update <table> ...
• delete from <table> ...

If entering a stored procedure, enter the procedure call in the SQL Statement field and select a Statement Type of "Stored Procedure". The statement should be of the form:

call package.proc(...)

A SQL Description can be associated with each distinct SQL statement to allow an agent user to understand the purpose of the call.

Impact of Direct SQL Update on Oracle Transportation Management Caches

If you are inserting, updating or deleting records from a published table in the Oracle Transportation Management schema, your changes need to be reflected in Oracle Transportation Management business caches. Otherwise, data used by planning, execution and other subsystems may not reflect the data changed in the Direct SQL Update action. Most Oracle Transportation Management tables are not cached and changes made by this action are independent of Oracle Transportation Management. But the use of caches within Oracle Transportation Management varies from release to release and must be accounted for when directly modifying data.

Caching and DML Statements

For DML statements entered as a Plain SQL Statement, Oracle Transportation Management first parses the table name to determine if any business caches could be impacted. If not, the SQL statement is simply executed and control is returned to the agent. If caches are impacted, Oracle Transportation Management determines which records are impacted by the SQL and synchronizes any associated business caches with the database. This synchronization is broadcast to all application servers in a clustered environment to ensure distributed caches are also kept up-to-date.

Several Refresh Cache options are available to determine cache records to synchronize:

1. DML Returning: This is an Oracle JDBC feature that provides Oracle Transportation Management with a list of all records impacted by the SQL execution. This is the default synchronization method and should be used with all plain SQL statements if possible. DML returning is not supported by all SQL statement nor by all JDBC drivers and database versions. In particular, DML returning is not supported by insert statements with subselects. You must select another Refresh Option for these statements.
2. Statement Parsing: This is an Oracle Transportation Management feature that parses simple SQL statements to determine records impacted by the SQL. This option should only be used when DML Returning is unavailable or its performance overhead is unacceptable. Statement parsing supports the following statement forms:

1. 1. • update table where col =
      • update table where col = and col =
      • update table where col = and col = (select col from...)
      • update table where col = and col in (select col from...)
      • insert into table (...) values (...)
      • insert into table (...) select from
      • delete from table where col =
      • delete from table where col = and col =
      • delete from table where col = (select col from...)
      • delete from table where col in (select col from...)

Note:

1. 1. • the primary key column(s) must be fully specified in each form. The parser determines impacted records by extracting or evaluating primary key columns.
      • For forms 1, 2, 5, 6 and 7 no SQL execution is needed. The impacted record can be determined purely by parsing the statement. This may be more efficient than DML Returning.
      • Any update using Statement Parsing should be thoroughly tested, including a monitoring of the exception log. If the parser fails, cache synchronization is delegated to Object Tree.

3. Select SQL: To avoid the limitations of Statement Parsing, you can explicitly inform Oracle Transportation Management which records are impacted by the SQL but specifying an associated select statement in the Select SQL For Cache Refresh field. This statement is of the form:

select column, ... from table where ...

Oracle Transportation Management executes the select statement and builds up a list of impacted records from the result set. Note:

1. • The table specification in the Select SQL must match the table in the SQL Statement.
   • Oracle Transportation Management replaces any column list in the Select SQL with a list of primary key columns

4. Object Tree: If DML Returning and Statement Parsing are unavailable or cannot determine records from the SQL Statement, Oracle Transportation Management defaults to looking at the tree of all business objects associated with the agent. This tree is based on all management screens working on the business object. For example, a SHIPMENT agent works on an Oracle Transportation Management Shipment. Maps governing records associated with the shipment drive various shipment management screens throughout the system. Such records include shipment reference numbers, remarks, involved parties, stops, ship units, etc. The Object Tree option collects all records associated to the shipment and synchronize caches impacted by any of the records. In general, Object Tree analysis should be avoided. It is kept primarily for backward compatibility.

Note:

1. • Object Tree adds significant overhead to the DIRECT SQL UPDATE process. It forces all descendant records of an agent's business object to be queried from the database.
   • Object Tree may not properly synchronize all impacted caches. If a SHIPMENT agent, for example, modifies order information, the order records are not considered part of the shipment tree. Their caches, if any, will not be synchronized.

Caching and Stored Procedures

For stored procedures, Oracle Transportation Management cannot assess the impact to cached records. It is the agent writer's responsibility to inform Oracle Transportation Management of any records inserted, updated or deleted by the stored procedure - if these records are in the published set of Oracle Transportation Management tables.

Several Refresh Cache options are available to inform Oracle Transportation Management which cache records to synchronize:

1. Select SQL: You can explicitly inform Oracle Transportation Management which records are impacted by the SQL by specifying an associated select statement in the Select SQL For Cache Refresh field. This statement is of the form:

select column, ... from table where ...

Oracle Transportation Management executes the select statement and builds up a list of impacted records from the result set.

Note:

1. • Oracle Transportation Management uses the table specification in the Select SQL to determine if any associated cache exists.
   • Oracle Transportation Management replaces any column list in the Select SQL with a list of primary key columns.
   • The stored procedure cannot modify records from different tables as Select SQL can only inform Oracle Transportation Management of the impacts on one of them.

2. Object Tree: If the stored procedure impacts multiple tables, you can specify the Object Tree option for synchronization. This looks at the tree of all business objects associated with the agent. See the above discussion of Object Tree for DML statements for more information.
3. None: If the stored procedure does not update records in Oracle Transportation Management tables, you can suppress any cache analysis by specifying None as a Refresh Cache option. Note that, by default, this option is only available for stored procedures. It can be enabled for DML statements for backward compatibility, but allows for the possibility of unsynchronized caches.