topic Re: Need to get data from a stored procedure in DB using the data connect app in Getting Data In

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

saipavan — Fri, 03 Jan 2014 12:52:41 GMT

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.

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

ziegfried — Fri, 03 Jan 2014 13:00:15 GMT

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.

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

saipavan — Mon, 28 Sep 2020 15:34:22 GMT

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.

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

araitz — Mon, 06 Jan 2014 17:05:23 GMT

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

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

phemanth — Tue, 16 Sep 2014 06:07:42 GMT

use this for executing stored procedures

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

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

SrinivasaC — Thu, 04 Jun 2015 02:59:55 GMT

http://answers.splunk.com/answers/123239/can-db-connect-run-stored-procedures.html

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

davidpaper — Mon, 28 Mar 2016 02:21:57 GMT

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;

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

RogueMrSmith — Mon, 28 Mar 2016 16:58:10 GMT

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??