Running init script on oracle test container with system privileges

Question

I am struggling with org.testcontainers:oracle-xe:1.14.3. I am trying to run a test intended to verify schema creation and migration, however I'm getting stuck at the InitScript, when trying to initialize the users for the test with the users 'sys as sysdba'.

@Before
public void setUp() {
    oracleContainer = new OracleContainer("oracleinanutshell/oracle-xe-11g")
    .withUsername("sys as sysdba")
    .withInitScript("oracle-initscript.sql");
    oracleContainer.start();
}

The above seems to be able to connect, but execution of the init script fails with a

ORA-01109: database not open

Using the 'system' user in the above does not provide the InitScript connection with sysdba privileges, but result in an open database.

I'm looking for a solution that will allow me to initialize multiple users prior to a test. This initialization has grants that requires sysdba privileges. The test, in which some SQL scripts are executed, requires that both users are created in the database and can connect to the database.

Answer 1

In my case I'm using

oracleContainer = new OracleContainer("gvenzl/oracle-xe:18.4.0-slim")
    .withUsername("test")
    .withPassword("test")
    .addEnv("ORACLE_PASSWORD", "s") // Sys password is required
    .withCopyFileToContainer(MountableFile.forHostPath("oracle-initscript.sql"), "/container-entrypoint-initdb.d/init.sql")

gvenzl/oracle-xe is the default image used by the org.testcontainers.oracle-xe library.

The documentation for this image describes how to call initialization SQL on DB start and it works great.

Hard to say what is the issue but here are some tricks:

• maybe "sys as sysdba" is not valid in your code, documentation is not clear about the usage
• maybe withLogConsumer can provide some clues what's wrong
• I recommend the image gvenzl/oracle-xe,
• in some cases withInitScript may not work properly.
• it is useful to test the init script on the container started manually