Unable to use "DROP TABLE IF EXISTS" in schema.sql for a Spring Boot application

Question

I need help with DROP/CREATE of tables in my schema.sql

Setup:

• Oracle XE
• Spring Boot v1.4.0
• Java 1.8

When I have the following entry in schema.sql:

DROP TABLE table_a;

CREATE TABLE table_a
(
    id                       VARCHAR(5) PRIMARY KEY,
    name                     VARCHAR(100));

I get the exception

DROP TABLE table_a; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

When I looked up some help on how to do a DROP TABLE IF EXISTS in Oracle, the best answer I got was the following (works in SQLDeveloper):

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

  EXECUTE IMMEDIATE 'CREATE TABLE table_a
  (
    id               VARCHAR(5) PRIMARY KEY,
    name             VARCHAR(100)
  )';
END;

However, the above code throws the following Exception:

2016-08-10 14:55:36.232 INFO 9032 --- [ main] o.s.jdbc.datasource.init.ScriptUtils : Executing SQL script from URL [file:/C:/projects/project_a/target/classes/schema.sql] 2016-08-10 14:55:36.286 WARN 9032 --- [ main] o.s.w.c.s.GenericWebApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration': Injection of autowired dependencies failed; nested exception is org.springframework.beans.factory.BeanCreationException: Could not autowire field: private javax.sql.DataSource org.springframework.boot.autoconfigure.orm.jpa.JpaBaseConfiguration.dataSource; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in class path resource [org/springframework/boot/autoconfigure/jdbc/DataSourceAutoConfiguration$NonEmbeddedConfiguration.class]: Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourceInitializer': Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/C:/projects/project_a/target/classes/schema.sql]: BEGIN EXECUTE IMMEDIATE 'DROP TABLE table_a'; nested exception is java.sql.SQLException: ORA-06550: line 1, column 44: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

• & = - + ; < / > at in is mod remainder not rem return returning <> or != or ~= >= <= <> and or like like2 like4 likec between into using || multiset bulk member submultiset

Does anybody have a more elegant way to handle DROP/CREATE of Oracle Tables in Spring Boot?

Answer 1

You haven't shown your Java code, but from the stack trace it looks like you're calling ScriptUtil's executeSqlScript() method, which used the default semicolon statement separator.

It isn't recognising the PL/SQL block as a single unit, and is instead trying to run everything up to the first semicolon as a standalone SQL statement - which isn't valid and causes the error you're seeing.

You can use the version of executeSqlScript() that lets you override the default and use / instead:

public static void executeSqlScript(Connection connection,
                                    EncodedResource resource,
                                    boolean continueOnError,
                                    boolean ignoreFailedDrops,
                                    String commentPrefix,
                                    String separator,
                                    String blockCommentStartDelimiter,
                                    String blockCommentEndDelimiter)
                             throws ScriptException

separator - the script statement separator; defaults to ";" if not specified and falls back to "\n" as a last resort; may be set to "^^^ END OF SCRIPT ^^^" to signal that the script contains a single statement without a separator

which would mean all the SQL statements in your script would have to use a / separator instead of a semicolon too:

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

CREATE TABLE table_a
  (
    id               VARCHAR(5) PRIMARY KEY,
    name             VARCHAR(100)
  )
/

...

As noted in comments, your original block wasn't quite right anyway; and the create doesn't need to be done through PL/SQL, even if the drop needs to be.

But that method also has a ignoreFailedDrops flag, which seems to do exactly what you want (I can't test it to check though):

ignoreFailedDrops - whether or not to continue in the event of specifically an error on a DROP statement

If you use that version and pass true for that flag you don't need the PL/SQL wrapper around the drop; you can keep the semicolon separator and revert to:

DROP TABLE table_a;

CREATE TABLE table_a
(
    id                       VARCHAR(5) PRIMARY KEY,
    name                     VARCHAR(100)
);

...

If your schema script contains any other PL/SQL - trigger, packages, etc. - then you will still need to switch to using the slash separator (or any other separator of your choice; a slash is traditional though) for everything.

Answer 2

Rather than dropping and recreating tables Oracle encourages you to use global temporary tables. The advantage of a global temp table is that you don't have to go through this routine of dropping a table just to recreate it.

Now, the thing about a global temp table is that the data in it is only visible to the session which creates it, and the data is automatically deleted when either A) the transaction commits, or B) the session disconnects - you get to choose when the data should be deleted when the table is created, but the data doesn't persist for long periods of time, nor is it visible to everyone who connects to the database. It's intended for "scratchpad" tables where an application needs to put data into a table on a temporary basis, use it within a given session, and then get rid of it. If this matches your intended usage this would be a good way to go.

To create your table as a global temp table you'd specify the following:

CREATE GLOBAL TEMPORARY TABLE table_a
  (id                       VARCHAR(5) PRIMARY KEY,
   name                     VARCHAR(100))
  ON COMMIT PRESERVE ROWS;

(And as a side comment - you should get in the habit of using VARCHAR2 instead of VARCHAR with Oracle. VARCHAR is an ANSI type which Oracle does not correctly support. In Oracle VARCHAR is currently a synonym for VARCHAR2, but the rumor is that Some Day Oracle will change VARCHAR so it's fully ANSI-compliant, and if you've used it in your table the behavior of your database may quietly change without warning. So this is your warning :-).

Best of luck.

Answer 3

Create a stored procedure

create or replace procedure recreate_table 
  (i_table_name in varchar2, i_create_stmt in varchar2) 
is
BEGIN
  BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE '||upper(i_table_name);
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
        RAISE;
      END IF;
  END;
  EXECUTE IMMEDIATE i_create_stmt;
END;

Then your schema.sql can use the SQL statement:

call recreate_table('TABLE_A','CREATE TABLE TABLE_A (ID NUMBER, VAL VARCHAR2(10))');

rather than including PL/SQL

Answer 4

It's old thread but I've stumbled upon the same problem in latest Spring Boot - 1.5.4 and I think I've found an answer (also thanks to Alex Poole above). By default Spring Boot uses property

spring.datasource.separator=;

So as You can see the ';' is used as separator for SQL commands. So in the process of trying to put PL/SQL procedure into 'schema.sql' Oracle DB recieves only first SQL-command and tries to write it ot the DB. So having code:

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

In DB only BEGIN and EXECUTE IMMEDIATE 'DROP TABLE table_a'; is being stored - You can see that in eg. SQL Developer. Changing the separator to eg. ';;' helps - and also using it in SQL code instead of ';'. I do not recommend using '/' as separator as this is the sign used to create multiline-SQL-comments so it can cause problems when someone uses them in SQL file.