topic Re: Splunk DB Connect 1: How to set Oracle schema in DB connect query? in All Apps and Add-ons

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

maurelio79 — Wed, 10 Jun 2015 13:52:07 GMT

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.

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

MichaelPriest — Wed, 10 Jun 2015 13:57:17 GMT

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

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

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

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

maurelio79 — Wed, 10 Jun 2015 14:18:10 GMT

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?

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

pmdba — Sat, 13 Jun 2015 16:58:03 GMT

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

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

maurelio79 — Tue, 16 Jun 2015 10:35:42 GMT

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

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

pmdba — Sun, 28 Jun 2015 04:32:14 GMT

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.