1

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).

patjd
  • 31
  • 1
  • 2
  • Look into Liquidbase or Flyway. There are libraries specifically for this – pvpkiran Dec 29 '17 at 13:37
  • `'CREATE TABLE user_data ( some data` it should had some column rather than some data – XING Dec 29 '17 at 14:00
  • 1
    @pvpkiran, unfortunately, I can't use libraries like Liquidbase or Flyway... – patjd Dec 29 '17 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 Dec 29 '17 at 14:11

1 Answers1

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
GoodDok
  • 1,770
  • 13
  • 28
  • 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. – patjd Dec 30 '17 at 11:27
  • Have you tried to play with `spring.datasource.separator` parameter? It seems, it worked in some cases: https://stackoverflow.com/a/45097894/9145106 https://stackoverflow.com/a/35871495/9145106 – GoodDok Dec 30 '17 at 21:13
  • Yes, I have tried use different separator but I had got some errors. I will use stored procedure. – patjd Jan 03 '18 at 07:41