Oracle blocks requiring forward-slash to execute

General Discussion
1

I am trying to generate SQL Output script, which I will run from Ant (via …) or in SQL Developer (a client application from Oracle).  However, both these methods require some blocks (e.g. create or replace type…, create or replace trigger …) to be followed by a single line containing only a forward-slash.  I cannot find a way to do this in LiquiBase, am I missing something?

This sounds like a feature that was inexplicably removed for v1.9.

2

It was working in 1.8?  Are you specifying the endDelimiter attribute on ?

Nathan

3

My assertion that the feature was “removed for v1.9” is based entirely on this:

http://www.nabble.com/Problem-with-generating-scripts-for-Oracle-td20541432.html

So, I haven’t seen this working reliably, since I just started using 1.9.3 & 1.9.4.

4

It does look like it was removed in favor of the ; line terminators.  The default line terminator string is returned by the OracleDatabase class.  If you use the databaseClassName attribute, you can specify your own subclass of OracleDatabase that overrides the defaultSeparator method.

I believe there is an attribute on to specify the end delimiter, but I don’t think it will allow you to say “\n/”.  I created a feature request to allow something like that (http://liquibase.jira.com/browse/CORE-406).

Nathan

5

In fact, the Ant sql task will let you specify the block delimiter, but this doesn’t solve my underlying problem.

Change logs can be executed (in the case of Oracle) in 4 ways that are relevant to me:

  • asking Liquibase to apply the changes directly (i.e. no outputfile)
  • asking Liquibase to generate a the script file, then

In our environment, we definitely need to be able to generate scripts that can be reviewed by our DBA.  I think that is a common scenario.  However, generating scripts that will run successfully and reliably in all three cases listed above (Ant Task, SQL*Plus, SQLDeveloper) is tricky.  In fact, I’ve not been able to successfully achieve even one of those.

The root of the problem is the handling of triggers, procedures, and other “blocks” vs. handling of “statements”.  I think I understand correctly that blocks require a delimiter (by default, “/” on a line by itself) after the end of the block.

I will build my own subclass to accomplish what’s needed (if I can identify a comprehensive solution), but I’m really surprised that no one has already addressed this.  Perhaps I overestimate the intersection of Liquibase users and Oracle users. :slight_smile:

6

Tim,

Maybe there is confusion about the block separator versus the PL/SQL command separator. In SQL*Plus these can sometimes be interchanged. In PL/SQL, only a semicolon can be used, and a semi-colon must trail the last END statement. This is part of the PL/SQL block. You then require a / to execute the block.

There is an example at http://www.liquibase.org/manual/custom_sql that shows a PL/SQL block in the SQL tag. No “/” required.
Hope this helps.

7

I am having success executing trigger and procedure blocks via an updateDatabase task, but I’m still not sure what to do to generate the proper scripts. I’ll look closer at the link you included.

8

We have the same use case of liquibase:

  • applying changes directly via liquibase is used only for dev purposes
  • for production purposes we generate script which is passed to dba

  1. Adding a slash to the end of each statement was a bad choice because sqlplus treats slash as a buffer flush. For procedure/triggers/functions it is a must: because content of if is stored in the buffer and is not executed. However, for all other statements it means executing the same statement twice because such statement is executed immediately and put into buffer and following slash just re-executes the same statement.

  2. We have found following workaround to the problem:
    a) because changeset accepts multiple tags so we create first for procedure and right after it a dummy like this:
    select 1 from dual
    b) then in ant build.xml we generate script and right after it we postprocess it with replace task: we replace above dummy sql with a slash. Since dummy sql is harmless so in applying mode it does not matter.

  3. I think instead of extending xml schema to accept things like end separator etc. I would focus on API which would allow control of generated script. Such API would accept a callback interface which would be called in certain places of generated script (f.e. right after the statement is outputed). Callback method could get a type of statement (raw sql OR create procedure). In case it is a create procedure tag then callback could add following slash to tackle sqlplus issue. Callback could be created manually by the user OR there would be out of the box callbacks for particular use cases: f.e. sqlplus. To have even more flexibility sql tags (and others) could accept an attribute called f.e. label which would be any name given by the user which would be passed to the callback: thus, callback could have more meta info about the statement.
    So generally it would be good to have an API for generating script which would allow plugging in a post processor mechanism: to allow customization driven by users and/or execution tools (sqlplus etc.).