SQLcl

Hi ,

as suggested by Jeff Smith moving my initial post to here
liquibase changeset dbms_output.put_line not showing anymore when proceeding update - Oracle Forums

As you will see the problem is around sql changeset that even though we set serveroutput on prior liquibase update execution will actually display dbms_output.put_line message ok in case of xml changeset but with sql changeset it will not . As our analyst and developers are used to deploy with sql script we want to ease the migration to liquibase and therefore keep using sql file .

Thanks in advance for your help

NB : I had paste all the content of the other post so you have all the information here .

“”"
###############################################################
## part1 ( me )

Hello there ,

My Name is Ricardo I'm a database administrator . I have been using liquibase on the past for many many year on a couple of my positions with different wrappers .

In my current job, we are evaluating the usage of liquibase making use of SQLcl . In the process I'm trying to cover all the requirements from our analyst developers and make sure that we can move forward .

They had basically 3 concerns

1. management of the character “&” within our string, aka set define. we have seen that within the context of sqlcl when we run liquibase command it is smart enough to know that we are not in a interactive session and process each of our script like we had specify set define off.
   ( Check - we are good to go there )
2. management of the encoding , our database is using cp1252 and in the past we had to make sure of two things prior running deployements script one that we set encoding cp1252 + that all the scripts that we about to be excuted are encoded as 1252 as well otherwise we encountered all kind of issues. within the context of sqlcl when we run liquibase command if the scripts are encoded in utf8 then there is magic going on but everything gets to the database correctly all the accents and special character treated as they should
   ( Check - we are good to go there )
3. managemet of dbms_output aka set serverouput on. within our scripts we have sometimes messages that are outputed and we need to keep those in the context of deployement log. within the context of sqlcl when we run liquibase command if prior execution of the script we execute the set serveroutput on , we ended with exactly what we need . With the version 24.1.1 that is ; with latest version no output at all are returned
   ( Problem with latest version / regression )

LIQUIBASE_POC@jdbc:oracle:thin:@corpdeve SQL>version
Oracle SQLDeveloper Command-Line (SQLcl) version: 24.1.1.0 build: 24.1.1.120.0826
LIQUIBASE_POC@jdbc:oracle:thin:@corpdeve SQL>lb up  -chf "./changelog-master.xml"
--Starting Liquibase at 2024-11-15T10:00:51.227452400 (version 4.25.0 #3966 built at 2023-11-10 23:07:29 UTC)
Running Changeset: 20241112-test-encoding-define/dbms-out.sql::dbms-out::ricardo

UPDATE SUMMARY
Run:                          1
Previously run:               5
Filtered out:                 0
-------------------------------
Total change sets:            6

Liquibase: Update has been successful. Rows affected: 1


Operation completed successfully.

Ricardo - this is an dbms_output.put_line test ...

LIQUIBASE_POC@jdbc:oracle:thin:@corpdeve SQL>

I'm sure you will be able to reproduce easily ; providind here just for reference the sql changeset we are using to test this behavior :

-- liquibase formatted sql 
--changeset ricardo:dbms-out splitStatements:false endDelimiter:/ 
--validCheckSum: 9:fc7b3180388c7f48e9df4dd1ed8cbbd2 

begin 
dbms_output.put_line('Ricardo - this is an dbms_output.put_line test ...'); 
end; 
/ 

--rollback empty; 

thanks in advance for the support.

###############################################################
## part2 ( answer from Jeff Smith )

can you move this post to the SQLcl forum?
https://forums.oracle.com/ords/apexds/domain/dev-community/category/sqlcl

I can test a dbms_output run for you, but 24.3.1 (it works) is the latest. Your version of SQLcl is about 8 months old

dbms_output from sqlcl script changeset

the changeSet:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog 
xmlns="http://www.liquibase.org/xml/ns/dbchangelog" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:n0="http://www.oracle.com/xml/ns/dbchangelog-ext" 
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog 
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
<changeSet id="abcd1234567890" author="Generated" failOnError="false" runAlways="true" >
<n0:runOracleScript objectName="MY_SCRIPT" objectType="SCRIPT" ownerName="thatJeffSmith" sourceType="STRING" replaceIfExists="false">
<n0:source><![CDATA[
set serveroutput on
begin
dbms_output.put_line('hello, it is me - from sqlcl liquibase changeSet');
end;
/
]]></n0:source>
</n0:runOracleScript>
</changeSet>
</databaseChangeLog>

###############################################################
## part3 ( me )

Hi thanks for the quick followup

We had no choice to revert to a way older version because of the problem listed below :
https://forums.oracle.com/ords/apexds/post/liquibase-issue-after-upgrade-to-24-2-0-5227#comment_41546041454371743055293850430586134844

problem that we saw you guys fixed with the latest version 👍.

I re-did some tests with you changeset and turns out we are both right . actually within the context of an xml changeset the output is correctly displayed as you mentionned and referenced below in annex:image1

however when we are using sql changeset we don't see the same behaviour in the latest version. reference below in annex:image2 . But we use to in the older version as annex:image3

Let me know if you want still to move all this to SQLcl forum. I will do.

Thank you

## annex

* image1

* image 2

* image3

“”"