creating function using liquibase sql changelog is successful but function has compilation warning

Question

I am trying to create function in oracle 12c database using liquibase, below is the sql formatted changelog. I am doing negative testing to see what response i get, purposefully using wrong keyword returns

--liquibase formatted sql

--changeset your.name:1 failOnError:true

CREATE OR REPLACE FUNCTION get_tab1_count RETURNS NUMBER AS
  l_count  NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO   l_count
  FROM   person3;

  RETURN l_count;
END;
/

This change log gets updated successfully, but as expected in the database I have a function now which has compilation errors,

Compilation errors for FUNCTION GET_TAB1_COUNT

Error: PLS-00103: Encountered the symbol "RETURNS" when expecting one of the following:
( return compress compiled wrapped

my question is am i doing something wrong, that liquibase is giving a "update has been successful" message? Or is this expected behaviour? I have tried adding failOnError:true in the changelog to see if it will actually now fail as there is a compilation error during function creation, but it does not make any difference. I would think that, liquibase would give some sort of failure message when there is a compilation error like the above, am I wrong?

ronak ronak · Accepted Answer · 2020-10-29T19:31:16

Liquibase is just running your changeset as sql being passed to a JDBC connection.

quoting a colleague: "the jdbc connection gives us for a response and I THINK most databases are just "yes, you created that fine" even though it is not actually valid at this point. But, they don't tell us until we run it"

As a work around you could write a script that checks for compilation errors and fails when it finds any issues. Create an execute change set with runAlways="true"

creating function using liquibase sql changelog is successful but function has compilation warning

1 Answers