Initialization of ORACLE database In unit tests

I have been trying to write some unit tests for my application. I am using oracle database in a docker container.

I am using ScriptUtils.executeSqlScript() to initialize the database from sql files. This work if I am using MySql database but I have PL/SQL in my Oracle schema and I get an exception when schemas executed (I am 100% sure that schemas work).

END; nested exception is java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

I guess ScriptUtils.executeSqlScript() does not support PL/SQL.

As I don't want to do volume binding in my docker-compose.yml I can't initialize tables when I start the docker container.

1. Is there a better way of initializing tables for my JUnit tests that will work for any database technology?

2. As you can see, I am generating tables before tests and dropping them after but I have other tests that will use those tables so I want to create tables once for all of the tests related to oracle. Is there any way of doing that?

@SpringBootTest
public class JdbcUserDaoImplTest {

    private static JdbcUserDaoImpl userDao;

    private User user;

    @BeforeClass
    public static void init() throws SQLException {
        userDao = new MysqlJdbcUserDaoImpl(dataSource());
        ScriptUtils.executeSqlScript(Objects.requireNonNull(userDao.getDataSource()).getConnection(), new ClassPathResource("pre.schema.sql"));
        ScriptUtils.executeSqlScript(userDao.getDataSource().getConnection(), new ClassPathResource("schema.sql"));
    }

    ... TEST CASES ...

    public static DataSource dataSource() {
        DataSourceBuilder<?> dataSourceBuilder = DataSourceBuilder.create();
        dataSourceBuilder.driverClassName("oracle.jdbc.OracleDriver");
        dataSourceBuilder.url("jdbc:oracle:thin:@127.0.0.1:1507:XE");
        dataSourceBuilder.username("system");
        dataSourceBuilder.password("oracle");
        return dataSourceBuilder.build();
    }

    @AfterClass
    public static void destroy() throws SQLException {
        ScriptUtils.executeSqlScript(Objects.requireNonNull(userDao.getDataSource()).getConnection(), new ClassPathResource("drop.sql"));
    }
}

EDIT

Example of undeployable SQL

CREATE TRIGGER item_trigger
    BEFORE INSERT
    ON items
    FOR EACH ROW
BEGIN

    IF :NEW.item_date IS NULL
    THEN
        SELECT CURRENT_TIMESTAMP INTO :NEW.item_date FROM DUAL;
    END IF;

    IF :NEW.item_uid IS NULL
    THEN
        SELECT SYS_GUID() INTO :NEW.item_uid FROM DUAL;
    END IF;

END;

Copyright Notice：Content Author:「Dogukan Evcil」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/68117079/initialization-of-oracle-database-in-unit-tests