...
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 | | convert, ddl_direct, direct | Yes | |
| CREATE/ALTER/DROP QUEUE | sql_direct | direct | Yes | |
| CREATE/ALTER/DROP SEQUENCE | | convert, ddl_direct, direct | Yes | |
| CREATE/ALTER/DROP PUBLIC SYNONYM | | direct | Yes | Alternatively, use Datical Auto-Synonyms. |
| CREATE/ALTER/DROP PRIVATE SYNONYM | | 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 | | convert, ddl_direct, direct | Yes | |
| CREATE/ALTER/DROP GLOBAL TEMPORARY TABLE | | 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 |
| 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 |
| 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 | | 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.
...
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 onraise_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:
Writing SQL Scripts for Datical DBLiquibase Enterprise
...