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

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


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/[email protected]

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)


Data Science
Data Analysis
Statistics
Data Science
Linear Algebra Mathematics
Trigonometry

Powered by ComboStrap