Oracle Test Container throwing SQLSyntaxErrorException: ORA-01918: user does not exist

Question

I am new to Oracle Container and I'm having a test container to open 2 database connections since I have 2 datasource in my spring boot application.

This is the abstract class where I create Oracle container instances:

@Slf4j
public class AbstractContanierBaseTest {

    static final OracleContainer ORACLE_OPIOWNER_CONTANINER;
    static final OracleContainer ORACLE_FIRMAOWNER_CONTANINER;

    static {
        ORACLE_OPIOWNER_CONTANINER = new OracleContainer("gvenzl/oracle-xe:21-slim");
        ORACLE_OPIOWNER_CONTANINER
                .withUsername("opiowner")
                .withPassword("password")
                .withDatabaseName("OPIOWNER")
                .withExposedPorts(1521)
                .withLogConsumer(new Slf4jLogConsumer(log))
                .withEnv("ORACLE_DATABASE", "OPIOWNER")
                .withEnv("ORACLE_PASSWORD", "password")
                .withEnv("APP_USER", "opiowner")
                .withEnv("APP_USER_PASSWORD", "password")
                .usingSid();
        ORACLE_OPIOWNER_CONTANINER.start();

        System.setProperty("spring.datasource.driver-class-name", ORACLE_OPIOWNER_CONTANINER.getDriverClassName());
        System.setProperty("spring.datasource.url", ORACLE_OPIOWNER_CONTANINER.getJdbcUrl());
        System.setProperty("spring.datasource.username", ORACLE_OPIOWNER_CONTANINER.getUsername());
        System.setProperty("spring.datasource.password", ORACLE_OPIOWNER_CONTANINER.getPassword());

        ORACLE_FIRMAOWNER_CONTANINER = new OracleContainer("gvenzl/oracle-xe:11-slim");
        ORACLE_FIRMAOWNER_CONTANINER
                .withUsername("firmaowner")
                .withPassword("password")
                .withDatabaseName("FIRMAOWNER")
                .withLogConsumer(new Slf4jLogConsumer(log))
                .withEnv("ORACLE_PASSWORD", "password")
                .withEnv("APP_USER", "firmaowner")
                .withEnv("APP_USER_PASSWORD", "password")
                .usingSid();
        ;
        ORACLE_FIRMAOWNER_CONTANINER.start();

        System.setProperty("spring.second-datasource.url", ORACLE_FIRMAOWNER_CONTANINER.getJdbcUrl());
        System.setProperty("spring.second-datasource.username", ORACLE_FIRMAOWNER_CONTANINER.getUsername());
        System.setProperty("spring.second-datasource.password", ORACLE_FIRMAOWNER_CONTANINER.getPassword());

    }

This class inherited by my integration tests written in junit5.

When executing junits an Sql script is launched before tests by means of @Sql annotation:

@Sql(config = @SqlConfig(transactionManager = "opiownerTransactionManager"), scripts = {"classpath:scripts/ddl/Recall_create_table_opiConfluenzaDati.sql", "classpath:scripts/dml/Recall_opiConfluenzaDati_script.sql"})

The script is executed properly, but I receive an oracle SqlSyntaxException as per Question title.

This is my create table script:

CREATE TABLE opiowner.OPI_CONFLUENZA_DATI
(
    "OPI_ANNO_EMISSIONE_TITOLO" NUMBER(4,0),
    "OPI_TIPOLOGIADISPOSIZIONE" VARCHAR2(11),
    "OPI_DATADISPOSIZIONE" DATE,
    "OPI_ID_DISPOSIZIONE" NUMBER(10,0) NOT NULL,
    "OPI_NUM_TITOLO" NUMBER(9,0),
    "OPI_PROG_QUOTA" NUMBER(15,0),
    "OPI_TIPO_COD_BENEF" VARCHAR2(2),
    "OPI_CODICE_BENEF" VARCHAR2(16),
    "OPI_TIPO_RECORD" VARCHAR2(11),
    "OPI_SPECIE_TITOLO" VARCHAR2(2),
    "OPI_TIPO_SPESA" VARCHAR2(5),
    "OPI_COD_ENTE" VARCHAR2(15),
    "OPI_IMPORTO" NUMBER(15,3),
    "OPI_NOME_FILE" VARCHAR2(100),
    "OPI_ORDINANTE" VARCHAR2(100)
);

ALTER TABLE opiowner.OPI_CONFLUENZA_DATI
ADD CONSTRAINT OPI_CONFLUENZA_DATI_PK PRIMARY KEY ("OPI_TIPOLOGIADISPOSIZIONE", "OPI_DATADISPOSIZIONE", "OPI_ID_DISPOSIZIONE");

As you may guess I need the 'opiowner' user to be present in the database. In particular in the first instance of the Oracle container: ORACLE_OPIOWNER firstly.

Question: How can I create 'opiowner' user in a Oracle Container instance?

I thought methods withUsername and withPassword would do the trick, but they do not...

So as you can see with method withEnv I tried to override environment variables of the Docker image, but again no way...

How to fix this issue?

Answer 1

I guess I found the issue that you are facing. Kindly go through this Github issue below:

[OracleContainer] Unable to execute statements which need system privileges

Note: I'm not a DB Expert.

Based on the information provided there, I've formed my answer which is as follows:

• For an oracle-xe image, now the default user and schema is now "TEST".

• I guess you won't be able to create any random user where you don't have the privileges. To overcome that, you have to use TEST user which is kind of acts as a super-user for test containers (Oracle Test containers uses/detects TEST as the default user).

• So, what can I suggest is to create an init.sql some like this below:

  CREATE USER OPIOWNER IDENTIFIED BY password;
  GRANT CONNECT TO OPIOWNER;
  GRANT SYSDBA TO OPIOWNER;
  GRANT CREATE SESSION GRANT ANY PRIVILEGE TO OPIOWNER;
  GRANT UNLIMITED TABLESPACE TO OPIOWNER;
  

• Update AbstractContanierBaseTest class to some like this:

    @Slf4
    public class AbstractContanierBaseTest {
  
    static final OracleContainer ORACLE_OPIOWNER_CONTANINER;
    static final OracleContainer ORACLE_FIRMAOWNER_CONTANINER;
  
    static {
        ORACLE_OPIOWNER_CONTANINER = new OracleContainer("gvenzl/oracle-xe:21-slim");
        ORACLE_OPIOWNER_CONTANINER
                .withUsername("test")
                .withInitScript("init.sql");
                .usingSid();
        ORACLE_OPIOWNER_CONTANINER.start();
  
        System.setProperty("spring.datasource.driver-class-name", ORACLE_OPIOWNER_CONTANINER.getDriverClassName());
        System.setProperty("spring.datasource.url", ORACLE_OPIOWNER_CONTANINER.getJdbcUrl());
        System.setProperty("spring.datasource.username", ORACLE_OPIOWNER_CONTANINER.getUsername());
        System.setProperty("spring.datasource.password", ORACLE_OPIOWNER_CONTANINER.getPassword());
  
        ORACLE_FIRMAOWNER_CONTANINER = new OracleContainer("gvenzl/oracle-xe:11-slim").withCopyFileToContainer(MountableFile.forClasspathResource("init.sql"), "/container-entrypoint-startdb.d/init.sql");
        ORACLE_FIRMAOWNER_CONTANINER
                .withUsername("firmaowner")
                .withPassword("password")
                .withDatabaseName("FIRMAOWNER")
                .withLogConsumer(new Slf4jLogConsumer(log))
                .withEnv("ORACLE_PASSWORD", "password")
                .withEnv("APP_USER", "firmaowner")
                .withEnv("APP_USER_PASSWORD", "password")
                .usingSid();
        ;
        ORACLE_FIRMAOWNER_CONTANINER.start();
  
        System.setProperty("spring.second-datasource.url", ORACLE_FIRMAOWNER_CONTANINER.getJdbcUrl());
        System.setProperty("spring.second-datasource.username", ORACLE_FIRMAOWNER_CONTANINER.getUsername());
        System.setProperty("spring.second-datasource.password", ORACLE_FIRMAOWNER_CONTANINER.getPassword());
  
    }
  

• Now, update the create table script to this:

    ALTER SESSION SET CONTAINER=<<NAME OF TEST CONTAINER>>;
  
    CREATE TABLE TEST.OPI_CONFLUENZA_DATI
    (
        "OPI_ANNO_EMISSIONE_TITOLO" NUMBER(4,0),
        "OPI_TIPOLOGIADISPOSIZIONE" VARCHAR2(11),
        "OPI_DATADISPOSIZIONE" DATE,
        "OPI_ID_DISPOSIZIONE" NUMBER(10,0) NOT NULL,
        "OPI_NUM_TITOLO" NUMBER(9,0),
        "OPI_PROG_QUOTA" NUMBER(15,0),
        "OPI_TIPO_COD_BENEF" VARCHAR2(2),
        "OPI_CODICE_BENEF" VARCHAR2(16),
        "OPI_TIPO_RECORD" VARCHAR2(11),
        "OPI_SPECIE_TITOLO" VARCHAR2(2),
        "OPI_TIPO_SPESA" VARCHAR2(5),
        "OPI_COD_ENTE" VARCHAR2(15),
        "OPI_IMPORTO" NUMBER(15,3),
        "OPI_NOME_FILE" VARCHAR2(100),
        "OPI_ORDINANTE" VARCHAR2(100)
     );
  
     ALTER TABLE TEST.OPI_CONFLUENZA_DATI
     ADD CONSTRAINT OPI_CONFLUENZA_DATI_PK PRIMARY KEY ("OPI_TIPOLOGIADISPOSIZIONE", "OPI_DATADISPOSIZIONE", "OPI_ID_DISPOSIZIONE");
  

See if this changes work. That's all. Look at that github issue for more info.