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.

[schauder]

I looked at the reproducer and it contains a lot of cruft (vaading, Hibernate, Spring web). Please simplify the reproducer.

Also it requires a database setup that is only described in prose with multiple users. Please provide at very least a complete script that based on a single user, creates the separate schema. Or even better, use Testcontainers to setup the database.

[spring-projects-issues]

If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.