Error in running Liquibase update

Liquibase Development
1

Hi, I am new to Liquibase and I am having problem using sqlFile. I am currently using the free Liquibase Pro 4.3.4 and I am creating my change log in sql format.

I had a few SQL in my changelog and they were all working fine but as soon as I added the following line:

–changeset Jule.Yu:5 sqlFile path=“C:\Program Files\liquibase\test\sql\test_view1.sql”

then it started to complain as below:

Starting Liquibase at 18:19:54 (version 4.3.4 #55 built at 2021-04-19 20:18+0000)
Unexpected error running Liquibase: Validation Failed:
1 changes have validation failures
‘sql’ is required, dbchangelog_test.sql::5::Jule.Yu

I am simply trying to create a view in my test_view1.sql - CREATE OR REPLACE VIEW V_TEST2 AS SELECT id, name FROM jule_test2;

What am I missing here? It might have something simple that I missed but I can’t seem to find any reference to that error. I am really appreciated for any help.

Thanks,
Jule

2

Hi @lbjycgg

I’m not sure, but it might be because of the . in the author name. Could you try removing the . in author and simply using alphabets instead. Like below -

--changeset JuleYu:5 sqlFile path=“C:\Program Files\liquibase\test\sql\test_view1.sql”

I’m not sure if this is the root cause. But atleast we can try once.

Thanks!

3

Thanks for your hint rakhi. I did try try your suggestion but still ended up with the same error. In fact I had 4 prior changeset with ‘Jule.Yu’ as the author and they all worked fine. All I want to test in this new changeset is to execute a SQL script file instead of typing the entire script as part of the changeset.

4

oh okay!

Thanks for sharing. This actually added a point in my knowledge base of liquibase.

If we again try for a trial and error, may be you could place the SQL file in some directory whose path doesn’t have spaces in it.

May be something like this : D:/liquibase/test/sql/test_view1.sql

Worth a try. Please let us know if it works.

Thanks!
Rakhi Agrawal

5

Hi @lbjycgg I’m not sure if you’ve tried @rakhi 's suggestion yet about the file paths but I would have thought that the fact that the path is in quotes would mean that the spaces wouldn’t make any difference, but I could be wrong. Still worth trying that anyway. It is always better to avoid spaces in file paths and file names if possible as they can cause issues.

If that doesn’t work I think it would be worth posting the rest of your changelog with the existing changesets that did work as that might help identify whether there is anything different about the new one compared to the existing ones that might help to figure out what the issue is.

1 Like
6

Thank you both. I did try to remove the space in my path but still did not work. I finally gave up and restarted my changelog in XML instead of SQL then it started to give me a different error. After some trials and error I finally got it working. I removed the absolutely path because it seems the new version no longer supports that.

This is what I ended up with:

<changeSet id="1x" author="JuleYu">
    <sqlFile path="test_view1.sql" relativeToChangelogFile="true" />
</changeSet>
7

Sorry you faced trouble with it and Great you could get the thing working!

Thanks!

10

Having more trouble executing a SQL script which runs in SQLPLUS. I tried to use the runwith=“sqlplus” option but it seems having trouble connecting to the database, however the previous 3 SQL runs fine. Not sure if anyone has any insight on something I might have missed. Below is my changelog in xml:

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

<changeSet id="1x" author="JuleYu">
    <sqlFile path="test_view1.sql" relativeToChangelogFile="true" />
</changeSet>   
	
<changeSet id="2x" author="JuleYu">
    <sqlFile path="test_view2.sql" relativeToChangelogFile="true" />
</changeSet>		

<changeSet id="3x" author="JuleYu">
    <sqlFile path="test_view3.sql" relativeToChangelogFile="true" />
</changeSet>			

<changeSet id="4x" author="JuleYu" runWith="sqlplus">
    <sqlFile path="test_view4.sql" relativeToChangelogFile="true" />
</changeSet>

Here is my test_view4.sql
CREATE OR REPLACE VIEW &SCHEMA_OWNER…V_TEST4 AS
SELECT uwi, well_name
FROM well;

Here is my log from execution:
[2021-05-26 22:35:08] INFO [liquibase.executor] Executing with the ‘sqlplus’ executor
[2021-05-26 22:35:08] INFO [liquibase.change] As of Liquibase 4.0, cannot set resource accessor on com.datical.liquibase.ext.SqlPlusRunner. Must add it to the Scope
[2021-05-26 22:35:08] INFO [liquibase.change] Using the ‘sqlplus’ executable located at: ‘C:\oracle\product\12.1.0\client_1\BIN\sqlplus.exe’
[2021-05-26 22:35:08] INFO [liquibase.change] SQLPLUS command:
C:\oracle\product\12.1.0\client_1\BIN\sqlplus.exe -v -L SYSTEM/*****@//10.22.38.33:1521/qadb
[2021-05-26 22:35:09] INFO [liquibase.change]
SQLPlus: Release 12.1.0.1.0 Production

[2021-05-26 22:35:09] INFO [liquibase.executor] Successfully validated ‘sqlplus’
[2021-05-26 22:35:09] INFO [liquibase.change] As of Liquibase 4.0, cannot set resource accessor on com.datical.liquibase.ext.SqlPlusRunner. Must add it to the Scope
[2021-05-26 22:35:09] INFO [liquibase.change] Using the ‘sqlplus’ executable located at: ‘C:\oracle\product\12.1.0\client_1\BIN\sqlplus.exe’
[2021-05-26 22:35:09] INFO [liquibase.change] Creating the SQL run script
[2021-05-26 22:35:09] INFO [liquibase.change] Creating temporary spool file for ‘liquibase-spool-4x-JuleYu-’
[2021-05-26 22:35:09] INFO [liquibase.change] Creating temporary file for ‘liquibase-sqlplus-4x-JuleYu-’
[2021-05-26 22:35:09] INFO [liquibase.change] SQLPLUS command:
C:\oracle\product\12.1.0\client_1\BIN\sqlplus.exe -L SYSTEM/*****@//10.22.38.33:1521/qadb @C:\Users\juyu\AppData\Local\Temp\liquibase-sqlplus-4x-JuleYu-2389720093034777744.sql
[2021-05-26 22:35:09] INFO [liquibase.change]
SQLPlus: Release 12.1.0.1.0 Production on Wed May 26 22:35:09 2021

Copyright (c) 1982, 2013, Oracle. All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus