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
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	Alternatively, use Datical Auto-Synonyms Note that for ALTER and CREATE OR REPLACE only use ddl_direct or sql_direct/direct. (Do not use ddl/convert with ALTER and CREATE OR REPLACE.)
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
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.

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 Datical DB

Fixed Folder Names

Flexible Folder Names

Using SQL Parser