I am now working with Oracle and Spring JDBC, but I do not want to use the schema in my sql statements:
Example: Select * from SCHEMA.table
Is there any way to set the default schema in application.properties or application.yml?
I'm using spring jdbc, do not hibernate. Hibernate does have to establish that property. But what happens when you use sql statements?
If it were postgreSQL or mySQL you could put the schema by default directly in the url of jbdc, something like this:
jdbc:postgresql://localhost:5432/mydatabase?searchpath=myschema
But not for oracle, :( so you have to look for some workaround , you have several options:
You can tell your bbdd to set a default schema once the session is started:
ALTER SESSION SET CURRENT_SCHEMA = TU_SCHEMA
You can also create synonymous in the bbdd in this way, but if you have many tables it can be cumbersome and complicated to maintain.
create synonym mi_tabla for TU_SCHEMA.tu_tabla;
select * from tu_tabla;
If none of this works for you, here you can find a snippet that manually changes the schema.
From what I see the user you use to connect to the DB is not the same user who owns the tables. Then to solve the problem the DBA or who is in charge of the DB, you must create the synonyms in the tables that you use the user you occupy. with this you can occupy the tables without their owner.
In Oracle, each user has their own schema. That is, a DB_USU user will own a scheme of the same name.
In case that once connected you need to establish another different scheme like the default scheme, you will simply have to execute the query
ALTER SESSION SET CURRENT_SCHEMA=nuevo_esquema
You should only run this after every connection.