Using SQL Keywords in a Dynamic Approver Group Query Gives an Error (Doc ID 1598648.1)

Last updated on JULY 12, 2016

Applies to:

Oracle Approvals Management - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

Creating a dynamic approval group in AME. When using a string which contains a SQL keyword followed by a space,


For example

'Asset '
(ie ... ASSET followed by a space)

anywhere within the query string, you get an error when attempting to save.  Unable to continue.

When clicking on the [Validate] button though, the SQL validates fine with no errors.




Steps to Reproduce:


> Approvals Management Business Analyst
> Transaction Type = OIE Expense Reports
> Approver Groups
> [Create]
 - Name = Testing
 - Description = Testing
 - Order Number = 4
 - Voting Method = Serial
 - Usage Type = Dynamic
 - Query = select 'Asset' from dual
> [Apply]
!!! SAVES SUCCESSFULLY !!!
> [Update]
 - Query = select 'Asset ' from dual
!!! ERROR RECEIVED !!!


TESTING RESULTS

ERROR TESTS
select 'Asset ' from dual;
select 'asdfasset ' from dual;
select ' Asset ' from dual;
select 'Asset' from dual;

GOOD TESTS
select ' Asset' from dual;
select 'Asset'||' ' from dual


WORKAROUND

Put a double pipe || (text concatenation) to break up the text.
ie ... instead of using
  select 'Asset ' from dual;
use the following
  select 'Asset'||' ' from dual;

 

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