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

[maurelio79]

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.

[pmdba · Accepted solution]

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]

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

[pmdba]

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.

[MichaelPriest]

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

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

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

[maurelio79]

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?