Oracle basic script does not work while importing schema using schema.sql. 2.5.5

[Drezir]

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE TEST';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN
            RAISE;
        END IF;
END;
/
create table TEST
(
    id   number(10) primary key,
    name varchar2(100)
);
/
create or replace procedure insert_user(id IN NUMBER,
                                        name IN VARCHAR2)
    is
begin
    insert into TEST values (id, name);
end;

spring:
  sql:
    init:
      mode: always
      platform: oracle
      separator: /
      encoding: utf-8

2021-10-07 12:45:11.303 ERROR 1407257 --- [           main] o.s.boot.SpringApplication               : Application run failed

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourceScriptDatabaseInitializer' defined in class path resource [org/springframework/boot/autoconfigure/sql/init/DataSourceInitializationConfiguration.class]: Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #2 of URL [file:/home/drezir/Plocha/demo/target/classes/schema-oracle.sql]: create table TEST ( id number(10) primary key, name varchar2(100) ); ; nested exception is java.sql.SQLSyntaxErrorException: ORA-00922: chybějící nebo neplatná volba

    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1804) ~[spring-beans-5.3.10.jar:5.3.10]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:620) ~[spring-beans-5.3.10.jar:5.3.10]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:542) ~[spring-beans-5.3.10.jar:5.3.10]
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335) ~[spring-beans-5.3.10.jar:5.3.10]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.3.10.jar:5.3.10]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333) ~[spring-beans-5.3.10.jar:5.3.10]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.10.jar:5.3.10]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:322) ~[spring-beans-5.3.10.jar:5.3.10]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.10.jar:5.3.10]
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1154) ~[spring-context-5.3.10.jar:5.3.10]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:908) ~[spring-context-5.3.10.jar:5.3.10]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:583) ~[spring-context-5.3.10.jar:5.3.10]
    at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:145) ~[spring-boot-2.5.5.jar:2.5.5]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:754) [spring-boot-2.5.5.jar:2.5.5]
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:434) [spring-boot-2.5.5.jar:2.5.5]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:338) [spring-boot-2.5.5.jar:2.5.5]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1343) [spring-boot-2.5.5.jar:2.5.5]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1332) [spring-boot-2.5.5.jar:2.5.5]
    at com.example.demo.DemoApplication.main(DemoApplication.java:10) [classes/:na]
Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #2 of URL [file:/home/drezir/Plocha/demo/target/classes/schema-oracle.sql]: create table TEST ( id number(10) primary key, name varchar2(100) ); ; nested exception is java.sql.SQLSyntaxErrorException: ORA-00922: chybějící nebo neplatná volba

    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:282) ~[spring-jdbc-5.3.10.jar:5.3.10]
    at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:254) ~[spring-jdbc-5.3.10.jar:5.3.10]
    at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:49) ~[spring-jdbc-5.3.10.jar:5.3.10]
    at org.springframework.boot.jdbc.init.DataSourceScriptDatabaseInitializer.runScripts(DataSourceScriptDatabaseInitializer.java:89) ~[spring-boot-2.5.5.jar:2.5.5]
    at org.springframework.boot.sql.init.AbstractScriptDatabaseInitializer.runScripts(AbstractScriptDatabaseInitializer.java:145) ~[spring-boot-2.5.5.jar:2.5.5]
    at org.springframework.boot.sql.init.AbstractScriptDatabaseInitializer.applyScripts(AbstractScriptDatabaseInitializer.java:107) ~[spring-boot-2.5.5.jar:2.5.5]
    at org.springframework.boot.sql.init.AbstractScriptDatabaseInitializer.applySchemaScripts(AbstractScriptDatabaseInitializer.java:97) ~[spring-boot-2.5.5.jar:2.5.5]
    at org.springframework.boot.sql.init.AbstractScriptDatabaseInitializer.initializeDatabase(AbstractScriptDatabaseInitializer.java:75) ~[spring-boot-2.5.5.jar:2.5.5]
    at org.springframework.boot.sql.init.AbstractScriptDatabaseInitializer.afterPropertiesSet(AbstractScriptDatabaseInitializer.java:65) ~[spring-boot-2.5.5.jar:2.5.5]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1863) ~[spring-beans-5.3.10.jar:5.3.10]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1800) ~[spring-beans-5.3.10.jar:5.3.10]
    ... 18 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: ORA-00922: chybějící nebo neplatná volba

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:630) ~[ojdbc8-21.3.0.0.jar:21.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:564) ~[ojdbc8-21.3.0.0.jar:21.3.0.0.0]
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1151) ~[ojdbc8-21.3.0.0.jar:21.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:771) ~[ojdbc8-21.3.0.0.jar:21.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:299) ~[ojdbc8-21.3.0.0.jar:21.3.0.0.0]
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:498) ~[ojdbc8-21.3.0.0.jar:21.3.0.0.0]
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:112) ~[ojdbc8-21.3.0.0.jar:21.3.0.0.0]
    at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1011) ~[ojdbc8-21.3.0.0.jar:21.3.0.0.0]
    at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1531) ~[ojdbc8-21.3.0.0.jar:21.3.0.0.0]
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1311) ~[ojdbc8-21.3.0.0.jar:21.3.0.0.0]
    at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:2163) ~[ojdbc8-21.3.0.0.jar:21.3.0.0.0]
    at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:2118) ~[ojdbc8-21.3.0.0.jar:21.3.0.0.0]
    at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:328) ~[ojdbc8-21.3.0.0.jar:21.3.0.0.0]
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-4.0.3.jar:na]
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:261) ~[spring-jdbc-5.3.10.jar:5.3.10]
    ... 28 common frames omitted
Caused by: oracle.jdbc.OracleDatabaseException: ORA-00922: chybějící nebo neplatná volba

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:637) ~[ojdbc8-21.3.0.0.jar:21.3.0.0.0]
    ... 43 common frames omitted

Process finished with exit code 1

Nothing special, just starter project with just oracle driver and spring data jpa:

<dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <version>21.3.0.0</version>
            <scope>runtime</scope>
        </dependency>

[wilkinsona]

Thanks for the report. ORA-0092 indicates that there's a syntax error in your SQL. It's not clear from your description why you think that this is a Spring Boot problem? Also, I'm a little bit confused by the configuration that you've shown. With spring.sql.init.mode set to never, DataSourceScriptDatabaseInitializer should not be doing anything. However, we can see from the stack trace that it is. That makes me suspect that there's something more to your problem that you haven't shown.

If you'd like us to spend some more time investigating, can you please provide a complete yet minimal example that reproduces the problem using Oracle XE?

[Drezir]

Sorry for the configuration issue. I forgot to change it for this report. Basically, I can run this script just fine in IntelliJ console but not using spring sql init scripts.

I am going to fix that mode.

[Drezir]

And here is the example demo.zip

[philwebb]

I don't think there's a bug with Spring Boot here, it's sending the SQL as expected. The problem is that your script isn't being split up correctly.

If you change your application.yaml to contain the following:

spring:
  sql:
    init:
      mode: always
      platform: oracle
      separator: ;;
      encoding: utf-8

Then the script will split on ;; rather than ;. You can then update your script as follows and it will work:

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE MY_USER';
    EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN
            RAISE;
        END IF;
END;

;;

create table MY_USER
(
    id   number(10) primary key,
    name varchar2(100)
)

;;

create or replace function insert_user_get_fn(id IN NUMBER,
                                              name IN VARCHAR2)
    return SYS_REFCURSOR
as
    users SYS_REFCURSOR;
begin
    insert into MY_USER values (id, name);
    open users for select * from MY_USER;
    return users;
end;

;;

create or replace procedure insert_user_get_proc(id IN NUMBER,
                                                 name IN VARCHAR2,
                                                 users out sys_refcursor)
as
begin
    insert into MY_USER values (id, name);
    open users for select * from MY_USER;
end;

;;

create or replace procedure insert_user(id IN NUMBER,
                                        name IN VARCHAR2)
    is
begin
    insert into MY_USER values (id, name);
end;

;;

[Drezir]

@philwebb

Is there any reason why forward slash / does not work as statement separator? Or is it the spaces between statements and separator that are required?

[wilkinsona]

The example doesn't appear to use / as a separator in the script:

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE MY_USER';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN
            RAISE;
        END IF;
END;
create table MY_USER
(
    id   number(10) primary key,
    name varchar2(100)
);
create or replace function insert_user_get_fn(id IN NUMBER,
                                              name IN VARCHAR2)
    return SYS_REFCURSOR
as
    users SYS_REFCURSOR;
begin
    insert into MY_USER values (id, name);
    open users for select * from MY_USER;
    return users;
end;
create or replace procedure insert_user_get_proc(id IN NUMBER,
                                                 name IN VARCHAR2,
                                                 users out sys_refcursor)
as
begin
    insert into MY_USER values (id, name);
    open users for select * from MY_USER;
end;

create or replace procedure insert_user(id IN NUMBER,
                                        name IN VARCHAR2)
    is
begin
    insert into MY_USER values (id, name);
end;

Nor does it customize the separator in application.yaml:

spring:
  datasource:
    url: jdbc:oracle:thin:@localhost:1521:ORCLCDB
    username: sys as sysdba
    password: Oradoc_db1
  jpa:
    properties:
      hibernate:
        dialect: org.hibernate.dialect.Oracle12cDialect

[Drezir]

@wilkinsona Sorry for the example, it is not super correct because I forgot to revert changes I made. If I had separator specified and statements separated by it, it would not work at least on my system.