All Apps and Add-ons

DB Connect v2 lookup ORA-00933: SQL command not properly ended

sc0tt
Builder

I've successfully created a manual lookup to an Oracle database using the DB Connect v2 app, but when I try to use it I receive the error Script for lookup table 'db_connect_has_plan' returned error code 1. Results may be incorrect. The dbx_error log contains the following error:

java.sql.SQLException: Validate the SQL [SELECT "USER_ID","STATUS" FROM (SELECT "USER_ID","STATUS" FROM "SPLUNK"."PLANS" WHERE "PLAN_ID" = 101 AND "STATUS" = 'active') AS lookuptable WHERE "USER_ID" = ?] failed. java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

The AS in the lookuptable alias is causing the error.

How can I adjust the SQL statement so that it is valid?

Edit: Just to clarify, the query I provide for the lookup is SELECT "USER_ID","STATUS" FROM "SPLUNK"."PLANS" WHERE "PLAN_ID" = 101 AND "STATUS" = 'active'. The SQL that fails validation is generated by the app.

svenkoch
New Member

Take a look at this: DB Connect Troubleshooting Docs . Disabling query wrapping solved the problem for me.

https://answers.splunk.com/answers/425211/db-connect-v2-lookup-ora-00933-sql-command-not-pro.html#an... (site is eating up the link 😕 )

0 Karma

AlexeyNL
Explorer

It seems the bug (one more) in Splunk DB Connect 2 application.

"oracle does not support as for table aliases"

http://stackoverflow.com/questions/9811711/sql-command-not-properly-ended

But DB Connect uses AS keyword for query wrapping:

select field1, field2, field3, etc (
our query
) as lookuptable WHERE "map field" = ?

Has anybody found a workaround?

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...