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!

Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...