Skip to content
Contact us Go to Customer Portal
  • There are no suggestions because the search field is empty.

Oracle - Artifacts

Applies to
Platform: Oracle

The artifacts of an Oracle generator are SQL*Plus scripts plus PowerShell scripts that deploy and load them. This article explains what each file does and how the files call each other. Use it to find the script that failed and to see which project settings change the artifacts.

All Oracle generator configurations produce the same structure: Stage, Data Store, Data Vault, Data Mart and their combination. Only the layer scripts differ.

Files in the artifacts folder

  • deploy.ps1: checks that SQL*Plus is installed and runs deploy.sql with sqlplus /nolog. See Oracle - Deployment.
  • deploy.sql: runs SQL\deploy_all_layers.sql, then the load-control deployment and configuration when the artifacts contain them. It continues after a failed statement.
  • execute.ps1 and execute.sql: with Native Load Control, they run the execution script of the project as the load-control user. Then they print the number of failed or disabled loaders. See Oracle - Load data - Native Load Control.
  • replace_placeholders.ps1 and replacement_config.json: replace the placeholders of all files. See Replace the placeholders in the artifacts.
  • README.md: the generator configuration and version, the project, the branch and the number of generated objects.
  • SQL\: deploy_all_layers.sql and one script per layer, numbered in load order, for example L010_Stage.sql and L020_Raw Vault.sql.
  • LoadControl\: the load-control scripts and the Airflow DAG, depending on the project settings below.
  • Helpers\create_development_user.sql: an example script that creates the database users. See Oracle - Target environment.

How the deployment scripts connect

SQL\deploy_all_layers.sql handles one layer after the other. For each layer it connects as the layer user with conn and switches on SQL*Plus error logging. Then it runs the layer script. Afterwards it prints the number of failed statements, logged in the table SPERRORLOG of that user, and the invalid objects from USER_ERRORS.

When the project uses model objects imported from a linked project, the script starts with a GRANT block. It connects as the user of each linked layer and grants SELECT on the used objects to the consuming layer user. See Create GRANTs for Imported Objects (Project).

What a layer script creates

For each model object the layer script creates its tables, views and loader:

  • Tables: with the default Deployment Options, each table is dropped with DROP TABLE ... CASCADE CONSTRAINTS PURGE and created again, empty.
  • Views: created with CREATE OR REPLACE VIEW.
  • Loader: a PL/SQL package <table name>_LOADER with the procedure Run. It takes the load timestamp and returns the row counts and a message.
  • Grants: GRANT ALL on the tables and views to the user of the next layer, with GRANT OPTION. With Native Load Control, GRANT EXECUTE on each loader to the load-control schema.

Here <table name> is the generated name of the model object's table, for example STG_ST_CUSTOMER.

Settings that change the artifacts

<project> stands for the project name. In the DAG file name it is written in lower case with underscores, for example test_datavault_oracle_dag.py.

Example

The Data Vault project test.datavault.oracle with Native Load Control, Airflow and the default settings produces these files:

deploy.ps1
deploy.sql
execute.ps1
execute.sql
README.md
replace_placeholders.ps1
replacement_config.json
Helpers\create_development_user.sql
LoadControl\configure_loadcontrol.sql
LoadControl\deploy_loadcontrol.sql
LoadControl\L000_Loadcontrol_Deployment.sql
LoadControl\L010_Stage_Configuration.sql
LoadControl\L020_Raw Vault_Configuration.sql
LoadControl\L030_Business Vault_Configuration.sql
LoadControl\LoadControl_Execution_test.datavault.oracle.sql
LoadControl\test_datavault_oracle_dag.py
SQL\deploy_all_layers.sql
SQL\L010_Stage.sql
SQL\L020_Raw Vault.sql
SQL\L030_Business Vault.sql

A Data Mart project with Deploy LoadControl false has no deploy_loadcontrol.sql and no L000_Loadcontrol_Deployment.sql. It uses the load control that another project deployed.

Release differences

  • Generator 2.2: Helpers\create_development_user.sql is added.
  • Generator 2.2: the Airflow DAG calls the loaders through the Python driver oracledb. Up to Generator 2.0.1 it imported the Microsoft SQL Server provider.

Related articles