QueryDatabaseTable ORA-01722: invalid number using docker

[#1]

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?

[#2]

Solved

[#3]

@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. 

[#4 · Accepted solution]

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)