Support Questions
Find answers, ask questions, and share your expertise

Issue in extracting incremental data from Oracle Exadata - ORA-00911: invalid character

Issue in extracting incremental data from Oracle Exadata - ORA-00911: invalid character

Hi,

We are using NiFi 1.13.0 to extract data from Oracle Exadata with a WHERE condition on a timestamp. We are using ExecuteSQL processor with a connector to PutFile processor. The SQL query looks like:

SELECT COL1, COL2 FROM DB1.TABLE1 WHERE RECORD_CREATED_DT >= TO_TIMESTAMP("01.01.1600:00:00","DD.MM.YYHH24:MI:SS")

Error is:

2017-07-28 17:43:00,007 ERROR [Timer-Driven Process Thread-4] o.a.nifi.processors.standard.ExecuteSQL ExecuteSQL[id=84833bc4-015d-1000-7d02-556597053920] Unable to execute SQL select query SELECT COL1, COL2 FROM DB1.TABLE1 WHERE RECORD_CREATED_DT >= TO_TIMESTAMP("01.01.1600:00:00","DD.MM.YYHH24:MI:SS") due to org.apache.nifi.processor.exception.ProcessException: java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

We have already tried out various versions with single quote in the TO_TIMESTAMP function etc, but without any luck. Please point out if there is any issue with the SQL or some other setting that we need to try out. Any guidance will be much appreciated.

Thanks!

2 REPLIES 2

Re: Issue in extracting incremental data from Oracle Exadata - ORA-00911: invalid character

Contributor

@rajalaxmi rath

The problem seems to be syntax error of the Oracle query.

1.) Can you do the following in your oracle database and provide the output.

select TO_TIMESTAMP("01.01.1600:00:00","DD.MM.YYHH24:MI:SS") from dual;

2) What is the data type of the oracle column "RECORD_CREATED_DT" ?

The good example of using TO_TIMESTAMP format is like the below one. I don't know if this format is acceptable in your NIFI processing.

SELECT TO_TIMESTAMP ('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') FROM DUAL;

3) You can also try the following directly in oracle,

SELECT COL1, COL2 FROM DB1.TABLE1 WHERE TO_TIMESTAMP(RECORD_CREATED_DT ,"DD.MM.YY HH24:MI:SS") >= TO_TIMESTAMP("01.01.16 00:00:00","DD.MM.YY HH24:MI:SS");

Re: Issue in extracting incremental data from Oracle Exadata - ORA-00911: invalid character

thanks @Rajesh for your answer