Splunk Search

Execute Oracle Procedure from Splunk DB Connect Version:3.6.0

MikeyD100
Explorer

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.

Labels (1)
0 Karma

VatsalJagani
SplunkTrust
SplunkTrust

@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

VatsalJagani_0-1681881635549.png

 

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

0 Karma

MikeyD100
Explorer

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. 

0 Karma

VatsalJagani
SplunkTrust
SplunkTrust

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

Tags (1)
0 Karma

MikeyD100
Explorer


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.

Screenshot 2023-10-25 133951.png

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

Screenshot 2023-10-25 134641.png

Which matches what I see when the SQL run directly from the server
Screenshot 2023-10-25 134755.png

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

0 Karma

VatsalJagani
SplunkTrust
SplunkTrust

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

 

0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...