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 -
procedurefolder - Functions -
functionfolder - Package -
packagefolder - Package Body -
packagebodyfolder - Views -
viewfolder - Triggers -
triggerfolder
- Procedures -
- 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 Folder | Archived? | Notes |
|---|---|---|---|
| CREATE/ALTER/DROP DATABASE LINK | sql_direct | Yes | |
| CREATE/ALTER/DROP FUNCTION | function | 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 | | Yes | |
| CREATE/ALTER/DROP QUEUE | sql_direct | Yes | |
| CREATE/ALTER/DROP SEQUENCE | | Yes | |
| CREATE/ALTER/DROP PUBLIC SYNONYM | | Yes | Alternatively, use Datical Auto-Synonyms |
| CREATE/ALTER/DROP PRIVATE SYNONYM | | Yes | Alternatively, use Datical Auto-Synonyms |
CREATE/ALTER/DROP TABLE | | Yes | |
| CREATE/ALTER/DROP GLOBAL TEMPORARY TABLE | | Yes | |
| CREATE/ALTER/DROP MATERIALIZED VIEW | sql_direct | Yes | |
| CREATE/ALTER/DROP PACKAGE | package | 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 | 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 | 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 |
| 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 | Yes | Each type must be in its own file and end with a '/'. |
| CREATE/ALTER/DROP VIEW |
| 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 | | Yes | |
| CREATE/ALTER/DROP CURSOR | sql_direct | Yes | |
| CREATE/ALTER/DROP DIRECTORY | sql_direct | Yes | |
| RENAME <DB_OBJECT> | sql_direct | Yes |
| Operation | Packaging Folder | Archived? | Notes |
|---|---|---|---|
| INSERT, UPDATE, DELETE, SELECT | data_dml | Yes | |
| GRANT, REVOKE | sql_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 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 sqplus 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: