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

ericsix — Mon, 28 Sep 2020 14:45:38 GMT

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?

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

sowings — Thu, 12 Sep 2013 14:21:54 GMT

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

topic DBConnect and Oracle and ORA-00933: SQL command not properly ended erro in Splunk Dev

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

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