3 Using SQL*Plus to Schedule and Execute Jobs

After you design and configure the logical definitions of your target system, you can deploy and create the physical instance of your target. You can then start deployed mapping and process flow scripts to load or update your data.

This chapter contains the following topics:

Managing Jobs Using SQL Scripts

Numerous SQL scripts are installed with Warehouse Builder so that you can manage jobs using other administrative tools.

Table 3-1 describes the scripts that you can use to manage deployment jobs, execution jobs, and Control Centers from SQL*Plus. The scripts are located in \owb\rtp\sql in the Oracle home directory for Warehouse Builder. Comments in the scripts explain how to use them.

Table 3-1 SQL Scripts for Managing Jobs and Control Centers

Script Description

abort_exec_request

Stops an execution that is currently busy.

abort_unit_request

Stops a deployment job at the unit level.

A deployment unit is a collection of objects that are being deployed to the same location with the same deployment action.

deactivate_deployment

Deactivates a deployment job.

deactivate_execution

Deactivates an execution job.

delete_warehouse_object

Deletes an object from a Warehouse Builder repository.

display_platform_property

Displays the value of a platform property. These properties control how the Control Center service behaves.

expedite_exec_request

Moves a deployment job to the top of the list of pending jobs.

list_requests

Lists the details of any active deployment or execution requests.

oem_exec_background_template

Starts a specified job in the background. This template must be copied into a user-defined SQL*Plus job in Enterprise Manager. You can obtain the task status and return result from the public views. The views have names that begin ALL_RT_*.

oem_exec_template

Creates a new, parameterized job or submits a new job for immediate execution. This template must be copied into a user-defined SQL*Plus job in Enterprise Manager.

print_exec_details

Prints the audit execution hierarchy and details about the individual executions associated with an audit ID.

print_running_exec_details

Prints the audit execution hierarchy and details of all executions that are incomplete and were started since a provided date.

purge_audit_template

Purges deployment audit data or execution audit data.

reset_repository

Resets the registration details for a Control Center.

rtrepos_report

Displays the details of the runtime repository and its registered locations.

service_doctor

Displays diagnostics about the Control Center repository and its service.

set_oem_home

Sets the platform properties associated with an Enterprise Manager home directory. These properties enable the Control Center to locate Enterprise Manager components.

set_platform_property

Sets the value of a platform property. These properties control how the Control Center service behaves.

set_repository_password

Sets the repository password, which is used by the Control Center service at startup.

show_service

Displays the current status of the Control Center service.

sqlplus_exec_background_template

Starts the specified object in the background. You can obtain the task status and return result from the public views. The views have names that begin ALL_RT_*

sqlplus_exec_template

Starts a job as described in "Starting ETL Jobs in SQL*Plus".

start_service

Starts a Control Center Service.

stop_service

Stops a Control Center Service.

Starting ETL Jobs in SQL*Plus

In addition to executing objects using the Control Center Manager, you can use SQL*Plus. To do this, use a script provided with Warehouse Builder named sqlplus_exec_template. Alternatively, you can use sqlplus_exec_background_template to run a job in the background.

Take these steps to run the SQLPLUS_EXEC_TEMPLATE script in SQL*Plus:

  1. From the Warehouse Builder Tools menu, choose SQL*Plus.

    The SQL*Plus window opens.

  2. Connect as a Warehouse Builder user, not as a repository owner.

  3. Start the script, using syntax such as the following.

    @%ORACLE_HOME%\owb\rtp\sql\sqlplus_exec_template MY_RUNTIME MY_WAREHOUSE PLSQL MY_MAPPING "," ","

See Also:

The SQLPLUS_EXEC_TEMPLATE SQL Script for a complete description of the syntax.

Scheduling ETL Jobs in Oracle Enterprise Manager

After you successfully deployed a mapping or a process flow, you can schedule it to run in Oracle Enterprise Manager. This is an alternative to using the Warehouse Builder scheduler described in the Oracle Warehouse Builder User's Guide.

See Also:

Oracle Enterprise Manager Concepts and the Enterprise Manager Help system for information about creating jobs and schedules.

To schedule a mapping or process flow in Enterprise Manager:

  1. Successfully deploy the mapping or process flow in Warehouse Builder.

  2. Connect to Enterprise Manager as a Warehouse Builder repository user or owner.

  3. Create a scheduler job that uses the WB_RT_API_EXEC.RUN_TASK function in a PL/SQL block.

    For more information about this function, see "The WB_RT_API_EXEC.RUN_TASK Function".

  4. Create a schedule for running the job.

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


1 = Success
2 = Warning
3 = Error

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:


1 = Success
2 = Warning
3 = Error

When background=0 and oem_friendly=1:


0 = Success or Warning
3 = Error

When background=1:


0 = Task successfully submitted for execution
1 = Task not successfully submitted

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;

Managing a Control Center

The repository owner may occasionally need to run a SQL script to manager a control center.

Disabling Automatic Recovery

Warehouse Builder automatically restarts jobs that were interrupted as the result of Oracle Database shutting down. You can control this behavior for all jobs associated with a particular Control Center by setting the RECOVERY platform property:

  • TRUE: Automatically restarts jobs. (Default)

  • FALSE: Jobs require manual restart.

To turn off automatic recovery:

  1. Open SQL*Plus and connect as the repository owner.

  2. Use the SET_PLATFORM_PROPERTY script to set the RECOVERY property to FALSE.

    The following example sets the property on a Windows platform:

    @%ORACLE_HOME%\owb\rtp\sql\set_platform_property RECOVERY FALSE

    ORACLE_HOME is the Oracle home directory for Warehouse Builder.

Unregistering Locations

If a location cannot be unregistered using the Control Center Manager or an OMB*Plus script, you can use a SQL script. This problem may occur if the control center becomes inaccessible for any reason, such as disk failure or simply deleting the control center without first unregistering the locations.

Note:

Only use this method of unregistering locations when the usual methods are unsuccessful.

To unregister a location:

  1. Open SQL*Plus and connect as the repository owner.

  2. Run the unregister_location script. The following example shows the syntax on Windows:

    @%ORACLE_HOME%\owb\misc\unregister_location

    ORACLE_HOME is the Oracle home directory for Warehouse Builder.