CODEWITHSUNDEEP
oracle-databasejdbcconnection-string
netic
netic

Reputation: 2954

Default Schema in Oracle Connection URL

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.

Upvotes: 55

Views: 109495

Answers (7)

Sank
Sank

Reputation: 131

Specifying the schema after the username in the format USERNAME[SCHEMA_NAME] works for me

Example:

spring.datasource.url=jdbc:oracle:thin:@<server>:<port1521>:<sid>
spring.datasource.username=MYUSERNAME[ANIMALS]
spring.datasource.password=******
...

Upvotes: 0

riskop
riskop

Reputation: 1787

If you use oracle.ucp.jdbc.PoolDataSource then you can use the connection validation mechanism:

...
poolDataSource.setValidateConnectionOnBorrow(true);
poolDataSource.setSQLForValidateConnection("ALTER SESSION SET CURRENT_SCHEMA=<SCHEMA_TO_USE_AS_DEFAULT>");
...

Upvotes: 0

Tyutyutyu
Tyutyutyu

Reputation: 369

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.

Upvotes: 5

robothy
robothy

Reputation: 1330

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;

Upvotes: 2

Daniel Worthington-Bodart
Daniel Worthington-Bodart

Reputation: 1964

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"));

Upvotes: 15

Rene
Rene

Reputation: 10551

What about the use of synonyms? 

create synonym monkey for animals.monkey;

select monkey_name from monkey

Upvotes: 12

Gary Myers
Gary Myers

Reputation: 35401

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.

Upvotes: 46

Related Questions