Oracle Guidelines
- For an Oracle Default Schema (single-schema) Project, database objects can be either fully qualified or unqualified with the schema name. If they are left unqualified, Datical DB will deploy the changes into the Connection Schema, or the Default Schema (if specified).
- For an Oracle Multi-Schema Project, database objects must be fully qualified with the schema name so they get deployed to the correct schema during packaging.
- You can use the schemaName attribute in the metadata.properties to indicate the default schema for all the scripts in the folder where the metadata.properties reside and its children as well.
Oracle SCM Packaging Patterns
Datical supports two development usage/packaging patterns:
- Permanent - 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 (when scripts are placed in the "/procedure" folder)
- Functions (when scripts are placed in the "/function" folder)
- Package (when scripts are placed in the "/package" folder)
- Package Body (when scripts are placed in the "/packagebody" folder)
- Views (when scripts are placed in the "/view" folder)
- Triggers (when scripts are placed in the "/trigger" folder)
- Transient - Database changes are archived after packaging (all other folders).
Oracle Packaging Folders
| Object Type | Packaging Folder | Archived? | Notes |
|---|---|---|---|
| CREATE/ALTER/DROP DATABASE LINK | /sql_direct | Yes | |
| CREATE/ALTER/DROP FUNCTION | /function | No | Each function must be ended by a '/' on its own line. |
| CREATE/ALTER/DROP INDEX | /ddl | Yes | |
| CREATE/ALTER/DROP QUEUE | /sql_direct | Yes | |
| CREATE/ALTER/DROP SEQUENCE | /ddl | Yes | |
| CREATE/ALTER/DROP SYNONYM | /ddl | Yes | Alternatively, use Datical Auto-Synonyms |
CREATE/ALTER/DROP TABLE | /ddl | Yes | |
| CREATE/ALTER/DROP GLOBAL TEMPORARY TABLE | /ddl | Yes | |
| CREATE/ALTER/DROP MATERIALIZED VIEW | /sql_direct | Yes | |
| CREATE/ALTER/DROP PACKAGE | /package | No | Each package must be ended by a '/' on its own line. |
| CREATE/ALTER/DROP PACKAGE BODY | /packagebody | No | Each package body must be ended by a '/' on its own line. |
| CREATE/ALTER/DROP PROCEDURE | /procedure | No | Each procedure must be ended by a '/' on its own line. |
| CREATE/ALTER/DROP TRIGGER | /trigger | No | Each trigger must be ended by a '/' on its own line. |
| CREATE/ALTER/DROP TYPE | /sql_direct | Yes | Each type must be ended by a '/' on its own line. |
| CREATE/ALTER/DROP VIEW | /view | No | |
| CREATE/ALTER/DROP VIEW INDEX | /ddl | 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 |