All Apps and Add-ons

Splunk DB Connect 1: How to set Oracle schema in DB connect query?

maurelio79
Communicator

Hi to all, i'd like to make a query to Oracle DB setting the schema in the query: how can i do it?
If i try:

    ALTER SESSION SET CURRENT_SCHEMA='my_schema'
    SELECT * FROM my_table

i get this error:

command="dbquery", A database error occurred: ORA-02421: missing or invalid schema authorization identifier

Can someone help me?
Regards.

0 Karma
1 Solution

pmdba
Builder

DB Connect can only issue "SELECT" commands on read-only connections. On connections which allow writes, they can also issue INSERT, UPDATE, and DELETE, but that is the extent of DBX's capability. All other SQL commands are forbidden, and no semi-colons are allowed, so you can't script multiple commands together. If you need to change the schema name used in your query, you will have to embed it explicitly in the FROM clause:

select * from schema1.table1

View solution in original post

pmdba
Builder

DB Connect can only issue "SELECT" commands on read-only connections. On connections which allow writes, they can also issue INSERT, UPDATE, and DELETE, but that is the extent of DBX's capability. All other SQL commands are forbidden, and no semi-colons are allowed, so you can't script multiple commands together. If you need to change the schema name used in your query, you will have to embed it explicitly in the FROM clause:

select * from schema1.table1

maurelio79
Communicator

Thanks very much!. Just a question: how to know Oracle client version used by Splunk?
Regards.

0 Karma

pmdba
Builder

By default Splunk uses JDBC-Thin drivers, and doesn't require the full Oracle client. It can also use JDBC-Thick connections, using the full OCI client, but you would have to install it yourself and would presumably know the version.

0 Karma

MichaelPriest
Communicator

A few suggestions here, if you haven't seen them already:

https://community.oracle.com/thread/2270534

https://community.oracle.com/thread/595554

0 Karma

maurelio79
Communicator

I tryed also without single quotes and i get

A database error occurred: ORA-00922: missing or invalid option

But now i have a doubt: the username that i use for the query must have the same name of the schema?

0 Karma
Get Updates on the Splunk Community!

New Splunk Observability innovations: Deeper visibility and smarter alerting to ...

You asked, we delivered. Splunk Observability Cloud has several new innovations giving you deeper visibility ...

Synthetic Monitoring: Not your Grandma’s Polyester! Tech Talk: DevOps Edition

Register today and join TekStream on Tuesday, February 28 at 11am PT/2pm ET for a demonstration of Splunk ...

Instrumenting Java Websocket Messaging

Instrumenting Java Websocket MessagingThis article is a code-based discussion of passing OpenTelemetry trace ...