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.
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.
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$;
INVALUE => INVALUE,
OUTVALUE => OUT_VALUE
Here SAMPLE_AUDIT is the procedure name.
This did not throw any error at all. Nor did it fetch any data.
DBX 1.x does not support stored procedures. We are investigating the possibility of supporting them in a future release of the app.
use this for executing stored procedures
search ---- | dbquery master limit=1000 "EXEC sp_readerrorlog 0,1"
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??