Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

QueryDatabaseTable ORA-01722: invalid number using docker

Solved Go to solution

QueryDatabaseTable ORA-01722: invalid number using docker

New Contributor

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?

1 ACCEPTED SOLUTION

Accepted Solutions

Re: QueryDatabaseTable ORA-01722: invalid number using docker

New Contributor

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 3

Re: QueryDatabaseTable ORA-01722: invalid number using docker

New Contributor

Solved

Highlighted

Re: QueryDatabaseTable ORA-01722: invalid number using docker

Community Manager

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



Cy Jervis, Community Manager

Was your question answered? Make sure to mark the answer as the accepted solution.
If you find a reply useful, say thanks by clicking on the thumbs up button.

Learn more about the Cloudera Community:
Community Guidelines
How to use the forum

Re: QueryDatabaseTable ORA-01722: invalid number using docker

New Contributor

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)

Don't have an account?
Coming from Hortonworks? Activate your account here