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