ORA-00907: missing right parenthesis Errors When Searching for '_' in DEFQ Form (Doc ID 2183545.1)

Last updated on JANUARY 21, 2017

Applies to:

Oracle Financial Services Analytical Applications Infrastructure - Version 8.0.2 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)
Oracle Financial Services Analytical Applications Infrastructure (OFSAAI)
Data Entry Forms and Queries (DEFQ)

Symptoms

On OFSA 8.0.2, when using a custom data entry form, the forms works, but when using the search the forms fails for some particular characters.

When trying to search in a text file with the character "_" inside the application raise the error:
ORA-00907: missing right parenthesis

Looking the DeFiService.log, the following error appears:

[DEFILOG] Jul 4,16 06:07:18 : [ABSTRACTDATAENTRYREPOSITORY]Query: SELECT MTV_ESTADO_GRUPO.COD_ESQUEMA a_0, MTV_ESTADO_GRUPO.COD_GRUPO a_1, MTV_ESTADO_GRUPO.COD_MALLA a_2, MTV_ESTADO_GRUPO.DES_GRUPO a_3, MTV_ESTADO_GRUPO.URL_ESTADO a_4, MTV_ESTADO_GRUPO.NUM_JOBS_GRUPO a_5, MTV_ESTADO_GRUPO.NUM_JOBS_FINALIZADOS a_6, MTV_ESTADO_GRUPO.NUM_JOBS_EJECUCION a_7, MTV_ESTADO_GRUPO.NUM_JOBS_ERROR a_8, MTV_ESTADO_GRUPO.NUM_ORDEN_GRUPO a_9, MTV_ESTADO_GRUPO.URL_DETALLE_JOB a_10, MTV_ESTADO_GRUPO.URL_DETALLE_PLANIF a_11, MTV_ESTADO_GRUPO.ESTADO_GRUPO a_12 FROM MTV_ESTADO_GRUPO WHERE (UPPER(MTV_ESTADO_GRUPO.COD_GRUPO) like UPPER('%FIL\_D%' ESCAPE '\')) ORDER BY a_2 ASC, a_9 ASC
[DEFILOG] Jul 4,16 06:07:18 : [ABSTRACTDATAENTRYREPOSITORY]Error (#1) at executeQuery()(2): java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthesis


The problem looks like DeFi is not creating the sql properlty in this case, the where clause is:
WHERE (UPPER(MTV_ESTADO_GRUPO.COD_GRUPO) like UPPER('%FIL\_D%' ESCAPE '\'))

but should be like

WHERE (UPPER(MTV_ESTADO_GRUPO.COD_GRUPO) like UPPER('%FIL\_D%' ) ESCAPE '\')

ERROR
[DEFILOG] Jul 4,16 06:07:18 : [ABSTRACTDATAENTRYREPOSITORY]Error (#1) at executeQuery()(2): java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthesis

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