Oracle - UpdateTagSQL - Issue with delimeters

pjsoares.almeida · May 31, 2021, 3:18pm

Hello there

It’s been a real pleasure trying liquibase as a database CI tool.
I’d like your view on if my issue is really an issue or an usage miss-understanding.
I’ve “base” DEV and QA envs which are fully CI integrated using liquibase. When I try to use the UpdateTagSQL command to provide a “custom” env patch overview + installation script, I’ve the following issue with the generated code:

every plsql block that needs a newline+/ (slash) char in the end to work, gets generated with no newline char. example:

— changeset instruction
<sqlFile … endDelimiter="/"…

— generated PLSQL instruction
END;/

This fails when executing trough and Oracle compatible SQL Client (event sqlplus).

Can you please help me with this?

Thank you,
Pedro Almeida

rakhi · June 1, 2021, 11:43am

Hi @pjsoares.almeida

Could you help us with the exact command you tried? That will help us get some solution and reproduce the issue.

Thanks!
Rakhi Agrawal

pjsoares.almeida · June 1, 2021, 12:04pm

Hello @rakhi thank you for your feedback
Here goes the full command (replaced redundant information with <> :

liquibase --classpath=<>\ojdbc8.jar --driver=oracle.jdbc.OracleDriver --url=jdbc:oracle:thin:@<> --username=<> --password=<> --changeLogFile=changelog\master.xml --outputFile=<>\<>.sql --liquibaseProLicenseKey=<> updateToTagSQL <>

Anything additional, feel free to ask.

Thank you,
Pedro

rakhi · June 1, 2021, 12:22pm

Hello,

Sorry for bothering but it would be really helpful if you could also provide us with a sample changeset file you are using (ofcource with dummy data).

Thanks!
Rakhi Agrawal

pjsoares.almeida · June 1, 2021, 2:10pm

Sure,

Here it goes:

<ns0:databaseChangeLog xmlns:ns0="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext  http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.1.xsd  http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.1.xsd">
    <ns0:changeSet author="jmalbuquerque" id="REQ123_28052021_45" runOnChange="true">
        <ns0:sqlFile encoding="utf8" endDelimiter="/" path="../../SCH_ADMIN/SCRIPTS/REQ123/REQ123_45_PROCEDURE_lock_table_online_create_INSTALL.sql" relativeToChangelogFile="true" stripComments="false" />
        <ns0:rollback>
            <ns0:sqlFile encoding="utf8" endDelimiter="/" path="../../SCH_ADMIN/SCRIPTS/REQ123/REQ123_45_PROCEDURE_lock_table_online_create_UNINSTALL.sql" relativeToChangelogFile="true" stripComments="false" />
        </ns0:rollback>
    </ns0:changeSet>
  <ns0:changeSet author="pjalmeida" id="TAG213">
        <ns0:tagDatabase tag="TAG213" />
    </ns0:changeSet>
</ns0:databaseChangeLog>

Thank you,
Pedro

ahmedabdeljelil · June 2, 2021, 9:28am

Hello,

I have done it this way:

1- This is my liquibase.properties
#Liquibase.properties
driver: oracle.jdbc.OracleDriver
classpath: ./ojdbc8.jar
url: jdbc:oracle:thin:@localhost:1521:XE
username: hr
password: hr

2- This is my changelog test.xml:

3- This is my testSql.sql file :
DECLARE
e_error EXCEPTION;
PRAGMA exception_init ( e_error,-00942 );
BEGIN
EXECUTE IMMEDIATE ‘drop table AD_GROUP_ROLE_MAPPING’;

EXCEPTION
WHEN e_error THEN
NULL;
END;

/
CREATE TABLE AD_GROUP_ROLE_MAPPING
(
AD_GROUP_NAME VARCHAR2(200) NOT NULL,
ROLE_ID NUMBER,
PRIMARY KEY (AD_GROUP_NAME)
)

4- I run the liquibase updateToTagSQL command :
liquibase --changeLogFile=test.xml --outputFile=out.sql updateToTagSQL TAG2134

5- This is the final output:
– *********************************************************************
– Update to ‘TAG2134’ Database Script
– *********************************************************************
– Change Log: test.xml
– Ran at: 02/06/2021 10:19
– Against: HR@jdbc:oracle:thin:@localhost:1521:XE
– Liquibase version: 4.3.5
– *********************************************************************

– Create Database Lock Table
CREATE TABLE HR.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID));

– Initialize Database Lock Table
DELETE FROM HR.DATABASECHANGELOGLOCK;

INSERT INTO HR.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);

– Lock Database
UPDATE HR.DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = ‘DESKTOP-AHMED (192.168.56.1)’, LOCKGRANTED = TO_TIMESTAMP(‘2021-06-02 10:19:44.937’, ‘YYYY-MM-DD HH24:MI:SS.FF’) WHERE ID = 1 AND LOCKED = 0;

– Create Database Change Log Table
CREATE TABLE HR.DATABASECHANGELOG (ID VARCHAR2(255) NOT NULL, AUTHOR VARCHAR2(255) NOT NULL, FILENAME VARCHAR2(255) NOT NULL, DATEEXECUTED TIMESTAMP NOT NULL, ORDEREXECUTED INTEGER NOT NULL, EXECTYPE VARCHAR2(10) NOT NULL, MD5SUM VARCHAR2(35), DESCRIPTION VARCHAR2(255), COMMENTS VARCHAR2(255), TAG VARCHAR2(255), LIQUIBASE VARCHAR2(20), CONTEXTS VARCHAR2(255), LABELS VARCHAR2(255), DEPLOYMENT_ID VARCHAR2(10));

– Changeset test.xml::1::krzysd
DECLARE
e_error EXCEPTION;
PRAGMA exception_init ( e_error,-00942 );
BEGIN
EXECUTE IMMEDIATE ‘drop table AD_GROUP_ROLE_MAPPING’;

EXCEPTION
WHEN e_error THEN
NULL;
END;/

CREATE TABLE AD_GROUP_ROLE_MAPPING
(
AD_GROUP_NAME VARCHAR2(200) NOT NULL,
ROLE_ID NUMBER,
PRIMARY KEY (AD_GROUP_NAME)
)/

INSERT INTO HR.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES (‘1’, ‘krzysd’, ‘test.xml’, SYSTIMESTAMP, 1, ‘8:f4bd3e9aaaf1952e45f18eaf849eaa76’, ‘sqlFile’, ‘’, ‘EXECUTED’, NULL, NULL, ‘4.3.5’, ‘2625585326’);

– Changeset test.xml::TAG2134::ahmed1
INSERT INTO HR.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES (‘TAG2134’, ‘ahmed1’, ‘test.xml’, SYSTIMESTAMP, 2, ‘8:334b4743b886a8813524c03189b50c6f’, ‘tagDatabase’, ‘’, ‘EXECUTED’, NULL, NULL, ‘4.3.5’, ‘2625585326’, ‘TAG2134’);

– Release Database Lock
UPDATE HR.DATABASECHANGELOGLOCK SET LOCKED = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;

I hope this help you.

Regards,
Ahmed.

pjsoares.almeida · June 2, 2021, 9:46am

Hello @ahmedabdeljelil how are you?
Thanks for your feedback.
In your output SQL I can see the same issue:

This code won’t execute correctly in sqlplus or any standard oracle sql client

Thanks,
Pedro

ahmedabdeljelil · June 2, 2021, 9:54am

Hello,

if you don’t want the “/” at the end of PL/SQL script, then you have to update the test.xml script like this :

You’ll get this output file :

– *********************************************************************
– Update to ‘TAG2134’ Database Script
– *********************************************************************
– Change Log: test.xml
– Ran at: 02/06/2021 10:50
– Against: HR@jdbc:oracle:thin:@localhost:1521:XE
– Liquibase version: 4.3.5
– *********************************************************************

– Create Database Lock Table
CREATE TABLE HR.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID));

– Initialize Database Lock Table
DELETE FROM HR.DATABASECHANGELOGLOCK;

INSERT INTO HR.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);

– Lock Database
UPDATE HR.DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = ‘DESKTOP-AHMED (192.168.56.1)’, LOCKGRANTED = TO_TIMESTAMP(‘2021-06-02 10:50:39.568’, ‘YYYY-MM-DD HH24:MI:SS.FF’) WHERE ID = 1 AND LOCKED = 0;

– Create Database Change Log Table
CREATE TABLE HR.DATABASECHANGELOG (ID VARCHAR2(255) NOT NULL, AUTHOR VARCHAR2(255) NOT NULL, FILENAME VARCHAR2(255) NOT NULL, DATEEXECUTED TIMESTAMP NOT NULL, ORDEREXECUTED INTEGER NOT NULL, EXECTYPE VARCHAR2(10) NOT NULL, MD5SUM VARCHAR2(35), DESCRIPTION VARCHAR2(255), COMMENTS VARCHAR2(255), TAG VARCHAR2(255), LIQUIBASE VARCHAR2(20), CONTEXTS VARCHAR2(255), LABELS VARCHAR2(255), DEPLOYMENT_ID VARCHAR2(10));

– Changeset test.xml::1::krzysd
DECLARE
e_error EXCEPTION;

PRAGMA exception_init ( e_error,-00942 );

BEGIN
EXECUTE IMMEDIATE ‘drop table AD_GROUP_ROLE_MAPPING’;

EXCEPTION
WHEN e_error THEN
NULL;

END;

CREATE TABLE AD_GROUP_ROLE_MAPPING
(
AD_GROUP_NAME VARCHAR2(200) NOT NULL,
ROLE_ID NUMBER,
PRIMARY KEY (AD_GROUP_NAME)
);

INSERT INTO HR.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES (‘1’, ‘krzysd’, ‘test.xml’, SYSTIMESTAMP, 1, ‘8:8df86fece362946fa950870e6c235733’, ‘sqlFile’, ‘’, ‘EXECUTED’, NULL, NULL, ‘4.3.5’, ‘2627439959’);

– Changeset test.xml::TAG2134::ahmed1
INSERT INTO HR.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES (‘TAG2134’, ‘ahmed1’, ‘test.xml’, SYSTIMESTAMP, 2, ‘8:334b4743b886a8813524c03189b50c6f’, ‘tagDatabase’, ‘’, ‘EXECUTED’, NULL, NULL, ‘4.3.5’, ‘2627439959’, ‘TAG2134’);

– Release Database Lock
UPDATE HR.DATABASECHANGELOGLOCK SET LOCKED = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;

Regards,
Ahmed.

pjsoares.almeida · June 2, 2021, 10:06am

Hello,

I actually need the “/” char at the end of plsql blocks. The issue here is that its formatted by liquibase to be “END;/”
instead of
“END;
/”
(which will actually work).

Thanks,
Pedro

rakhi · June 2, 2021, 10:16am

I tried this and was able to reproduce the issue. It seems like a bug in liquibase. Would you mind logging an issue here. You may refer this for help with loggining an issue.
If someone from the community has any workaround for this issue, they could comment it there. Also it would be easier for tracking and getting this issue in their notice.

Thanks!
Rakhi Agrawal

pjsoares.almeida · June 2, 2021, 10:34am

will do.

Thank you, very much.

Topic		Replies	Views
Liquibase 4.8 breaking the PL/SQL procedure because of the dashes in the quoted string General Discussion	8	2132	June 24, 2022
endDelimiter issues Mysql General Discussion	7	2070	April 24, 2020
An unexpected syntax result with updateSQL for dbms:oracle General Discussion	2	183	June 1, 2023
Liquibase 4.9.0 and "endDelimiter:/" recognizes "/" in PL/SQL code as changeset end delimiter General Discussion	1	996	March 27, 2022
Problem with SQL scripts generated for loadUpdateData General Discussion	6	485	April 23, 2012

Oracle - UpdateTagSQL - Issue with delimeters

Related Topics