I’m trying to automate an integration test on my backend application against an oracle database. I can bring up a separate docker container with the schema and i can connect to it using the schema owner no problem. My application has a special user (user_app, different from the schema owner) to connect to the database, with limited grants. The profile for normal opertation and deployment looks like this:

spring:
  
  # OracleDB connection settings
  datasource:
    driver-class-name: oracle.jdbc.OracleDriver
    url: jdbc:oracle:thin:@localhost:1521/XEPDB1?useUnicode=yes&characterEncoding=UTF-8
    username: user_app
    password: user_app

Now when I try to test the application using @SpringbootTest I would do something like this in my test profile using the schema owner, otherwise I stumble upon grant issues:

spring:
  
  # OracleDB connection settings
  datasource:
    driver-class-name: oracle.jdbc.OracleDriver
    url: jdbc:oracle:thin:@localhost:1521/XEPDB1?useUnicode=yes&characterEncoding=UTF-8
    username: user
    password: user

And something like this to set up the testcase:

@Sql(scripts = {
    "classpath:/sql/clean_db.sql",
    "classpath:/sql/setup_test_cases.sql"
})
@Sql(scripts = {
        "classpath:/sql/clean_db.sql"
    },
    executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD
)
class APITest {

    @Test
    void testAPI()
    {
        // Call some api method to test against the database
        someAPIMethod();
    }
}

I would like to run the application with the normal limited user (user_app) but run the @Sql parts with the schema owner (user) instead of using the schema owner as I´m doing now for testing. How should I do this?

I tried to add @SqlConfig(dataSource = "schemaOwnerDatasource") by setting up bith datasources in yaml profile:

spring:
  
  # OracleDB connection settings
  datasource:
    driver-class-name: oracle.jdbc.OracleDriver
    url: jdbc:oracle:thin:@localhost:1521/XEPDB1?useUnicode=yes&characterEncoding=UTF-8
    username: user_app
    password: user_app
  schema-owner-datasource:
    driver-class-name: oracle.jdbc.OracleDriver
    url: jdbc:oracle:thin:@localhost:1521/XEPDB1?useUnicode=yes&characterEncoding=UTF-8
    username: user
    password: user

And then adding the beans to a @Configuration class:

@Configuration
class TestDatasourceConfiguration {

@Primary
@Bean(name = "userAppDatasource")
@ConfigurationProperties(prefix="spring.datasource")
public DataSource userAppDatasource() {
    return DataSourceBuilder.create().build();
}

@Bean(name = "schemaOwnerDatasource")
@ConfigurationProperties(prefix="spring.schema-owner-datasource")
public DataSource shcemaOwnerDatasource() {
    return DataSourceBuilder.create().build();
}

}

But then I´m hit with errors on the application failing to connect to the database. What I am missing?