splunk db connect 3.0: invalid column index

[snjy991]

Hi
we want to call a SP in splunk db connect 3 and I referred below splunk document

http://docs.splunk.com/Documentation/DBX/3.0.0/DeployDBX/Commands#For_upgraders

and by referring that document we used below query

dbxquery procedure="{call schemaname.procedure_name(?, ?)}" connection="Oracle_dev_db" params="args1,args2"
but getting Invalid column index
but the same query is working for
***dbxquery query="select * from schemaname.tablename where x > ? and y = ?"* connection="Oracle_dev_db" params="arg1,arg2"**

can anyone please help me what we are missing. or any workaround for that?
thanks,

[tmuth_splunk]

We updated the documentation for this was well today:
https://docs.splunk.com/Documentation/DBX/3.0.2/DeployDBX/Commands

[jcoates_splunk]

thanks for the help Tyler!

[tmuth_splunk]

The documentation is a bit lacking on this currently which I'll work to resolve. In short:

• The first parameter in your stored procedure has to be an out parameter of type SYS_REFCURSOR.
• The number of parameters in the procedure argument to dbxquery is the same as the actual parameters in your PL/SQL proc.
• The number of parameters in the parameter argument to dbxquery is n-1. You omit the out parameter.

So, if you have the following procedure:

CREATE OR REPLACE PROCEDURE ref_cur_test(
  p_ref_cursor  OUT SYS_REFCURSOR, p_var_in IN VARCHAR)
AS
BEGIN
 OPEN p_ref_cursor FOR
 SELECT 'you passed-in: '|| p_var_in out_var FROM dual;
END ref_cur_test;
/

You would call it like this:

| dbxquery connection=splunk_test procedure="{call ref_cur_test(?,?) }" params="foo"

or this:

| makeresults count=1
| eval test="foo"
| map search="| dbxquery connection=splunk_test procedure=\"{call ref_cur_test(?,?) }\" params=\"$test$\" "

I put together some examples here: https://github.com/tmuth/splunking-oracle/tree/master/Misc/Stored%20Procedure