Liquibase not compilng oracle pkb/pks packges,

Question

I am implementing liquibase for database deployment, new to liquibase, learning it. Question: I have pks/pkb packages, I want to deploy them using liquibase, I have purposely added errors in pkb package(change the function name which doesn't exist in pks). Liquibase always deploying those packages to database with successful state but when I am looking at package using Oracle SQL developer its having compilation error. Liquibase should the fail the deployment if there is an error in package. What I feel: If pks/pkb are having any compilation error, liquibase should not deploy them and should through an error

Note: I am using packages file path, "

My Changelog file

<?xml version="1.0" encoding="UTF-8" standalone="no"?> 
<databaseChangeLog 
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog" 
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" 
    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.2.xsd 
    http://www.liquibase.org/xml/ns/dbchangelog-ext 
    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">     
    <changeSet author="N_Pawar" id="86e6529" failOnError="true" dbms="oracle"> 
    <sqlFilePath="D:/Jenkins_Build/CentralLOB/Builds/dev/DevOps/Database/OraclePackage/Packages/PKG_ENTITYNOTESORDSDevOps.pkb" stripComments="true" endDelimiter="\n/" relativeToChangelogFile="false"/> 
</changeSet> 
</databaseChangeLog>

looking for help, any suggestions/guidance much appreciated, Thank you in advance

Andreas Buschka Andreas Buschka · Accepted Answer · 2017-07-09T20:20:16

The problem here is that Liquibase (so far) only reports errors if the JDBC driver returns an error code from the CREATE (OR UPDATE) PACKAGE (BODY)... statement. However, Oracle RDBMS does not count errors during package (or package body) compilation as a statement error. With complex projects (esp. when there are circular type dependencies), you often cannot avoid compilation errors altogether, even when your code is fine. This is the reason it may not necessarily be a good idea to check for the object status immediately after every single procedure/package/function.

In our software project, we use an extra "change set" (which really only does validation) that runs every time at the end of the deployment (runAlways="true"). There, we execute the PL/SQL block below (DBMS_UTILITY should not need any special EXECUTE privileges IIRC). If there are any invalid objects in the schema (which could also be different object types, e.g. synonyms...) a statement/runtime exception will be thrown. In our development team, we have made it a rule that after every successful schema migration, no invalid objects may exist.

Note: If you use one Liquibase session to change objects in multiple schemas, you will need to adapt the script below to support that. The script was made for single-schema deployments.

DECLARE
  v_num_errors INTEGER;
BEGIN
  /* Executing this twice seems to be necessary with Oracle on occasion, cannot really say why */
  DBMS_UTILITY.COMPILE_SCHEMA(schema => USER, compile_all => FALSE);
  DBMS_UTILITY.COMPILE_SCHEMA(schema => USER, compile_all => FALSE);

  SELECT COUNT(*) INTO v_num_errors FROM user_objects WHERE status<>'VALID';

  IF v_num_errors > 0 THEN
    RAISE_APPLICATION_ERROR(-20000, 'At least one object in the schema is invalid. Please execute '
      || 'SELECT * FROM user_errors to see the details.');
  END IF;
END;
/

Liquibase not compilng oracle pkb/pks packges,

My Changelog file

1 Answers