OWB - How to start a job (Mapping, Process, ...) from SQLPlus

> Data Integration Tool (ETL/ELT) > Oracle Warehouse Builder

1 - About

A job for OWB is :

  • a PL/SQL Mapping
  • a SQL*Loader Mapping
  • a ProcessFlow
  • a SAP Mapping
  • a DataAuditor Mapping
  • a Scheduled Job

To start a job from SqlPlus, you must use :

  • the script sqlplus_exec_template.sql
  • located in the repository OWB_HOME\owb\rtp\sql

Open it to have more information.

3 - Usage

3.1 - SYNOPSYS

@sqlplus_exec_template.sql rt_owner location_name {PLSQLMAP | SQLLOADERCONTROLFILE | PROCESSFLOW | ABAPFILE | DATAAUDITOR | SCHEDULEDJOB} 
[parent]/task_name system_params custom_params
rt_owner      := e.g. MY_RUNTIME      - Name of the Runtime Repository Owner

location_name :- e.g. MY_WAREHOUSE    - Physical Name of the Location to which this task was deployed
                                        (i.e. a DB Location or a Process Location or the Platform Schema)
                                        Note: Always use "PlaformSchema" for SQL_LOADER and SAP types.

task_type     :- PLSQLMAP             - OWB PL/SQL Mapping
              |  SQLLOADERCONTROLFILE - OWB SQL*Loader Mapping
              |  PROCESSFLOW          - OWB ProcessFlow
              |  ABAPFILE             - OWB SAP Mapping
              |  DATAAUDITOR          - OWB DataAuditor Mapping
              |  SCHEDULEDJOB         - OWB Scheduled Job

task_name     :- e.g. MY_MAPPING      - Physical Name of the Deployed Object. This can be optionally qualified
                                        by the name of a deployed parent, such as the Processflow Package name
                                        of a Processflow. A module name cannot be used here because it is not
                                        a deployable object.

system_params :- { , | (name = value [, name = value]...)}
                 e.g. ","
                 or   MY_PARAM=1,YOUR_PARAM=true

custom_params :- { , | (name = value [, name = value]...)}
                 e.g. ","
                 or   MY_PARAM=1,YOUR_PARAM=true
Advertising

3.2 - RETURNS

  • 1 if task reports SUCCESS,
  • 2 if WARNING,
  • 3 if ERROR

4 - Example

  • for a mapping without parameters :
@sqlplus_exec_template REPO_OWNER_NAME LOCATION_NAME PLSQL MAPPING_NAME "," ","
  • for a process with custom parameters :
@sqlplus_exec_template.sql REPO_OWNER_NAME PROCESS_FLOW_LOCATION PROCESS_FLOW PROCESS_FLOW_NAME "," "TIME_DIM_START_DD_MON_YYYY=01012000,TIME_DIM_YEAR_NUMBER=10"
  • a complete example in the shell Dos to start a mapping
C:\Documents AND Settings\ngerard>SET SQLPATH=E:\oracle\product\10.2.0\owb_1\owb\rtp\SQL
 
C:\Documents AND Settings\ngerard>sqlplus REPO_USER/Password@SID
 
SQL*Plus: RELEASE 10.2.0.3.0 - Production ON Wo Feb 18 04:53:53 2009
 
Copyright (c) 1982, 2006, Oracle.  ALL Rights Reserved.
 
 
Connected TO:
Oracle Database 10g Enterprise Edition RELEASE 10.2.0.3.0 - Production
WITH the Partitioning, OLAP AND Data Mining options
 
SQL> SET serveroutput ON;
SQL> @sqlplus_exec_template REPO_OWNER QS_STG_SAP7_LOCATION PLSQL TEST_ADRESS "," ","
 
Session altered.
 
 
Role SET.
 
Stage 1: Decoding Parameters
|  location_name=QS_STG_SAP7_LOCATION
|  task_type=PLSQL
|  task_name=TEST_ADRESS
Stage 2: Opening Task
|  l_audit_execution_id=15066
Stage 3: Overriding Parameters
Stage 4: Executing Task
|  l_audit_result=1 (SUCCESS)
Stage 5: Closing Task
Stage 6: Processing Result
|  EXIT=1
 
PL/SQL PROCEDURE successfully completed.
 
Disconnected FROM Oracle Database 10g Enterprise Edition RELEASE 10.2.0.3.0 - Production
WITH the Partitioning, OLAP AND Data Mining options
 
C:\Documents AND Settings\ngerard>

5 - Reference

11g Release 1 (11.1)

Advertising