3 Using SQL*Plus to Schedule and Execute Jobs

The SQLPLUS_EXEC_TEMPLATE SQL Script

This script enables you to start the ETL process from SQL*Plus, and to use scheduling tools such as cron, AT, Autosys, and Tivoli.

The sqlplus_exec_template.sql script is located in the following directory: ORACLE_HOME/owb/rtp/sql.

Return Value

Syntax

SQLPLUS_EXEC_TEMPLATE rt_owner location task_type task_name 
     system_params custom_params

Arguments

Provide a value for each of the following arguments.

• rt_owner: The repository owner

• location: For PL/SQL mappings and process flows, specify the location you used for deployment.

  For SQL*Loader and SAP mappings, set this parameter to PlatformSchema. This is a case-sensitive variable.

• task_type: Enter the appropriate task type for the mapping or the process flow:

  • PLSQLMAP: PL/SQL mapping

  • SQLLOADERCONTROLFILE: SQL*Loader mapping

  • PROCESSFLOW: Process flow

  • ABAPFILE: SAP mapping

  • DATAAUDITOR: Data Auditor mapping

  • SCHEDULEDJOB: Warehouse Builder scheduled job

• task_name: The physical name of the mapping or the process flow.

• system_params: Values of system parameters for this task type. These values override the default values. Enter the parameters in the form name=value. Separate multiple parameters with commas, and enclose the entire string in double quotes. A backslash (\) is the escape character, when you need to include commas or double quotes as literal text.

  The following examples are correct:

  ","
  "this_param=true"
  "this_param=true, that_param=2"
  

• custom_params: Values of a custom parameter defined for this task. Refer to system_params for the syntax.

Examples

In each of the following examples, you may need to provide the path to sqlplus.exe and to sqplus_exec_template.sql.

sqlplus user/password@tns_name @sqlplus_exec_template MY_RUNTIME MY_WAREHOUSE PLSQL MY_MAPPING "," ","

sqlplus user/password@tns_name @sqlplus_exec_template MY_RUNTIME PlatformSchema SQL_LOADER MY_LOAD "," ","

sqlplus user/password@tns_name @sqlplus_exec_template MY_RUNTIME MY_WORKFLOW PROCESS MY_PROCESS "," ","

sqlplus user/password@tns_name @sqlplus_exec_template MY_RUNTIME PlatformSchema ABAP MY_SAP "," ","

The WB_RT_API_EXEC.RUN_TASK Function

The RUN_TASK function of the WB_RT_API_EXEC PL/SQL package enables you to schedule and run the ETL process from Warehouse Builder.

Return Value

The return value is affected by the parameters of the function.

When background=0 and oem_friendly=0:

When background=0 and oem_friendly=1:

When background=1:

Syntax

RUN_TASK
     ( location        IN   VARCHAR2,
       task_type       IN   VARCHAR2,
       task_name       IN   VARCHAR2,
       custom_params   IN   VARCHAR2   DEFAULT NULL,
       system_params   IN   VARCHAR2   DEFAULT NULL,
       oem_friendly    IN   NUMBER     DEFAULT 0,
       background      IN   NUMBER     DEFAULT 0
     ) 
     RETURN NUMBER;

Provide a value for each of the following parameters:

• location: For PL/SQL mappings and process flows, specify the location you used for deployment.

  For SQL*Loader and SAP mappings, set this parameter to PlatformSchema. This is a case-sensitive variable.

• task_type: Enter the appropriate task type for the mapping or the process flow:

  • PLSQLMAP or PLSQL: PL/SQL mapping

  • SQLLoader or SQLLoaderControlFile or SQLLoaderMap: SQL*Loader mapping

  • Process or ProcessFlow: Process flow

  • ABAPFile or SAPMap or SAP: SAP mapping

  • DataAuditor: Data auditor mapping

  • ScheduledJob: Warehouse Builder schedule object

  • AppsCMScheduler: Concurrent Manager schedule job

  • DBMSScheduler: Database schedule job

  Note:

  Previously, you could specify task_type using both, a numeric value as well as a literal value. For example, you could use 3 to specify ProcessFlow, or 4 to specify SAP and so on. But starting with 11g Release 1 (11.1), numeric values are no longer valid. It is mandatory to use the literal value. The value can be in uppercase, lowercase, or in mixed case. For example, PROCESSFLOW, ProcessFlow, and processflow are all valid.

• task_name: The name of the mapping or the process flow.

• custom_params: Values of a custom parameter defined for this task. Refer to system_params for the syntax.

• system_params: Values of system parameters for this task type. These values override the default values. Enter the parameters in the form name=value. Separate multiple parameters with commas, and enclose the entire string in double quotes. A backslash (\) is the escape character, when you need to include commas or double quotes as literal text.

  The following examples are correct:

  ","
  "this_param=true"
  "this_param=true, that_param=2"
  

• oem_friendly: Controls the return values. Set to 1 for execution in Enterprise Manager, or set to 0 for other environments.

• background: Controls execution of the task. Set to 1 for background, or set to 0 for foreground.

Example

The following example displays the return value of the function, which runs a mapping named CUSTOMER_MAP in SALES_TARGET_LOCATION.

BEGIN
        DBMS_OUTPUT.PUT_LINE('Result: ' || TO_CHAR(gccrep.wb_rt_api_exec.run_task(
'SALES_TARGET_LOCATION','PLSQLMAP','CUSTOMER_MAP', null, null, 1)));
END;