Create Procedure ends up with ORA-00900

I am exploring Liquibase and hopefully can introduce into our dev environment. However, I am running into an error and i can’t get rid of it. I am simply trying to add a procedure.

create or replace PROCEDURE AddPerson (NewName varchar2) 
AS
BEGIN
   INSERT INTO TEMPTABLE1 (NAME) VALUES(NewName);
END;
/

error is as follows

Unexpected error running Liquibase: ORA-00900: invalid SQL statement
 [Failed SQL: (900) END]
liquibase.exception.MigrationFailedException: Migration failed for change set 
./ChangeLog1212.xml::1::chuan.he:

my changelog xml for that particular piece is

	<changeSet author="chuan.he" id="1">
	<sqlFile path="./12.1.2/ADDPERSON.pls"/>		
    </changeSet>

interesting part is that this does not fail completely, it actually put a broken piece of procedure into the database, which is kinda worse than complete failure. below is what got put into DB

create or replace PROCEDURE AddPerson (NewName varchar2) 
AS
BEGIN
  INSERT INTO TEMPTABLE1 (NAME) VALUES(NewName)

so it ended at the first semicolon ; , making me think something wrong with it.

please help, i have no idea what the heck is going on.

To run procedural code , you will need to specify the end limiter, by default its is “;” but for procedure change it like so

<sqlFile endDelimiter="/"
                path="my.sql"/>
1 Like

splitStatements="false"

For single plsql code block in one file , this will work as well and remove the last “/” from code file

1 Like

got this though

Unexpected error running Liquibase: Error parsing line 10 column 60 of ./ChangeLog1212.xml: cvc- 
complex-type.3.2.2: Attribute 'endDelimiter' is not allowed to appear in element 'sqlFile'.
liquibase.exception.ChangeLogParseException: liquibase.exception.SetupException: Error parsing line 10 
column 60 of ./ChangeLog1212.xml: cvc-complex-type.3.2.2: Attribute 'endDelimiter' is not allowed to 
appear in element 'sqlFile'.

This should have worked

<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-3.8.xsd">

    <changeSet id="1"
               author="test">
        <sqlFile path="testSql.sql" relativeToChangelogFile="true" endDelimiter="/"/>
    </changeSet>

</databaseChangeLog>
Attribute 'endDelimiter' is not allowed to appear in element 'relativeToChangelogFile'.

is there something wrong with my liquibase? it doesn’t like relativeToChangelogFile or endDelimiter, i have community 3.8.0

it did like the splitStatements=“false” though, but that will limit me to one sql command per file.

what is the exact change log and dbms you are running?

I call from this powershell command, i am trying to run from a machine that has Oracle client 11, to make change to a Oracle 19 database, i am not running any fancy queries so the version shouldn’t matter.

& "$liquibase\liquibase.bat" --changeLogFile= "C:\liquibase\sql\ChangeLog.xml" --url="jdbc:oracle:thin:@mymachine:1234:CCTL" --driver="oracle.jdbc.OracleDriver" --classpath="C:\oracle\product\11.2.0\client_1\jdbc\lib\ojdbc8.jar" --username="admin as sysdba" --password=keymaker update | out-file $logPath -Append

This is the changelog.xml, which calls to next level of changelog.xml, one in question is Changelog1212.xml

<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.7"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.7
		 http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.7.xsd">

	<include  file="./ChangeLog121.xml"/>  
	<include  file="./ChangeLog1212.xml"/>
	
</databaseChangeLog>	

Then this is Changelog1212.xml, where weird things happen. My liquibase doesn’t like “relativeToChangelogFile” and “endDelimiter” for some reason.

<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.7"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.7
		 http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.7.xsd">

	<changeSet author="chuan.he" id="1">
		<sqlFile splitStatements="false" path="./12.1.2/ADDPERSON.pls"/>		
	</changeSet>

	<changeSet author="chuan.he" id="2">
		<sqlFile splitStatements="true"	relativeToChangelogFile="true" path="12.1.2/ADDPERSON.pls" endDelimiter="/"  />		
	</changeSet>

</databaseChangeLog>

The XML tag expected the .sql file that needs to be applied AFAIK. what is the content of ./12.1.2/ADDERSON.pls" file? Can you try running it as “.sql” instead ?

The sample changeset xml should look like so

<changeSet  author="liquibaseuser"  id="sqlFile-to-run-1">  
    <sqlFile  dbms="!h2, oracle"  
            encoding="UTF-8"  
            endDelimiter="/"  
            path="my/path/file.sql"  
            relativeToChangelogFile="true"  
            splitStatements="true"  
            stripComments="true"/>  
</changeSet>

I do not have oracle setup yet, once I do I can try it out. Please post what change you are applying

This is the content of addperson.pls, I changed it to addperson.sql and i still get the same error. i am really puzzled.

create or replace PROCEDURE AddPerson (NewName varchar2) 
AS
BEGIN
   INSERT INTO TEMPTABLE1 (NAME) VALUES(NewName);
END;
/

create or replace PROCEDURE AddPerson2 (NewName varchar2) 
AS
BEGIN
  INSERT INTO TEMPTABLE2 (NAME) VALUES(NewName);
END;
/

-----------------Database Update Log-----------------
Starting Liquibase at Tue, 22 Sep 2020 12:12:10 GMT-08:00 (version 3.8.0 built at 2019-08-15T20:38:06Z)
Liquibase Community 3.8.0 by Datical
Unexpected error running Liquibase: Error parsing line 14 column 114 of ./ChangeLog1212.xml: cvc-complex-type.3.2.2: Attribute ‘relativeToChangelogFile’ is not allowed to appear in element ‘sqlFile’.
liquibase.exception.ChangeLogParseException: liquibase.exception.SetupException: Error parsing line 14 column 114 of ./ChangeLog1212.xml: cvc-complex-type.3.2.2: Attribute ‘relativeToChangelogFile’ is not allowed to appear in element ‘sqlFile’.
at liquibase.parser.core.xml.AbstractChangeLogParser.parse(AbstractChangeLogParser.java:25) ~[liquibase.jar:na]
at liquibase.Liquibase.getDatabaseChangeLog(Liquibase.java:217) ~[liquibase.jar:na]
at liquibase.Liquibase.update(Liquibase.java:190) ~[liquibase.jar:na]
at liquibase.Liquibase.update(Liquibase.java:179) ~[liquibase.jar:na]
at liquibase.integration.commandline.Main.doMigration(Main.java:1223) [liquibase.jar:na]
at liquibase.integration.commandline.Main.run(Main.java:209) [liquibase.jar:na]
at liquibase.integration.commandline.Main.main(Main.java:132) [liquibase.jar:na]
Caused by: liquibase.exception.SetupException: Error parsing line 14 column 114 of ./ChangeLog1212.xml: cvc-complex-type.3.2.2: Attribute ‘relativeToChangelogFile’ is not allowed to appear in element ‘sqlFile’.
at liquibase.changelog.DatabaseChangeLog.handleChildNode(DatabaseChangeLog.java:359) ~[liquibase.jar:na]
at liquibase.changelog.DatabaseChangeLog.load(DatabaseChangeLog.java:308) ~[liquibase.jar:na]
at liquibase.parser.core.xml.AbstractChangeLogParser.parse(AbstractChangeLogParser.java:23) ~[liquibase.jar:na]
… 6 common frames omitted
Caused by: liquibase.exception.ChangeLogParseException: Error parsing line 14 column 114 of ./ChangeLog1212.xml: cvc-complex-type.3.2.2: Attribute ‘relativeToChangelogFile’ is not allowed to appear in element ‘sqlFile’.
at liquibase.parser.core.xml.XMLChangeLogSAXParser.parseToNode(XMLChangeLogSAXParser.java:120) ~[liquibase.jar:na]
at liquibase.parser.core.xml.AbstractChangeLogParser.parse(AbstractChangeLogParser.java:15) ~[liquibase.jar:na]
at liquibase.changelog.DatabaseChangeLog.include(DatabaseChangeLog.java:568) ~[liquibase.jar:na]
at liquibase.changelog.DatabaseChangeLog.handleChildNode(DatabaseChangeLog.java:351) ~[liquibase.jar:na]
… 8 common frames omitted
Caused by: org.xml.sax.SAXParseException: cvc-complex-type.3.2.2: Attribute ‘relativeToChangelogFile’ is not allowed to appear in element ‘sqlFile’.
at com.sun.org.apache.xerces.internal.util.ErrorHandlerWrapper.createSAXParseException(Unknown Source) ~[na:1.8.0_211]
at com.sun.org.apache.xerces.internal.util.ErrorHandlerWrapper.error(Unknown Source) ~[na:1.8.0_211]
at com.sun.org.apache.xerces.internal.impl.XMLErrorReporter.reportError(Unknown Source) ~[na:1.8.0_211]
at com.sun.org.apache.xerces.internal.impl.XMLErrorReporter.reportError(Unknown Source) ~[na:1.8.0_211]
at com.sun.org.apache.xerces.internal.impl.XMLErrorReporter.reportError(Unknown Source) ~[na:1.8.0_211]
at com.sun.org.apache.xerces.internal.impl.xs.XMLSchemaValidator$XSIErrorReporter.reportError(Unknown Source) ~[na:1.8.0_211]
at com.sun.org.apache.xerces.internal.impl.xs.XMLSchemaValidator.reportSchemaError(Unknown Source) ~[na:1.8.0_211]
at com.sun.org.apache.xerces.internal.impl.xs.XMLSchemaValidator.processAttributes(Unknown Source) ~[na:1.8.0_211]
at com.sun.org.apache.xerces.internal.impl.xs.XMLSchemaValidator.handleStartElement(Unknown Source) ~[na:1.8.0_211]
at com.sun.org.apache.xerces.internal.impl.xs.XMLSchemaValidator.emptyElement(Unknown Source) ~[na:1.8.0_211]
at com.sun.org.apache.xerces.internal.impl.XMLNSDocumentScannerImpl.scanStartElement(Unknown Source) ~[na:1.8.0_211]
at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl$FragmentContentDriver.next(Unknown Source) ~[na:1.8.0_211]
at com.sun.org.apache.xerces.internal.impl.XMLDocumentScannerImpl.next(Unknown Source) ~[na:1.8.0_211]
at com.sun.org.apache.xerces.internal.impl.XMLNSDocumentScannerImpl.next(Unknown Source) ~[na:1.8.0_211]
at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source) ~[na:1.8.0_211]
at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source) ~[na:1.8.0_211]
at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source) ~[na:1.8.0_211]
at com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(Unknown Source) ~[na:1.8.0_211]
at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(Unknown Source) ~[na:1.8.0_211]
at com.sun.org.apache.xerces.internal.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source) ~[na:1.8.0_211]
at liquibase.parser.core.xml.XMLChangeLogSAXParser.parseToNode(XMLChangeLogSAXParser.java:112) ~[liquibase.jar:na]
… 11 common frames omitted

For more information, please use the --logLevel flag

@tyran11
I tried it locally and it does accept the parameters endDelimiter etc as shown in image. NOTICE the XSD schema version change… to “dbchangelog-3.9.xsd” and main master change log as here

But , the plsql code does not work with two procedures in same sql file…still needs to be in two separate files , as even though it accepts it as parameter, it does not really treat it as delimiter and creates the entire “addperson.sql” code as one procedure which is then invalid (see attached screen shot)
so, if you can create separate sql file for each plsql code block its better ( best practice anyways)

You can try SQL Formatted change log if you need to keep all plsql code in one script.
let me know if any questions.

also look at this : https://oracle-base.com/articles/misc/liquibase-automating-your-database-deployments

1 Like