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.
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;
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??
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
IN_VALUE := $id$;
IN_VALUE => IN_VALUE,
OUT_VALUE => OUT_VALUE
Here SAMPLE_AUDIT is the procedure name.
This did not throw any error at all. Nor did it fetch any data.