Skip to main content
gdr
Gold ⭐️
January 30, 2020
Solved

QueryDatabaseTable ORA-01722: invalid number using docker

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:

image.pngimage.png

 

The view:

image.pngimage.png

 

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

2020-01-29.png

 

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?

gdr
gdrAnswer
Gold ⭐️
February 5, 2020

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)

3 replies

gdr
gdrAuthor
Gold ⭐️
February 5, 2020

Solved

Translate
cjervis
Community Manager
Community Manager
February 5, 2020

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

Translate
Keep the questions coming
gdr
gdrAuthorAnswer
Gold ⭐️
February 5, 2020

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)

Translate