Failed to save record in Oracle with customized schema in NamingStrategy

[Dennis3453]

When I try to connect Oracle with username readonlyuser, and use MYSCHEMA as default schema, findAll() and findById() are working but all repo.save() failed to work and return error "object "MYSCHEMA" does not exist".

Spring Boot version: 2.5.2 & 2.5.4

@Bean
NamingStrategy namingStrategy() {
    return new NamingStrategy() {
        @Override
        public String getSchema() {
            return "MYSCHEMA";
        }
    };
}   

Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO "MYSCHEMA"."CUSTOMER" ("CUSTOMER_ID", "NAME") VALUES (?, ?)]; nested exception is java.sql.SQLSyntaxErrorException: ORA-04043: object "MYSCHEMA" does not exist

	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:991)
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:356)
	at org.springframework.data.jdbc.core.convert.DefaultDataAccessStrategy.executeInsertAndReturnGeneratedId(DefaultDataAccessStrategy.java:148)
	at org.springframework.data.jdbc.core.convert.DefaultDataAccessStrategy.insert(DefaultDataAccessStrategy.java:127)
	at org.springframework.data.jdbc.core.JdbcAggregateChangeExecutionContext.executeInsertRoot(JdbcAggregateChangeExecutionContext.java:94)
	at org.springframework.data.jdbc.core.AggregateChangeExecutor.execute(AggregateChangeExecutor.java:66)
	... 126 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-04043: object "MYSCHEMA" does not exist

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:456)
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:451)
	at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:1040)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268)
	at oracle.jdbc.driver.T4C8Odsy.doODSY(T4C8Odsy.java:146)
	at oracle.jdbc.driver.T4C8Odsy.doODSYTable(T4C8Odsy.java:126)
	at oracle.jdbc.driver.T4CConnection.doDescribeTable(T4CConnection.java:5168)
	at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:4654)
	at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:372)
	at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)
	at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:228)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649)
	... 132 more

[schauder]

By "use MYSCHEMA as default schema" you mean the NamingStrategy you showed, right?

I can't see the mistake in the insert statement. Can you connect to the database with the user readonlyuser and find out how such insert statement should look?

Also showing the SELECT statements that do work might help.

[Dennis3453]

The insert statement from log is correct and can be executed in Oracle SQL developer. Same statement also work with @Query.

@Modifying
@Query("INSERT INTO \"MYSCHEMA\".\"CUSTOMER\" (\"CUSTOMER_ID\", \"NAME\") VALUES (1, 'TEST')")
boolean insert();

[nio-9091-exec-1] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL update
[nio-9091-exec-1] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [INSERT INTO "MYSCHEMA"."CUSTOMER" ("CUSTOMER_ID", "NAME") VALUES (1, 'TEST')]

SELECT statement

repo.findAll();
[nio-9091-exec-2] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL query
[nio-9091-exec-2] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [SELECT "MYSCHEMA"."CUSTOMER"."CUSTOMER_ID" AS "CUSTOMER_ID", "MYSCHEMA"."CUSTOMER"."NAME" AS "NAME" FROM "MYSCHEMA"."CUSTOMER"]

[schauder]

That is ... interesting. I'll need a reproducer to understand what is going on here.

[Dennis3453]

Please try this. Thanks. https://github.com/Dennis3453/my-oracle-svc

[schauder]

I can't access that reproducer. Also the github user doesn't have any public repositories. Is it private maybe?

[Dennis3453]

Already set back to public, is that I can try with this?

[schauder]

Yes, that should work.