PL/SQL and SQL script in one sqlFile with liquibase?

Question

I try very lot options to run in room changeSet/sqlFile which contains pl/sql block and simlple sql statement. E.g.:

BEGIN
    aud.someProcedure('parameter');
END;
/
insert into test_table(_id, value) VALUES(1, 'test');

But I get the following exception:

liquibase.exception.MigrationFailedException: Migration failed for change set [xml path]:
     Reason: liquibase.exception.DatabaseException: ORA-06550: 4 line, 1 col:
PLS-00103: Encountered the symbol: "/" 
 [Failed SQL: BEGIN 
    aud.someProcedure('parameter'); 
END; 
//
insert into test_table(_id, value) VALUES(1, 'test')
]
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:584)
    at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:51)
    at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:73)
    at liquibase.Liquibase.update(Liquibase.java:210)
    at liquibase.Liquibase.update(Liquibase.java:190)
    at liquibase.Liquibase.update(Liquibase.java:186)
    at org.jenkinsci.plugins.liquibase.builder.LiquibaseBuilder.perform(LiquibaseBuilder.java:128)
    at hudson.tasks.BuildStepMonitor$1.perform(BuildStepMonitor.java:20)
    at hudson.model.AbstractBuild$AbstractBuildExecution.perform(AbstractBuild.java:779)
    at hudson.model.Build$BuildExecution.build(Build.java:205)
    at hudson.model.Build$BuildExecution.doRun(Build.java:162)
    at hudson.model.AbstractBuild$AbstractBuildExecution.run(AbstractBuild.java:537)
    at hudson.model.Run.execute(Run.java:1741)
    at hudson.model.FreeStyleBuild.run(FreeStyleBuild.java:43)
    at hudson.model.ResourceController.execute(ResourceController.java:98)
    at hudson.model.Executor.run(Executor.java:381)
Caused by: liquibase.exception.DatabaseException: ORA-06550: 4 line, col oszlop:
PLS-00103: Encountered the symbol: "/" 
 [Failed SQL: BEGIN 
    aud.someProcedure('parameter'); 
END; 
//
insert into test_table(_id, value) VALUES(1, 'test')
]

When I try to change in the xml the splitStatement and endDelimiter, nothing changed.

Do you have eny idea?

Thegn

i am facing the same problem. Found any solution yet? Thanks mojoo – Blau

Does my answer solve your problem? Please let us know if you have any other questions. – Methanol

Answer 1

The "endDelimiter" works perfectly.

Semicolon in SQL statement produces "invalid character error", so you have to remove it when it isn't a delimiter. (Yes, it does its work in PL/SQL and SQL*Plus, just like a slash "/", more: When do I need to use a semicolon vs a slash in Oracle SQL? )

Solutions:

• endDelimiter = "/"

  <changeSet id="1" author="me">
  <sql endDelimiter="/">
      BEGIN
          aud.someProcedure('parameter');
      END;
      /
      insert into test_table(_id, value) VALUES(1, 'test')
  </sql>
  </changeSet>
  

• two sections

  <changeSet id="1" author="me">
  <sql endDelimiter="/">
      BEGIN
          aud.someProcedure('parameter');
      END;
  </sql>
  <sql>
      insert into test_table(_id, value) VALUES(1, 'test');
  </sql>
  </changeSet>
  

• or maybe ;)

  <changeSet id="1" author="me">
  <sql endDelimiter="#Gabor was here#">
      BEGIN
          aud.someProcedure('parameter');
      END;
      #Gabor was here#
      insert into test_table(_id, value) VALUES(1, 'test')
  </sql>
  </changeSet>
  

Methanol

You haven't really answered his question because he said he was using an sqlFile Change Type, which implies his SQL was stored in a seperate file (although to be fair he doesn't mention that). But in any case, that's what I'm trying to do and have exactly the same error when using "/\n" and using only "/" fails at expressions using a divisor. – Mcclung

Answer 2

Try putting the insert statement inside the Begin, end block and possibly removing the trailing slash (/) character:

BEGIN
  aud.someProcedure('parameter');
  insert into test_table(_id, value) VALUES(1, 'test');
END;

The slash is a termination character used by SQL*Plus and some other interactive query tools used to denote the end of a PL/SQL block.

Topsyturvy

Here are notes from the SQL*Plus documentation on the slash operator: docs.oracle.com/cd/B19306_01/server.102/b14357/… – Topsyturvy

The BEGIN * END; is only a sample, there is lot of script with procedure and other, and it's impossible to rewrite all of them, but thanks for the advice :) – Thegn

Answer 3

Phew, managed to make this work:

--liquibase formatted sql
--changeset n/a:1 runAlways:true runOnChange:true splitStatements:true endDelimiter:/

sql statement, no ; at the end
/

begin
    null;
end;
/

... and it's compatible with running the same script with SQLcl directly, i.e. the code runs identically.

Ralston

Answer 4

use semicolon in sql script files to separate sql statements that tell client software (SQL*Plus, SQL Developer) what are the single statements to be executed.

use slash in sql script files to separate pl/sql blocks that tell client software (SQL*Plus, SQL Developer) what are the single pl/sql blocks to be executed.

use slash in SQL*Plus command line when you want to execute buffered statement (yes it is a single sql statement without the semicolon or pl/sql block without the slash)

https://community.oracle.com/tech/developers/discussion/3981652/when-should-i-use-semicolon-or-slash-in-oracle

Haloid