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

Card Puncher Data Processing

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.

Usage

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


RETURNS

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

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>

Reference

11g Release 1 (11.1)





Discover More
Card Puncher Data Processing
Oracle Warehouse Builder

Owb Documentation - Understanding Data Quality Management White...



Share this page:
Follow us:
Task Runner