/
Oracle Database Objects and Packaging

Liquibase Enterprise was formerly known as Datical DB.

Oracle Database Objects and Packaging

Oct 17, 2023

    Qualifying Object Names

    • For a Single-Schema project, you can qualify object names with the schema name. If they are left unqualified, Datical uses the Connection Schema, or the Default Schema (if specified).

    • For a Multi-Schema Project, you must qualify object names with the schema name so they get deployed to the correct schema during packaging.

    • You can use the schemaName attribute in the metadata.properties file to indicate the default schema. See Using the metadata.properties file. 

    Oracle SCM Packaging Patterns

    Note that Datical packages non-transient files differently from transient files. Place scripts for Oracle objects in packaging folders as follows. 

    • Non-transient - In-place editing, packaging and no archiving of database changes so they can be managed similar to application code. This applies to the following object types:

      • Procedures - procedure folder

      • Functions - function folder

      • Package  - package folder

      • Package Body - packagebody folder

      • Views  - view folder

      • Triggers - trigger folder

    • Transient - Database changes are archived after packaging (all other folders).

    Oracle Packaging Folders

    Use the specified folder for listed Oracle database operations. 

    Note that using CREATE OR REPLACE rather than CREATE alone (see Notes) allows stored-logic SQL scripts to be modified and repackaged. 

    Object Type

    Packaging Folders (fixed folders)

    Package Methods (flexible folders)

    Archived?

    Notes

    Object Type

    Packaging Folders (fixed folders)

    Package Methods (flexible folders)

    Archived?

    Notes

    CREATE/ALTER/DROP DATABASE LINK

    sql_direct

    direct

    Yes

     

    CREATE/ALTER/DROP FUNCTION

    function

    storedlogic

    No

    Use CREATE OR REPLACE rather than CREATE alone. 

    Each function must be in its own file and end with a '/'.

    CREATE/ALTER/DROP INDEX

    ddl, ddl_direct, sql_direct

    convert, ddl_direct, direct

    Yes

     

    CREATE/ALTER/DROP QUEUE

    sql_direct

    direct

    Yes

     

    CREATE/ALTER/DROP SEQUENCE

    ddl, ddl_direct, sql_direct

    convert, ddl_direct, direct

    Yes

     

    CREATE/ALTER/DROP PUBLIC SYNONYM

    sql_direct

    direct

    Yes

    Alternatively, use Datical Auto-Synonyms.

    CREATE/ALTER/DROP PRIVATE SYNONYM

    ddl, ddl_direct, sql_direct

    convert, ddl_direct, direct

    Yes

    For ALTER and CREATE OR REPLACE synonym use ddl_direct or sql_direct/direct. (Do not use ddl/convert with ALTER and CREATE OR REPLACE synonym.)

    Alternatively, use Datical Auto-Synonyms.

    CREATE/ALTER/DROP TABLE

    ddl, ddl_direct, sql_direct

    convert, ddl_direct, direct

    Yes

     

    CREATE/ALTER/DROP GLOBAL TEMPORARY TABLE

    ddl, ddl_direct, sql_direct

    convert, ddl_direct, direct

    Yes

     

    CREATE/ALTER/DROP MATERIALIZED VIEW

    sql_direct

    direct

    Yes

     

    CREATE/ALTER/DROP PACKAGE

    package

    storedlogic

    No

    Use CREATE OR REPLACE rather than CREATE alone. 

    Each package must be in its own file and end with a '/'.

    CREATE/ALTER/DROP PACKAGE BODY

    packagebody

    storedlogic

    No

    Use CREATE OR REPLACE rather than CREATE alone. 

    Each package body must be in its own file and end with a '/'.

    CREATE/ALTER/DROP PROCEDURE

    procedure

    storedlogic

    No

    Use CREATE OR REPLACE rather than CREATE alone. 

    Each procedure must be in its own file and end with a '/'.

    CREATE/ALTER/DROP TRIGGER

    trigger

    storedlogic

    No

    Use CREATE OR REPLACE rather than CREATE alone. 

    Each trigger must be in its own file and end with a '/'.

    CREATE/ALTER/DROP TYPE

    sql_direct

    direct

    Yes

    Each type must be in its own file and end with a '/'.

    CREATE/ALTER/DROP VIEW

    view

    storedlogic

    No

    Use CREATE OR REPLACE rather than CREATE alone. 

    Each view must be in its own file and end with a '/'.

    CREATE/ALTER/DROP VIEW INDEX

    ddl, ddl_direct, sql_direct

    convert, ddl_direct, direct

    Yes

     

    CREATE/ALTER/DROP CURSOR

    sql_direct

    direct

    Yes

     

    CREATE/ALTER/DROP DIRECTORY

    sql_direct

    direct

    Yes

     

    RENAME <DB_OBJECT>

    sql_direct

    direct

    Yes

     

    Operation

    Packaging Folder (fixed folders)

    Package Methods (flexible folders)

    Archived?

    Notes

    Operation

    Packaging Folder (fixed folders)

    Package Methods (flexible folders)

    Archived?

    Notes

    INSERT, UPDATE, DELETE, SELECT

    data_dml

    data_dml

    Yes

     

    GRANT, REVOKE

    sql_direct

    direct

    Yes

    Alternatively, use Datical Auto-Permissions.

    Error Handling

    Do not put error-handling statements in the SQL scripts. 

    For Oracle databases, do not include WHENEVER SQLERROR statements with an object definition. 

    Datical DB has its own error-handling that wraps SQL scripts. Specifying error-handling within the statements is not necessary and can prevent the script from being simulated in Forecast. 

    Deployment Packager fails with an error if it encounters statements before an object definition for scripts in a stored logic folders or where packageMethod is defined as packageMethod=STOREDLOGIC.

    Oracle and PL/SQL

    • We recommend that you do not put error handling statements in your scripts, because Datical DB has its own error-handling that wraps SQL scripts.  However, If you include error-handling code, use the raise_application_error() method as part of your catch routine. Datical relies on raise_application_error() to determine the status of a change (success, failure). 

    • Datical creates and uses a DATICAL_SPERRORLOG table in the Datical tracking schema. It is re-created as needed when a sqlplus change is deployed. 

     

     

    See also these pages for overview of packaging workflows, which packaging methods or folders to use for which types of changes, and SQL Parser for Oracle:

    Overview of Packaging

    Writing SQL Scripts for Liquibase Enterprise

    Fixed Folder Names

    Flexible Folder Names

    Using SQL Parser

      Copyright © Liquibase 2012-2026 - Proprietary and Confidential