QueryDatabaseTable ORA-01722: invalid number using docker

gdr — Thu, 30 Jan 2020 15:37:22 GMT

Hi all!

I have a docker-compose created with an image of apache/nifi 1.10.0 into a linux machine.

Something like this:

version: '2' services: nifi-1: image: 'apache/nifi:1.10.0' hostname: nifi ports: - 8080:8080 - 8082:8082 environment: - NIFI_WEB_HTTP_PORT=8080 - NIFI_ELECTION_MAX_WAIT=1 min - TZ=America/Argentina/Buenos_Aires - LANG=es_AR.UTF-8

Into my development i have a QueryDatabaseTable that read a entire oracle view and then insert this record in other oracle database table.
My problem is that this in docker not run.

If i run this in a local nifi server this run with success.

QueryDatabaseTable configurations:

The view:

The problem (Im trying with a string field and a date field);

The log

2020-01-30 12:26:14,004 ERROR [Timer-Driven Process Thread-10] o.a.n.p.standard.QueryDatabaseTable QueryDatabaseTable[id=72b4c9f0-e289-33d7-f82d-50bb8f23d4f1] Unable to execute SQL select query SELECT TRUNC(FECHA_EVALUACION) FECHA_EVALUACION, CANT_ENCUESTADOS, CANTIDAD_LIDERES, COD_COMPETENCIA, COD_EVALUACION, COD_EVALUADO, COD_EVALUADOR, COD_MODULO, COD_TIPO_EVALUACION, DESC_COMPETENCIA, DESC_EVALUACION, DESC_MODULO, DESC_TIPO_ENCUESTA, DESC_TIPO_EVALUACION, TO_CHAR(FECHA_EXAMEN,'DD/MONTH/YYYY') FECHA_EXAMEN, NUM_FILA_EVALUADO, NUM_FILA_MODULO, NUM_FILA_TIP_EVALUACION, GENERAL_NOTA_FINAL, GENERAL_NOTA_POR_T_EVAL, LIDERAZGO, MODULO_CANTIDAD, MODULO_NOTA, MODULO_NOTA_FINAL, MODULO_NOTA_PONDERADA, MODULO_PONDERACION_TOTAL, NOTA, NRO_ENCUESTA, PONDERACION_FINAL, PONDERACION_FINAL_P_ENC, PONDERACION_MODULO, PONDERACION_MODULO_AJUSTADO, NRO_ENCUESTA_REPETIDA, TIPO_EVALUCION_CANT_P_ENC FROM VW_STG_CUB_EVALUACIONES due to java.sql.SQLSyntaxErrorException: ORA-01722: invalid number : java.sql.SQLSyntaxErrorException: ORA-01722: invalid number java.sql.SQLSyntaxErrorException: ORA-01722: invalid number at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494) at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612) at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:213) at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:37) at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:733) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:904) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1082) at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1276) at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:366) at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:329) at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:329) at org.apache.nifi.processors.standard.AbstractQueryDatabaseTable.onTrigger(AbstractQueryDatabaseTable.java:281) at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1176) at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:213) at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117) at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Caused by: oracle.jdbc.OracleDatabaseException: ORA-01722: invalid number at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)

So i don't know what happen. I have this development with other views and i don't have any problem but with this view is other thing.

Can someone help me?

Re: QueryDatabaseTable ORA-01722: invalid number using docker

gdr — Wed, 05 Feb 2020 14:48:10 GMT

Solved

Re: QueryDatabaseTable ORA-01722: invalid number using docker

cjervis — Wed, 05 Feb 2020 14:55:09 GMT

@gdr Congratulations on resolving your issue. Can you please share the details on how you solved it in case it can be of assistance to others.

Re: QueryDatabaseTable ORA-01722: invalid number using docker

gdr — Wed, 05 Feb 2020 15:23:56 GMT

The nifi database client is different from the one used to develop the application. The specific problem was in a field of a source table of the view where this field was used both to record numerical values and text and on this field the to_number oracle function was used to convert the text to numeric. But since the configuration is different from the docker, the format of numbers was ',.' so the data could not be transformed.

The data in the productive database were somewhat different from those in the development database.
I used a function where i use TO_NUMBER(REPLACE(NVL(TRIM(p_cadena),'0'),'.',',')); to solved this problem. (Only for docker)

question Re: QueryDatabaseTable ORA-01722: invalid number using docker in Support Questions

QueryDatabaseTable ORA-01722: invalid number using docker

Re: QueryDatabaseTable ORA-01722: invalid number using docker

Re: QueryDatabaseTable ORA-01722: invalid number using docker

Re: QueryDatabaseTable ORA-01722: invalid number using docker