PLS-00103: Encountered the symbol "end-of-file" - Script for deleting everything from db ignoring constraints

while1618 0
Viewed 27

I have a problem running a SQL script in Spring Boot. My script is executing correctly in SQL Oracle developer, but when I start it through java, I'm getting this error:

PLS-00103: Encountered the symbol "END" 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 overlaps using ||
   multiset bulk year day member submultiset

I know there are a lot of these questions on SO, and I tried to apply solutions that are suggested, but nothing seems to work for me.

Are there any ideas what could be wrong?

This is mine SQL script:

BEGIN
  FOR c IN (SELECT table_name, constraint_name FROM user_constraints WHERE constraint_type = 'R' AND table_name != 'DATABASECHANGELOG') LOOP
    EXECUTE IMMEDIATE ('alter table ' || c.table_name || ' disable constraint ' || c.constraint_name);
  END LOOP;

  FOR c IN (SELECT table_name FROM user_tables WHERE table_name != 'DATABASECHANGELOG') LOOP
    EXECUTE IMMEDIATE ('truncate table ' || c.table_name);
  END LOOP;

  FOR c IN (SELECT table_name, constraint_name FROM user_constraints WHERE constraint_type = 'R' AND table_name != 'DATABASECHANGELOG') LOOP
    EXECUTE IMMEDIATE ('alter table ' || c.table_name || ' enable constraint ' || c.constraint_name);
  END LOOP;
END;
oracle spring-boot sql
1 Answers

So the problem was not in the script after all, it was in the way I execute it.

I was using DatabasePopulatorUtils.execute(), which have some strange separator, and it is set to ;. Because of that, my script was corrupted, only the commands before the first ; were executed, and that's why I was getting PLS-00103 error.

After I change from DatabasePopulatorUtils.execute() to jdbcTemplate.execute() everything worked as expected.

while16180
Related