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

Oracle - Load data - Native Load Control

Applies to
Platform: Oracle

Load the deployed Oracle solution with the native load control by running execute.ps1. The script builds the execution plan of the project, runs every loader in dependency order and reports the loaders that did not succeed. Afterwards the view V_LASTLOAD shows the result of each loader.

How the load control plans, runs and logs loads is explained in Understand Load Control.

Prerequisites

Load the data

  1. Open PowerShell 7 in the artifacts folder, the folder that contains execute.ps1.
  2. Run .\execute.ps1. The script connects as the load-control user and runs LoadControl\LoadControl_Execution_<project>.sql. It returns when the execution has ended.
  3. Read NUMBER_OF_FAILED_OR_DISABLED_LOADERS in the output. 0 means that every loader succeeded.

The execution script builds the execution plan DEV_<project>, adds all load objects of the load configuration <project> and starts it. <project> is the project name, for example test.datavault.oracle. All loaders of one execution use the same load timestamp, taken from the database clock when the execution starts.

Check the result

Connect as the load-control user and query the last execution:

SELECT LOAD_OBJECT, EXECUTION_RESULT, NUMBER_OF_ROWS_INSERTED,
       ERROR_DESCRIPTION
FROM V_LASTLOAD;

Each loader has one row. EXECUTION_RESULT is Success for a loader that ran without error. More queries are in Monitor Load Control.

Example

The Data Vault project test.datavault.oracle has 105 load objects. Its first load on a local database took about 30 seconds and printed:

=> Executing LoadControl/LoadControl_Execution_test.datavault.oracle.sql

NUMBER_OF_FAILED_OR_DISABLED_LOADERS
------------------------------------
                                   0

=> LoadControl/LoadControl_Execution_test.datavault.oracle.sql finished

V_LASTLOAD then held 105 rows, all with EXECUTION_RESULT Success. All loaded tables had the same BG_LOADTIMESTAMP.

Run other execution plans

LoadControl_Execution_<project>.sql contains commented examples for other calls: a plan with the Stage layer only, removing load objects from a plan, removing a plan, and stopping or resuming an execution. On Oracle you call the procedures in a PL/SQL block. The procedures are described in Plan Load Control and Execute Load Control.

Load several projects with one load control

Several projects, for example a Data Vault and a Data Mart on top of it, can share one load control:

  1. Keep Deploy LoadControl true in the project that deploys the load control, and set it to false in the others.
  2. Enter the same loadcontrol#loadcontrol# values in the replacement configuration of every project.
  3. Deploy the load-control project first, then the others. Each project registers its own load configuration.
  4. Run .\execute.ps1 of each project in load order, for example the Data Vault before the Data Mart.

Deploying a project with Deploy LoadControl set to true recreates the load control tables and removes the configuration of the other projects, your execution plans and execution history. Deploy the load control with one project only and set Deploy LoadControl to false in the others.

Failure symptoms and recovery

Some loaders failed or were disabled

Message: NUMBER_OF_FAILED_OR_DISABLED_LOADERS greater than 0
Cause: A loader failed; the loaders that depend on it were not run.
Fix: Query V_LASTLOAD for rows whose EXECUTION_RESULT is not Success and read ERROR_DESCRIPTION. Correct the cause, then resume the execution as described in Execute Load Control.

The view V_LASTLOAD does not exist

Message: ORA-00942: table or view "<user name>"."V_LASTLOAD" does not exist
Cause: The load-control user is not the load-control schema, or the load control was not deployed.
Fix: Set user_name and schema_name of the load control to the same user and deploy again.

Load-control procedures are invalid after deployment

Message: PLS-00201 for DBMS_LOCK in the invalid objects of the deployment
Cause: The load-control user has no EXECUTE on SYS.DBMS_LOCK.
Fix: Grant it, deploy again and run .\execute.ps1.

Related articles