topic Re: Execute Oracle Procedure from Splunk DB Connect Version:3.6.0 in Splunk Search

Execute Oracle Procedure from Splunk DB Connect Version:3.6.0

MikeyD100 — Tue, 18 Apr 2023 13:40:11 GMT

I am using DB Connect Version:3.6.0

I am trying to execute an Oracle procedure from DB Connect

The Oracle procedure returns a number of REF CURSORS but has no in parameters

create or replace PROCEDURE GET_SOME_LOG_MONITORING
(po_error_log_details_01 out SYS_REFCURSOR,
po_error_log_details_02 out SYS_REFCURSOR,
po_error_log_details_03 out SYS_REFCURSOR,
po_error_log_details_04 out SYS_REFCURSOR,
po_error_log_details_05 out SYS_REFCURSOR,
po_error_log_details_06 out SYS_REFCURSOR,
po_error_log_details_07 out SYS_REFCURSOR,
po_error_log_details_08 out SYS_REFCURSOR
)
AS .......

However I can't seem to call the procedure from DB Connect successfully

| dbxquery connection="SOME-CONNECTION" procedure="{call SOMESCHEMA.GET_SOME_LOG_MONITORING(?,?,?,?,?,?,?,?) }"

However when I run this I get the following error

java.sql.SQLException: Missing IN or OUT parameter at index:: 2

Which suggests I am missing an input parameter but I don't have any input parameter that needs to be passed in.

The procedure can be successfully executed from within SQL developer so I know that the procedure works fine. It just seems to be an issue when I try to call this from Splunk DB Connect.

Also how would I go about getting this set up as an input in Spunk DB Connect so that it executes the procedure every 60mins and then anything that is returned is indexed so that I can create an alert on the back of this.

Re: Execute Oracle Procedure from Splunk DB Connect Version:3.6.0

VatsalJagani — Wed, 19 Apr 2023 05:21:03 GMT

@MikeyD100 - I'm no expert with DB connect or Oracle, but I think you need to pass params.

Kindly check the documentation - https://docs.splunk.com/Documentation/DBX/3.12.2/DeployDBX/Commands

I hope this helps!!! Kindly upvote if it does!!

Re: Execute Oracle Procedure from Splunk DB Connect Version:3.6.0

MikeyD100 — Wed, 25 Oct 2023 11:26:09 GMT

Hi @VatsalJagani ,

Sorry for the delay in coming back to you on this to you.

I have read this documentation and got the procedure to only return one OUT REFCURSOR. I don't need to pass in anything so there is no need pass params.

create or replace PROCEDURE GET_SOME_LOG_MONITORING
(po_error_log_details_01 out SYS_REFCURSOR
)
AS.........

I can now successfully call the procedure using the below however I get no data returned from the SYS_REFCURSOR

| dbxquery connection="SOME-CONNECTION" procedure="{call ISOMESCHEMA.GET_SOME_LOG_MONITORING(?)}"

However when I get a DBA to log into the database directly on the server with the same user that Splunk is using to execute the PROCEDURE they get the following results returned.

TIMESTAMP
---------------------------------------------------------------------------
CORRELATION
--------------------------------------------------------------------------------
TO_CHAR(MSGXML)
--------------------------------------------------------------------------------
25-OCT-23 11.33.40.968589 AM
a2306D43d6606aa67f8jgrg21
{"errors":[{"code":"500.12.004","description":"ProviderServiceFailure"}]}

25-OCT-23 11.33.44.569205 AM
a2306D43d6606aa67f8jgrg21
{"errors":[{"code":"500.12.004","description":"ProviderServiceFailure"}]}

TIMESTAMP
---------------------------------------------------------------------------
CORRELATION
--------------------------------------------------------------------------------
TO_CHAR(MSGXML)
--------------------------------------------------------------------------------

25-OCT-23 11.33.47.144192 AM
a2306D43d6606aa67f8jgrg21
{"errors":[{"code":"500.12.004","description":"ProviderServiceFailure"}]}

25-OCT-23 11.33.49.823233 AM
a2306D43d6606aa67f8jgrg21

TIMESTAMP
---------------------------------------------------------------------------
CORRELATION
--------------------------------------------------------------------------------
TO_CHAR(MSGXML)
--------------------------------------------------------------------------------
{"errors":[{"code":"500.12.004","description":"ProviderServiceFailure"}]}

25-OCT-23 11.33.51.383443 AM
a2306D43d6606aa67f8jgrg21
{"errors":[{"code":"500.12.004","description":"ProviderServiceFailure"}]}

25-OCT-23 11.33.52.708949 AM

Splunk has the all the permissions to successfully run the PROCEDURE as proved by this being run directly on the server and data is being returned but when I execute this from the Splunk nothing is returned.

Re: Execute Oracle Procedure from Splunk DB Connect Version:3.6.0

VatsalJagani — Wed, 25 Oct 2023 12:05:25 GMT

@MikeyD100 - In your query you have (?) question mark which DB connect understands as param replacement. I don't know if that is intended. If not please try updating the query accordingly.

I hope that helps!!!

Re: Execute Oracle Procedure from Splunk DB Connect Version:3.6.0

MikeyD100 — Wed, 25 Oct 2023 12:58:41 GMT

As per the documentation you need to have the (?) when using Oracle, as I understand it that is for the OUT REFCURSOR, if I take it out I get an error.

Plus when there is no data to return I get this from Splunk so it works with (?)

Which matches what I see when the SQL run directly from the server

The problem seems to be when there is data to be returned but I am not sure what the issue is.

Re: Execute Oracle Procedure from Splunk DB Connect Version:3.6.0

VatsalJagani — Wed, 25 Oct 2023 15:40:34 GMT

@MikeyD100 - I would suggest to create a customer support case with Splunk.