Create Procedure ends up with ORA-00900

tyran11 · September 16, 2020, 10:48pm

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.

hope99 · September 17, 2020, 1:53pm

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"/>

hope99 · September 17, 2020, 1:54pm

splitStatements="false"

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

tyran11 · September 17, 2020, 10:13pm

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'.

hope99 · September 18, 2020, 2:07pm

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>

tyran11 · September 18, 2020, 8:09pm

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.

hope99 · September 18, 2020, 8:35pm

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

tyran11 · September 21, 2020, 4:38pm

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>

hope99 · September 21, 2020, 6:18pm

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

tyran11 · September 22, 2020, 8:17pm

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

hope99 · September 23, 2020, 9:46pm

@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

yarheld88 · August 30, 2021, 12:38pm

Hi,
try to change the EOL character of the .sql file . Open it on notepad++ , then Edit > End of line character conversion > UNIX format (LF) .

Hope it helps.
Valerio

Topic		Replies	Views
Formatted SQL, endDelimiter issue General Discussion	1	3871	July 29, 2014
Invoking oracle stored procedure package with sqlFile General Discussion	6	1719	April 17, 2023
Script working fine from sqlplus (oracle) not in liquibase General Discussion	4	553	August 27, 2021
XML parsing error? General Discussion	10	668	November 22, 2010
Liquibase formatted SQL General Discussion	6	1426	July 14, 2011

Create Procedure ends up with ORA-00900

Related Topics