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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...