51

I'd like to set default database schema in Oracle Connection URL

jdbc:oracle:thin:@<server>:<port1521>:<sid>

My sample SQL statement:

select monkey_name from animals.monkey

I need to query database without schema prefix animals. i.e. when I run this statement

select monkey_name from monkey

it will use animals schema by default.

What do I need to specify in connection URL above get such effect?

Thanks.

techGaurdian
  • 732
  • 1
  • 14
  • 35
netic
  • 2,854
  • 6
  • 28
  • 25

5 Answers5

45

You can't put anything in the connection URL.

In Oracle each user has their own schema (even if doesn't contain any objects) and that is their default schema. Once logged in/connected, they can change their default schema with an

ALTER SESSION SET CURRENT_SCHEMA=animals

So you'd need to do the extra statement after connecting. It is possible to have a logon trigger on the user and/or database that will run this when they log in. I'd personally prefer an explicit statement when an application connects.

Gary Myers
  • 34,963
  • 3
  • 49
  • 74
  • This looks like something I can work around it. Create logon trigger with this statement might be my possible solution. Thanks. – netic Mar 01 '10 at 21:43
  • 1
    I can now see that, this should be a solution. By having this logon trigger, I don't have to modify my sql. So all generic statements are still portable. – netic Mar 02 '10 at 05:18
14

If you use C3PO you can make it do it when it checks the connection out.

As properties:

c3p0.preferredTestQuery=alter session set current_schema=animals
c3p0.testConnectionOnCheckout=true

As Java code:

ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setPreferredTestQuery("alter session set current_schema=animals");
dataSource.setTestConnectionOnCheckout(true);

Downside is this will happen every time the connection is taken out of the pool.

If you are using a JDBC connection yourself you could just do:

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connection = getConnection("jdbc:oracle:thin:@//server:1521/instance",   "username", "password");
connection.createStatement().execute("alter session set current_schema=animals"));
Daniel Worthington-Bodart
  • 1,854
  • 19
  • 27
  • Do I need to give any privilege to instance user? I am accessing oracle via localhost, `"jdbc:oracle:thin:@localhost:1521:xe"` this connection works, but when I try to change xe with created user it fails to connect with error `ORA-12505, TNS:listener does not currently know of SID given in connect descriptor` – user3153014 May 31 '18 at 16:39
12

What about the use of synonyms? 

create synonym monkey for animals.monkey;

select monkey_name from monkey
Rene
  • 10,391
  • 5
  • 33
  • 46
  • It's a good idea. However I find it is not definite, I am not sure if there are any side effects that could break things along the way. – netic Mar 01 '10 at 21:41
  • 2
    This is usually the much better choice over altering the session since it shifts the problem from a behavioural aspect to a structural, which is much more robust. – mwhs Feb 06 '14 at 16:54
  • Although this does work for most cases, in many corporate environments there will exist multiple application schemas in a single database. there will be a namespace collision if the original table names aren't created using prefixes. `e.g. MY_APP_PREFIX_USER` (usually more succint) many people find this ugly and would prefer the concept of a default schema over synonyms. – coderatchet May 23 '17 at 05:41
  • coderathet, that would be true if you used public synonyms. It is always advisable to use private synonyms as far as possible, which avoids such name collisions. – Amit Naidu Jun 19 '18 at 23:44
3

Since Java 1.7 there is a setSchema method on java.sql.Connection.

In the Oracle's oracle.jdbc.driver.PhysicalConnection implementation this method execute the alter session set current_schema = ? statement.

Tyutyutyu
  • 171
  • 2
  • 6
2

You can create a trigger using connection DB user to change the current schema.

create or replace trigger SET_SCHEMA_AFTER_LOGON
    after logon on database
begin
     execute immediate 'alter session set CURRENT_SCHEMA=animals';
end SET_SCHEMA_AFTER_LOGON;
robothy
  • 1,132
  • 11
  • 18