In the migration process I’m doing I have several stored procedures and packages in sql files, they have the oracle end delimiter / and I’m trying to use them with liquibase. But I found an issue with this delimiter and here is an example. For this procedure:
CREATE OR REPLACE PROCEDURE TEST_PROC(key_value in number)
AS
TABLENAME VARCHAR2(40);
BEGIN
SELECT 'TEST_TABLE' INTO TABLENAME FROM DUAL;
DBMS_OUTPUT.PUT_LINE('DELETE FROM ' || tablename || ' WHERE key_value = ' || to_char(key_value) || '');
END;
/
If I use this changeset it works well:
DROP PROCEDURE TEST_PROC
The problem appears when you have comments like this:
CREATE OR REPLACE PROCEDURE TEST_PROC(key_value in number)
AS
TABLENAME VARCHAR2(40);
BEGIN
SELECT 'TEST_TABLE' INTO TABLENAME FROM DUAL;
/* My Comment */
DBMS_OUTPUT.PUT_LINE('DELETE FROM ' || tablename || ' WHERE key_value = ' || to_char(key_value) || '');
END;
/
I tries to run * My Comment * SQL statement.
Of course I can replace the /* … / by --. But when you use oracle hints we have a problem because we need the /, for example
CREATE OR REPLACE PROCEDURE TEST_PROC(key_value in number)
AS
TABLENAME VARCHAR2(40);
BEGIN
SELECT /*+ PARALLEL */ 'TEST_TABLE' INTO TABLENAME FROM DUAL;
DBMS_OUTPUT.PUT_LINE('DELETE FROM ' || tablename || ' WHERE key_value = ' || to_char(key_value) || '');
END;
/
I tried something else like:
DROP PROCEDURE TEST_PROC
But it also fails, although I think this one should work fine…
This was working before and now with revision 1718 it is failling:
CREATE OR REPLACE PROCEDURE TEST_PROC(key_value in number)
AS
TABLENAME VARCHAR2(40);
BEGIN
SELECT 'TEST_TABLE' INTO TABLENAME FROM DUAL;
DBMS_OUTPUT.PUT_LINE('DELETE FROM ' || tablename || ' WHERE key_value = ' || to_char(key_value) || '');
I’ve made some changes and bug fixes with the end delimiters, the end delimiter is actually a regular expression, so you should be able to use endDelimiter="^/$" and it will only split on /'s that are on their own line.
After pulling down the source code and stepping through it with a debugger, I came to that understanding (about using regex). The expression you suggest doesn’t work for some reason, but borrowing from the code in StringUtils.java, I came up this this combination which seems to work perfectly:
Thanks for your assistance and thanks for a great tool.
This is just what I was looking for , the delimiter for PL/SQL, incredible how something so basic in Oracle was so hard to find . It works great when executing from Liquibase, but when trying to get SQL (executeSQL), as a DBA would, to execute the code in production environment, it shows the text below. The forward-slash (/) is replaced by the regex in endDelimiter, is that the expected behavior?
Please help, the tool is great, but I’m lost here
INFO 23/06/11 12:47 PM:liquibase: Successfully acquired change log lock INFO 23/06/11 12:47 PM:liquibase: Reading from DATABASECHANGELOG INFO 23/06/11 12:47 PM:liquibase: Reading from DATABASECHANGELOG INFO 23/06/11 12:47 PM:liquibase: ChangeSet test.xml::6::admin ran successfully in 0ms INFO 23/06/11 12:47 PM:liquibase: Successfully released change log lock – ********************************************************************* – Update Database Script – ********************************************************************* – Change Log: test.xml – Ran at: 23/06/11 12:47 PM – Against: PORTA@jdbc:oracle:thin:@130.2.17.15:1521:AXISD – Liquibase version: 2.0.1 – *******************************************************************
– Lock Database – Changeset test.xml::6::admin::(Checksum: 3:b68a4e5f5289107c5db491db643a449a) CREATE OR REPLACE Package PORTA.a Is /holax/ --Function b(Edad Number) Return Varchar2; Function b(Telefono Number) Return Number; Function b(Nombre Varchar2) Return Number;
, the delimiter for PL/SQL, incredible how something so basic in Oracle was so hard to find 
. It works great when executing from Liquibase, but when trying to get SQL (executeSQL), as a DBA would, to execute the code in production environment, it shows the text below. The forward-slash (/) is replaced by the regex in endDelimiter, is that the expected behavior?