topic Re: Database Query with Context variable Oracle in Talend Studio

Database Query with Context variable Oracle

Anonymous — Fri, 29 Mar 2019 05:19:26 GMT

Hi!

I have a problem with a query in the tDBInput oracle component, the error is as follows:

Exception in component tDBInput_1 (TASK1_PREVALIDACION_PRES_ARL)

java.sql.SQLSyntaxErrorException: ORA-00933: comando SQL no terminado correctamente

From what I was reading, it is due to the use of context variables in the query, I would like them to help me know what I have wrong in the following query:

"SELECT NUMDOCCIA,CODIGOCIA
FROM (
SELECT 1 AS INDICE,NUMDOCCIA , CODOGOCIA AS CODIGOCIA
FROM ARL_COMPANIA
WHERE DIRECTCIA='"+context.DIRECTORIO_CIA+"'
UNION
SELECT 2 AS INDICE, NUMDOCCIA , CODOGOCIA AS CODIGOCIA
FROM ARL_COMPANIA
WHERE NUMDOCCIA='"+context.NI_ENTIDAD_ARCHIVO+"'
) D1
ORDER BY INDICE
FETCH FIRST 1 ROWS ONLY"

Thanks!

Re: Database Query with Context variable Oracle

manodwhb — Fri, 29 Mar 2019 06:42:34 GMT

@fredypr_03 ,cab you use the below way.

"SELECT NUMDOCCIA,CODIGOCIA
FROM (
SELECT 1 AS INDICE,NUMDOCCIA , CODOGOCIA AS CODIGOCIA
FROM ARL_COMPANIA
WHERE DIRECTCIA=\'"+context.DIRECTORIO_CIA+"\'
UNION
SELECT 2 AS INDICE, NUMDOCCIA , CODOGOCIA AS CODIGOCIA
FROM ARL_COMPANIA
WHERE NUMDOCCIA=\'"+context.NI_ENTIDAD_ARCHIVO+"\'
) D1
ORDER BY INDICE
FETCH FIRST 1 ROWS ONLY"

Re: Database Query with Context variable Oracle

vapukov — Fri, 29 Mar 2019 06:49:05 GMT

what version of Oracle do you use?

FETCH FIRST 1 ROWS ONLY - supported only in Oracle 12

Re: Database Query with Context variable Oracle

vapukov — Fri, 29 Mar 2019 06:51:25 GMT

@manodwhb

single quotes do not need to be escaped

but if run the original query in Oracle 11g, error text will be exactly as in question