Archive

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

Path Finder

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

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

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

Path Finder

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

0 Karma

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

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

Path Finder

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