Splunk Dev

DBConnect and Oracle and ORA-00933: SQL command not properly ended erro

ericsix
Explorer

Greetings All-
Using latest DBConnect in an oracle environment. I have a query that I run and it successfully is returning data via an adhoc query. When I add this query as a input, I am getting the following:

ERROR:TailDatabaseMonitor - Error while executing database monitor: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

Here is the query (data is cleansed..)

SELECT a.MONTH||a.DAY AS "Call_Date",
a.XXY_ID AS "XX_Y" ,
CASE
WHEN a.DISPLY_NUMBER_FLG = '1' THEN '0' || a.disply_number
ELSE a.dis_id
END AS "Telephone_No",
a.XXXXXX_ID AS "Destination_No",
CASE
WHEN substr(a.XXXXXX_ID, 1,3) = '010' THEN NVL(b.COUNTRY_SPLITTING,' ')
ELSE ' '
END AS "Country",
substr(a.TUWA_START_TIME,9,2) || ':' || substr(a.TUWA_START_TIME,11,2) || ':' || substr(a.TUWA_START_TIME,13,2) || '.' || substr(a.TUWA_START_TIME,15,1) AS "Start_Time" ,
substr(a.TUWA_END_TIME,9,2) || ':' || substr(a.TUWA_END_TIME,11,2) || ':' || substr(a.TUWA_END_TIME,13,2) || '.' || substr(a.TUWA_END_TIME,15,1) AS "End_Time",
CASE WHEN TUWA_TIME>=360000 THEN trim(to_char(floor(TUWA_TIME/3600),'000'))
ELSE trim(to_char(floor(TUWA_TIME/3600),'00'))
END ||':'|| trim(to_char(floor((TUWA_TIME-floor(TUWA_TIME/3600)*3600)/60),'00'))||':'|| trim(to_char(TUWA_TIME-floor(TUWA_TIME/60)*60,'00.0')) AS "Call_Time" ,
'5' AS "Time_Zone",
trim(to_char(a.VVVVVVVVV10,'999,999,990.99')) AS "Charges",
a.TUWA_START_TIME AS "StartDateTime"
FROM CDR.KVH_CALL_DATA_T a,
CDR.KVH_MDL_MASTER_T b
WHERE ((a.month = '201307'
AND a.day >=1)
OR (a.month= '201309'
AND a.day <=11))
AND customer_cd='1000000-000'
AND a.prefix = b.code (+)
AND a.y.z = b.y.z(+)
AND a.except_typ = '00'
AND a.VVVVVVVVV10 <> 0
{{WHERE $rising_column$ > ?}}

My $rising_column$ is set as the StartDateTime field.

Going through dbx.log is pretty use..less.

INFO:TailDatabaseMonitor - Applying latest tail.rising.column value=201308032333370

Can see its getting the proper schema and value for the rising column. But after this it errors out with;

bx8559:ERROR:TailDatabaseMonitor - Error while executing database monitor: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791)
.......

Other simple queries running as inputs are working..

Any ideas?

Tags (3)
0 Karma

sowings
Splunk Employee
Splunk Employee

Yes. The rising condition is applied incorrectly, because of the way the statement is written. It's evaluating as WHERE ... AND ... AND ... WHERE rising_condition. That's not valid SQL. Check the answer linked below for a hint; you're going to have to use "custom SQL" for your DB input, but then it should work as expected.

http://answers.splunk.com/answers/98012/dbx-combining-where-in-custom-sql-with-rising-column

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...