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

[sc0tt]

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]

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 😕 )

[jcoates_splunk]

I agree, sounds like you should disable query wrapping:
* http://docs.splunk.com/Documentation/DBX/2.3.0/DeployDBX/SQLtipsandtricks#Use_inline_views_.28query_...
* http://docs.splunk.com/Documentation/DBX/2.3.0/DeployDBX/Createandmanagedatabaseinputs
* http://docs.splunk.com/Documentation/DBX/2.3.0/DeployDBX/Troubleshooting#Database_inputs_or_lookups_...
* http://docs.splunk.com/Documentation/DBX/2.3.0/DeployDBX/Commands#dbxquery

[AlexeyNL]

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?