Getting Data In

Need to get data from a stored procedure in DB using the data connect app

saipavan
Explorer

I tried various ways to execute a stored procedure using the DB connect app in splunk. But i am not able to do so. The DB connect app is designed to execute sql queries to get the data but it doesnt seem to have a provision for a stored procedure. Can someone pls suggest a way to do it.

Tags (1)

davidpaper
Contributor

Hi,

DB Connect v1 and v2 don't currently support querying stored procedures natively, but there is a work around. Hat tip to Tyler Muth for the code.

In short, you can call PL/SQL packages from a query using DBX 2 as long as you wrap a table function around them. There is no way to call a PL/SQL package, procedure or function directly as DBX 2 only lets you run queries. However, a table function allows you to treat a PL/SQL array of PL/SQL objects as a table row source.

This has been tested this using the latest build of dbx 2 with a db input and as a search using dbxquery.

Using example code on https://oracle-base.com/articles/misc/pipelined-table-functions to test because Tim’s examples are always accurate and usually concise.

Pasting the code so comments can be added:

-- this type encapsulates the columns of a row
CREATE TYPE t_tf_row AS OBJECT (
  id           NUMBER,
  description  VARCHAR2(50)
);
/

-- this is essentially an array of the type above
CREATE TYPE t_tf_tab IS TABLE OF t_tf_row;
/

-- the table function returns table type above instead of returning a scalar of type number or character
-- p_rows is an input parameter. You could have many and use these to pass onto the real PL/SQL you want to call
CREATE OR REPLACE FUNCTION get_tab_tf (p_rows IN NUMBER) RETURN t_tf_tab AS
  l_tab  t_tf_tab := t_tf_tab();
BEGIN
  FOR i IN 1 .. p_rows LOOP
    l_tab.extend;
    l_tab(l_tab.last) := t_tf_row(i, 'Description for ' || i);
  END LOOP;

  -- you could call any PL/SQL package, procedure or function you want here. If that code returns values, you could return them below as l_tab object

  RETURN l_tab;
END;
/

-- this is the query that I can run as a db input or using dbxquery. The “10” is the input parameter to the function. 
SELECT *
FROM   TABLE(get_tab_tf(10))
ORDER BY id DESC;

RogueMrSmith
Engager

Is this ran directly into the application Query? I tested it and it returned 0 rows, are you running this as one large Query? Or are you saving a job then in a separate search calling it??

0 Karma

SrinivasaC
Path Finder
0 Karma

phemanth
Engager

hi

use this for executing stored procedures

search ---- | dbquery master limit=1000 "EXEC sp_readerrorlog 0,1"

araitz
Splunk Employee
Splunk Employee

DBX 1.x does not support stored procedures. We are investigating the possibility of supporting them in a future release of the app.

0 Karma

saipavan
Explorer

Hi, since i wasnt aware if this was such a possibility i tried in whatever way i could. First i created a new dbmon-tail, provided all the details such as the database name, table name and rising column field.

Next in the SQL query section i gave a PL/SQL statement like

DECLARE
IN_VALUE NUMBER;
OUT_VALUE VARCHAR2(200);
BEGIN
IN_VALUE := $id$;

SAMPLE_AUDIT(
IN_VALUE => IN_VALUE,
OUT_VALUE => OUT_VALUE
);
END;

Here SAMPLE_AUDIT is the procedure name.

This did not throw any error at all. Nor did it fetch any data.

0 Karma

ziegfried
Influencer

Could you please add some more details, such as an example of what you've been trying to execute, error messages, details about the database, especially the database type/version.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...