"java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended" Exception when a Direct SQL Update Agent Action Containing a Sub Select Query is Executed (Doc ID 2272524.1)

Last updated on MAY 31, 2017

Applies to:

Oracle Transportation Management - Version 6.3.0 and later
Information in this document applies to any platform.

Symptoms

ACTUAL BEHAVIOR
---------------
When configuring an automation agent with a Direct SQL Update (DSU) statement that contains an insert statement with subselects, the execution of the DSU fails with the following exception

Example DSU:

insert into ORDER_RELEASE_INV_PARTY (ORDER_RELEASE_GID,INVOLVED_PARTY_QUAL_GID,INVOLVED_PARTY_CONTACT_GID,COM_METHOD_GID,OVERRIDE_LOCATION_NAME,OVERRIDE_CITY,OVERRIDE_PROVINCE,OVERRIDE_PROVINCE_CODE,OVERRIDE_COUNTRY_CODE3_GID,OVERRIDE_POSTAL_CODE,DOMAIN_NAME) SELECT ORE.ORDER_RELEASE_GID,CIP.INVOLVED_PARTY_QUAL_GID,CIP.CONTACT_GID,CIP.COM_METHOD_GID,null,null,null,null,null,null,CIP.DOMAIN_NAME FROM CORPORATION_INVOLVED_PARTY CIP,LOCATION_CORPORATION LC, ORDER_RELEASE ORE WHERE CIP.CORPORATION_GID = LC.CORPORATION_GID AND LC.LOCATION_GID = ORE.SOURCE_LOCATION_GID AND ORE.ORDER_RELEASE_GID = ?


ERROR
---------------


EXPECTED BEHAVIOR
-----------------------
It is expected to be able to run valid SQL insert statements with subselects as part of an agent's DSU statement.

STEPS
-----------------------
The issue can be reproduced with the following steps:

1. Create an automation agent with a DSU agent action.
2. For this DSU, define a SQL insert statement with sub select query
3. Trigger the automation agent
4. The agent DSU action will fail with the above exception.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms