Unable to run multi line constraint

General Discussion
1

Hi,

While running multiple line constraint we facing below error, kindly assist.

 Reason: liquibase.exception.DatabaseException: ORA-01735: invalid ALTER TABLE option

[Failed SQL: (1735) ALTER TABLE “ACLU_CSV_PREMIUM” ADD CONSTRAINT “CKC_V_LASTUPD_PROG” CHECK (V_LASTUPD_PROG IS NOT NULL) ENABLE NOVALIDATE
ALTER TABLE “ACLU_CSV_PREMIUM” ADD CONSTRAINT “CKC_V_LASTUPD_INFTIM” CHECK (V_LASTUPD_INFTIM IS NOT NULL) ENABLE NOVALIDATE
ALTER TABLE “ACLU_CSV_PREMIUM” ADD CONSTRAINT “CKC_V_LASTUPD_USER” CHECK (V_LASTUPD_USER IS NOT NULL) ENABLE NOVALIDATE]

Thanks,
Naren.A

2

@linuxerpro, which database are you using?

Thanks,
Wesley

3

Hi Wesley,

We using Oracle 19c database.

Thanks,
Naren

4

@linuxerpro I also should have asked, what type changelog are you using? Thanks.

5

Hi @wwillard,

XML Type.

          Changelog:
          
          <databaseChangeLog xmlns="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
                             http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.1.xsd">
          	<includeAll path="SEQUENCES" relativeToChangelogFile="true"/>	
          	<includeAll path="TABLES" relativeToChangelogFile="true"/>	
          	<includeAll path="CONSTRAINTS" relativeToChangelogFile="true"/>	
          </databaseChangeLog>
6

Please provide the entire contents of the changelog that is getting the error.

7

hi @daryldoak,

pls find entire changelog below,


<databaseChangeLog xmlns="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
                   http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.1.xsd">
	<includeAll path="SEQUENCES" relativeToChangelogFile="true"/>	
	<includeAll path="TABLES" relativeToChangelogFile="true"/>
	<includeAll path="DRG_MODULES" relativeToChangelogFile="true"/>		
	<includeAll path="INDEXES" relativeToChangelogFile="true"/>	
	<includeAll path="CONSTRAINTS" relativeToChangelogFile="true"/>		
	<includeAll path="FUNCTIONS" relativeToChangelogFile="true"/>	
	<includeAll path="MATERIALIZED_VIEWS" relativeToChangelogFile="true"/>	
	<includeAll path="PROCEDURES" relativeToChangelogFile="true"/>		
	<includeAll path="TYPES" relativeToChangelogFile="true"/>
	<includeAll path="PACKAGES" relativeToChangelogFile="true"/>	
	<includeAll path="PACKAGE_BODIES" relativeToChangelogFile="true"/>
	<includeAll path="SYNONYMS" relativeToChangelogFile="true"/>
	<includeAll path="VIEWS" relativeToChangelogFile="true"/>
</databaseChangeLog>

Thanks,
Naren.

8

That is not the changelog that received the error, it would be one of the files in the CONSTRAINTS folder, I would assume. We need to see that file that actually contains the sql that received the error.

9

Hi @daryldoak

Understood, Pls find the file below,


--liquibase formatted sql
--changeset ACLU_CSV_PREMIUM:raw
--------------------------------------------------------
--  Constraints for Table ACLU_CSV_PREMIUM
--------------------------------------------------------

  ALTER TABLE "ACLU_CSV_PREMIUM" ADD CONSTRAINT "CKC_V_LASTUPD_PROG" CHECK (V_LASTUPD_PROG IS NOT NULL) ENABLE NOVALIDATE
  ALTER TABLE "ACLU_CSV_PREMIUM" ADD CONSTRAINT "CKC_V_LASTUPD_INFTIM" CHECK (V_LASTUPD_INFTIM IS NOT NULL) ENABLE NOVALIDATE
  ALTER TABLE "ACLU_CSV_PREMIUM" ADD CONSTRAINT "CKC_V_LASTUPD_USER" CHECK (V_LASTUPD_USER IS NOT NULL) ENABLE NOVALIDATE

Thanks,
Naren

10

ok, thanks. This is as I suspected. You need to end each statement with a semicolon delimiter.


--liquibase formatted sql
--changeset ACLU_CSV_PREMIUM:raw
--------------------------------------------------------
--  Constraints for Table ACLU_CSV_PREMIUM
--------------------------------------------------------

  ALTER TABLE "ACLU_CSV_PREMIUM" ADD CONSTRAINT "CKC_V_LASTUPD_PROG" CHECK (V_LASTUPD_PROG IS NOT NULL) ENABLE NOVALIDATE;
  ALTER TABLE "ACLU_CSV_PREMIUM" ADD CONSTRAINT "CKC_V_LASTUPD_INFTIM" CHECK (V_LASTUPD_INFTIM IS NOT NULL) ENABLE NOVALIDATE;
  ALTER TABLE "ACLU_CSV_PREMIUM" ADD CONSTRAINT "CKC_V_LASTUPD_USER" CHECK (V_LASTUPD_USER IS NOT NULL) ENABLE NOVALIDATE;