1

Hi I am trying to configure the quarkus to connect to a oracle database. With the current configuration I am able to connect to the database, but I cannot specify the current schema.

I followed the documentation and try to use the new-connection-sql to set the current schema. But it doesn't seems to work.

quarkus.datasource.mydatasource.new-connection-sql=ALTER SESSION SET CURRENT_SCHEMA=SCHEMA_NAME

Here is my application.properties file

quarkus.datasource.mydatasource.db-kind=oracle
quarkus.datasource.mydatasource.jdbc.driver=oracle.jdbc.driver.OracleDriver
quarkus.datasource.mydatasource.jdbc.url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.15.73.140)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=SN)))
quarkus.datasource.mydatasource.jdbc.min-size=3
quarkus.datasource.mydatasource.jdbc.max-size=20
quarkus.datasource.mydatasource.username=username
quarkus.datasource.mydatasource.password=password
quarkus.datasource.mydatasource.new-connection-sql=ALTER SESSION SET CURRENT_SCHEMA=SCHEMA_NAME

What could be the issue here?

Thank you.

Kosala Lakshitha
  • 37
  • 2
  • 10
  • 1
    I wouldn't know, I've never even heard about "quarkus". However: if you can connect to the database, that's also your "current" schema, isn't it? It looks as if you'd want to alter session and set another schema to be current. How about a workaround? Directly connect to that "new"/"different" schema and it'll then become current so you'll avoid ALTER SESSION. By the way, *maybe* your problem has something to do with command being a DDL. For example, in Oracle, to run it from PL/SQL you need to use dynamic SQL. Perhaps "quarkus" requires something like that as well? – Littlefoot Jun 28 '21 at 06:04
  • What are you trying to accomplish specifically, by changing the current_schema? Is this for security, or convenience, or something else? – pmdba Jun 28 '21 at 10:33
  • actually its for both. all of my database objects belong to one schema. So when a user logged in we need to set the schema to be able to access the database object. – Kosala Lakshitha Jun 29 '21 at 03:08

2 Answers2

2

This is working fine if you add the jdbc sub path name to the property

quarkus.datasource.mydatasource.jdbc.new-connection-sql=ALTER SESSION SET CURRENT_SCHEMA=SCHEMA_NAME

You can refer to these Quarkus configuration references:

Mathieu Allain
  • 339
  • 1
  • 4
  • 13
KATHUK
  • 36
  • 2
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 15 '21 at 17:04
0

You could try to set the schema in the connection url. But what you are trying to archieve, basically routing each user request to a specific schema, you should check the hibernate multitenancy support by this means, you can route each request to the database you want, but beware of the limitations regarding the parameters you can work with to know where to route your request.

Also check hibernate catalog and schema configuration parameters

Javier Toja
  • 1,630
  • 1
  • 14
  • 31