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

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.

@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

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

• 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>

• Using SQL*Plus to Schedule and Execute Jobs Oracle® Warehouse Builder API and Scripting Reference

11g Release 1 (11.1)