Liquibase Enterprise was formerly known as Datical DB.

Oracle Database Objects and Packaging

Owned by Former user (Deleted)
Last updated: Oct 17, 2023 by Amy Smith
3 min read

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 TypePackaging Folders (fixed folders)Package Methods (flexible folders)Archived?Notes
CREATE/ALTER/DROP DATABASE LINKsql_directdirectYes
CREATE/ALTER/DROP FUNCTIONfunctionstoredlogicNo

Use CREATE OR REPLACE rather than CREATE alone. 

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

CREATE/ALTER/DROP INDEXddl, ddl_direct, sql_directconvert, ddl_direct, directYes
CREATE/ALTER/DROP QUEUEsql_directdirectYes
CREATE/ALTER/DROP SEQUENCEddl, ddl_direct, sql_directconvert, ddl_direct, directYes
CREATE/ALTER/DROP PUBLIC SYNONYMsql_directdirectYesAlternatively, use Datical Auto-Synonyms.
CREATE/ALTER/DROP PRIVATE SYNONYMddl, ddl_direct, sql_directconvert, ddl_direct, directYes

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_directconvert, ddl_direct, directYes
CREATE/ALTER/DROP GLOBAL TEMPORARY TABLEddl, ddl_direct, sql_directconvert, ddl_direct, directYes
CREATE/ALTER/DROP MATERIALIZED VIEWsql_directdirectYes
CREATE/ALTER/DROP PACKAGEpackagestoredlogicNo

Use CREATE OR REPLACE rather than CREATE alone. 

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

CREATE/ALTER/DROP PACKAGE BODYpackagebodystoredlogicNo

Use CREATE OR REPLACE rather than CREATE alone. 

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

CREATE/ALTER/DROP PROCEDUREprocedurestoredlogicNo

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

storedlogicNo

Use CREATE OR REPLACE rather than CREATE alone. 

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

CREATE/ALTER/DROP TYPEsql_directdirectYesEach type must be in its own file and end with a '/'.
CREATE/ALTER/DROP VIEW

view

storedlogicNo

Use CREATE OR REPLACE rather than CREATE alone. 

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

CREATE/ALTER/DROP VIEW INDEXddl, ddl_direct, sql_directconvert, ddl_direct, directYes
CREATE/ALTER/DROP CURSORsql_directdirectYes
CREATE/ALTER/DROP DIRECTORYsql_directdirectYes
RENAME <DB_OBJECT>sql_directdirectYes
OperationPackaging Folder (fixed folders)Package Methods (flexible folders)Archived?Notes
INSERT, UPDATE, DELETE, SELECTdata_dmldata_dmlYes
GRANT, REVOKEsql_directdirectYesAlternatively, 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-2022 - Proprietary and Confidential