All Apps and Add-ons

splunk db connect 3.0: invalid column index

snjy991
New Member

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,
alt text

0 Karma

tmuth_splunk
Splunk Employee
Splunk Employee

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

jcoates_splunk
Splunk Employee
Splunk Employee

thanks for the help Tyler!

0 Karma

tmuth_splunk
Splunk Employee
Splunk Employee

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...