Execute Oracle Procedure from Splunk DB Connect Version:3.6.0

[MikeyD100]

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.

[VatsalJagani]

@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!!

[MikeyD100]

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. 

[VatsalJagani]

@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!!!

[MikeyD100]

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. 

[VatsalJagani]

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