Spring Boot schema.sql, initializing database using PL/SQL

Question

I am working on a project where I have to use Oracle Database 12c and I have to write all queries manually (so I can't use Spring Data). For creating all tables and relationships, I use schema.sql and for template data I use data.sql.

And I have a problem with checking if table or data already exists. In MySQL creating table would be like "create table if not exists". In PL/SQL unfortunately, there is no equivalent for "if not exists". I replaced this functionality by:

begin
execute immediate
'CREATE TABLE user_data (
  some data
)';
exception when others then if SQLCODE = -955 then null; else raise; end if;
end;

And it works when I run this script in SQL Developer or in Intellij's SQL console but the problem occurs when I want to run an application and Spring Boot tries to execute a script from schema.sql.

Output in terminal tells that:

nested exception is java.sql.SQLException: ORA-06550: line 8, column 4:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

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

So it looks like Spring Boot doesn't know that it should run statement between "begin" and "end". Any idea how can I manage the problem with database initialization ?

As a workaround, I could drop tables with every application run but it is not an optimal solution (and it wouldn't work when someone run the application for the first time).

Look into Liquidbase or Flyway. There are libraries specifically for this — pvpkiran
– pvpkiran, Commented Dec 29, 2017 at 13:37
'CREATE TABLE user_data ( some data it should had some column rather than some data — XING
– XING, Commented Dec 29, 2017 at 14:00
@pvpkiran, unfortunately, I can't use libraries like Liquidbase or Flyway... — patjd
– patjd, Commented Dec 29, 2017 at 14:10
@XING it is just an example. In my application, I have some columns but it is not necessary to show them here because problem doesn't concern them — patjd
– patjd, Commented Dec 29, 2017 at 14:11

GoodDok · Accepted Answer · 2017-12-29 21:33:21Z

1

Firstly, I would like to share two topics that seem to be relevant to this problem:

There you will find a solution that should work: create a stored procedure and use in your schema.sql statement like

call recreate_table('USER_DATA','CREATE TABLE USER_DATA (SOME DATA)');

CALL statement is widely used across different databases, shortened to statement with only one semicolon and thus works well.

Secondly, I may only suppose, that the main problem is that anonymous blocks in PL/SQL (as well as other complex enough statements that may contain more than one semicolon) should be finished by a / character. I would recommend you to try to append this character to the end of your script, take a look at this and this answers, and if it does not work, create a stored procedure.

Also note that there is another way to check existence of the table (that comes over this wait for an exception pattern):

select count(*)
  from user_tables t
 where t.table_name = 'USER_DATA'
   and rownum < 2

answered Dec 29, 2017 at 21:33

GoodDok

1,85016 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

patjd Over a year ago

Thanks, creating procedure helped me. But I had to create a procedure in SQL Developer because there is exactly the same problem when I run "create or replace procedure" in schema.sql. Spring doesn't know that should run script between "create or replace" and "/". It brakes after the first semicolon. So I will have to create a procedure in DB in every environment before running application and I guess I must create other procedures for creating relationships between tables. Nevertheless, thank you for a help. I'd like to mark your answer as a useful but I don't have reputation points.

GoodDok Over a year ago

Have you tried to play with spring.datasource.separator parameter? It seems, it worked in some cases: stackoverflow.com/a/45097894/9145106 stackoverflow.com/a/35871495/9145106

patjd Over a year ago

Yes, I have tried use different separator but I had got some errors. I will use stored procedure.

Collectives™ on Stack Overflow

Spring Boot schema.sql, initializing database using PL/SQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related