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.

我是Oracle容器的新手，我有一个测试容器来打开2个数据库连接，因为我的Spring启动应用程序中有2个数据源。

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

这是我创建Oracle容器实例的抽象类：

@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.

这个类由我用junit5编写的集成测试继承。

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

执行junits时，通过@ Sql annotation在测试前启动Sql脚本：

@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.

脚本执行正确，但我根据问题标题收到oracle SqlSyntaxException。

This is my create table script:

这是我的CREATE TABLE脚本：

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.

您可能猜到我需要“opiowner”用户存在于数据库中。特别是在Oracle容器的第一个实例中：首先是ORACLE_OPIOWNER。

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

问题：如何在Oracle容器实例中创建“opiowner”用户？

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

我认为使用Username和withPassword的方法可以解决问题，但他们没有...

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

正如您在withEnv方法中看到的，我尝试重写Docker镜像的环境变量，但还是没有办法……

How to fix this issue?

如何解决这个问题

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

我想我找到了您所面临的问题。请仔细阅读以下GitHub问题：

[OracleContainer] Unable to execute statements which need system privileges

[OracleContainer]无法执行需要系统权限的语句

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".

  对于oracle-xe映像，现在默认的用户和架构现在是“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).

  我猜你将无法在没有权限的情况下创建任何随机用户。为了克服这一点，您必须使用作为测试容器的超级用户的测试用户（ Oracle测试容器使用/检测测试作为默认用户）。

  
  



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

  因此，我的建议是创建一个init.sql ，如下所示：

  
  

  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:

  将AbstractContanierBaseTest类更新为如下所示：

  
  

    @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:

  现在，将CREATE TABLE脚本更新为：

  
  

    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.

查看此更改是否可行。仅此而已。有关更多信息，请查看github问题。