KADDACHI Hafedh's Oracle Blog: août 2012

jeudi 9 août 2012

Manual Upgrade of a 10gr2 Database To a 11gr2 Database

Introduction
The goal of this article is to give an example of an manual upgrade from 10gr2 database to 11gr2 database.
In my example, the 10gr2 database is BDTEST.

A. Check list to do Source Database

1 Clean up duplicate objects in the SYS and SYSTEM schemas
Some objects created under SYS schema during the installation of the DB, can be created also under SYSTEM schema with manaul launch scripts (for example for replication).
Exception for :
AQ$_SCHEDULES TABLE
AQ$_SCHEDULES_PRIMARY INDEX
DBMS_REPCAT_AUTH PACKAGE
DBMS_REPCAT_AUTH PACKAGE BODY

1-1 To find duplicate objects, run as SYS:
select object_name, object_type
from dba_objects
where object_name||object_type in
(select object_name||object_type
from dba_objects
where owner = 'SYS')
and owner = 'SYSTEM';

1-2 to drop duplicate objects, run as SYSTEM:
set pause off
set heading off
set pagesize 0
set feedback off
set verify off
spool duplicate_objects.sql
select 'DROP ' || object_type || ' SYSTEM.' || object_name || ';'
from dba_objects
where object_name||object_type in
(select object_name||object_type
from dba_objects
where owner = 'SYS')
and object_name not in ('AQ$_SCHEDULES', 'AQ$_SCHEDULES_PRIMARY', 'DBMS_REPCAT_AUTH', 'DBMS_REPCAT_AUTH' )
and owner = 'SYSTEM';
spool off
exit

2. Purge Recylce bin
purge the recycle bin beforethe upgrade.
SQL> purge DBA_RECYCLEBIN;

3. Cluster
If CLUSTER_DATABASE is set to TRUE, change it to FALSE before upgrading the database.

4. Pre-Upgrade Tool
To prepare your 10gr2 database for upgrade, you must first download the scirpt utility utlu112i_sql. Refer to My Oracle Support Document 884522.1: How to Download and Run Oracle's Database Pre-Upgrade Utility for details on downloading and running the Pre-Upgrade Utility. This SQL script checks the following:
1. Database configuration: Determines if the logfiles and datafiles are sized adequately for the upgrade
2. Initialization parameters: Reports which initialization parameters need changing, replacing or removing before the upgrade
3. Components: Which installed components will be upgraded
4. Miscellaneous Warnings: Any other situations requiring attention before the upgrade
5. Required tablespace: Ensure that the SYSAUX tablespace is created in the current database BEFORE the upgrade is carried out.
6. Timezone file version: Reports which file version is used and when/how to upgrade the timezone version.

Failure to run the pre-upgrade tool (utlu112i.sql) will result in the following error while running the catupgrd.sql script :

SQL> SELECT 
TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
2 FROM registry$database
3 
WHERE tz_version != (SELECT version from v$timezone_file);
SELECT 
TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
*
ERROR at line 
1:
ORA-01722: invalid number

From source server (10gr2) as sys:

SQL> spool /u03/upgrade/utlu112i_1.log
SQL> @/u03/upgrade/utlu112i_1.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 08-09-2012 11:25:35
Script Version: 11.2.0.1.0 Build: 004
**********************************************************************
Database:
**********************************************************************
--> name: BDTEST
--> version: 10.2.0.5.0
--> compatible: 10.2.0.1.0
--> blocksize: 8192
--> platform: Linux x86 64-bit
--> timezone file: V4
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 1331 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 34 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 721 MB
--> TBS_ADMIN_STREAM tablespace is adequate for the upgrade.
.... minimum required size: 3 MB
--> TBS_STREAM tablespace is adequate for the upgrade.
.... minimum required size: 3 MB
--> TBS_TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
--> background_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"
--> user_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Data Mining [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 11.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.5.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj.sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER BEMIS has 33 INVALID objects.
.... USER PUBLIC has 1 INVALID objects.
.... USER ADMINSTRM has 2 INVALID objects.
.... USER SYS has 2 INVALID objects.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data prior to upgrade.
WARNING: --> There are files which need media recovery.
.... Ensure no files need media recovery prior to upgrade.
WARNING: --> Sync standby database prior to upgrade.
WARNING: --> Your recycle bin contains 2359 object(s).
.... It is REQUIRED that the recycle bin is empty prior to upgrading
.... your database. The command:
PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
**********************************************************************
Oracle recommends removing all hidden parameters prior to upgrading.
To view existing hidden parameters execute the following command
while connected AS SYSDBA:
SELECT name,description from SYS.V$PARAMETER WHERE name
LIKE '\_%' ESCAPE '\'
Changes will need to be made in the init.ora or spfile.
**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.
To view existing non-default events execute the following commands
while connected AS SYSDBA:
Events:
SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'
Trace Events:
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'
Changes will need to be made in the init.ora or spfile.
**********************************************************************
SQL> spool off

Now, on the 10gr2 database BDTEST, perform the recomendations from utlu112i_1.log.

5. Compile invalid objects :

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql

6. Gather stats for tables that lacks statistics :

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

B. Upgrade

Do a cold backup pf BDTEST (10gr2) and move all the files to the 11gr2 server.
[oracle@**** ~]$ export ORACLE_SID=BDTEST
on the 11gr2 serveur :

[oracle@**** ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 9 10:31:25 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.
SQL> startup UPGRADE;
SQL> spool catupgrd.log
SQL> @?/rdbms/admin/catupgrd.sql
......

SQL>

SQL>@?/rdbms/admin/utlrp.sql

SQL>shutdown immediate;

SQL>startup

Execute the script utlu112i.sql that provides a summury of the upgrade :
SQL> @?/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 08-09-2012 14:27:06

**********************************************************************

Database:

**********************************************************************
--> name: BDTEST

--> version: 11.2.0.1.0

--> compatible: 11.2.0.0.0

--> blocksize: 8192

--> timezone file: V4

Database already upgraded; to rerun upgrade use rdbms/admin/catupgrd.sql.

PL/SQL procedure successfully completed.

SQL>

Main important changes
According to the oracle note Oracle 11gR2 Upgrade Companion [ID 785351.1], the main changes
in behavior between 10g and 11g are :

New Background Processes (Oracle Database 10g to 11g Change)

The following are the new database background process that you could be
running depending upon the features being used.

DBRM (database resource manager) process is responsible for setting resource plans and other resource manager related tasks.
DIA0 (diagnosability process 0) (only 0 is currently being used) is responsible for hang detection and deadlock resolution.
EMNC (event monitor coordinator) is the background server process used for database event management and notifications.
FBDA (flashback data archiver process) archives the historical rows of tracked tables into flashback data archives. Tracked tables are tables which are enabled for flashback archive. When a transaction containing DML on a tracked table commits, this process stores the pre-image of the rows into the flashback archive. It also keeps metadata on the current rows.
FBDA is also responsible for automatically managing the flashback data archive for space, organization, and retention and keeps track of how far the archiving of tracked transactions has occurred.
GTX0-j (global transaction) processes provide transparent support for XA global transactions in an Oracle RAC environment. The database autotunes the number of these processes based on the workload of XA global transactions. Global transaction processes are only seen in an Oracle RAC environment
GEN0 (general task execution process) performs required tasks including SQL and DML
GMON maintains disk membership in ASM disk groups.
MARK marks ASM allocation units as stale following a missed write to an offline disk.
SMCO (space management coordinator) process coordinates the execution of various space management related tasks, such as proactive space allocation and space reclamation. It dynamically spawns slave processes (Wnnn) to implement the task.
VKTM (virtual keeper of time) is responsible for providing a wall-clock time (updated every second) and reference-time counter (updated every 20 ms and available only when running at elevated priority
VKRM (virtual scheduler for resource manager process) manages the CPU scheduling for all managed Oracle processes. The process schedules managed processes in accordance with an active resource plan.

Initialization Parameters (Oracle Database 10g to Oracle Database 11g Change)

1. OPTIMIZER_USE_INVISIBLE_INDEXES
OPTIMIZER_USE_INVISIBLE_INDEXES was introduced in Oracle Database 11g
Release 1. This parameter enables or disables the use of invisible indexes. The default
value for OPTIMIZER_USE_INVISIBLE_INDEXES is FALSE.

2. OPTIMIZER_USE_PENDING_STATISTICS
OPTIMIZER_USE_PENDING_STATISTICS was introduced in Oracle Database 11g
Release 1. This parameter specifies whether or not the optimizer uses pending
statistics when compiling SQL statements. The default value for
OPTIMIZER_USE_PENDING_STATISTICS is FALSE.

3. OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES was introduced in Oracle Database
11g Release 1. This parameter enables or disables the automatic recognition of
repeatable SQL statements, as well as the generation of SQL plan baselines for such
statements. The default value for OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is
FALSE.

4. OPTIMIZER_USE_SQL_PLAN_BASELINES
OPTIMIZER_USE_SQL_PLAN_BASELINES was introduced in Oracle Database 11g
Release 1. This parameter enables or disables the use of SQL plan baselines stored in
SQL Management Base. When enabled, the optimizer looks for a SQL plan baseline for
the SQL statement being compiled. If one is found in SQL Management Base, then the
optimizer will cost each of the baseline plans and pick one with the lowest cost. The
default value for OPTIMIZER_USE_SQL_PLAN_BASELINES is TRUE.

Automatic Optimizer Statistics Collection

In Oracle Database 11g Automatic optimizer statistics collection runs as part of the automated
maintenance tasks infrastructure (AutoTask) and is enabled by default to run in all predefined
maintenance windows. The AutoTask statistics collection replaces the Oracle Database 10g
GATHER_STATS_JOB. Automatic optimizer statistics collection is enabled by default. If for some

reason automatic optimizer statistics collection needs to be disabled, you can disable it using the

DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package.

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
To re-enable automatic optimizer statistics collection, you can enable it using the ENABLE
procedure in the DBMS_AUTO_TASK_ADMIN package
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;

for complete information, please refer to the [ID 785351.1] note.

mardi 7 août 2012

How to Execute Process Flow from SQL*Plus

1. Introduction :
Process flow allows us to execute mappings, tranformations ...
Using Sql*Plus, you can execute the process flows, and you can find belw the scripts to use for 9i, 10g and 11g.

2. Scripts
2.1 9i
First create the Run_Owb_Job function as :

CREATE OR REPLACE FUNCTION RUN_OWB_OWNER_9204."RUN_OWB_JOB" ( p_repos_owner in varchar2 default null
, p_location_name in varchar2 default null
, p_task_type in varchar2 default null
, p_task_name in varchar2 default null
, p_system_params in varchar2 default '","'
, p_custom_params in varchar2 default '","'
, p_oem_friendly in number default 0
) return number
is
l_oem_style boolean := case (p_oem_friendly)
when 0 then false
else true
end ;
l_audit_execution_id number; -- Audit Execution Id
l_audit_result number := wb_rt_api_exec.RESULT_FAILURE; -- Result Code
l_audit_result_disp varchar2(64) := 'FAILURE'; -- Result Display Code
l_task_type_name varchar2(64); -- Task Type Name
l_task_type varchar2(64); -- Task Type
l_task_name varchar2(64); -- Task Name
l_location_name varchar2(64); -- Location Name
l_return number ;
procedure override_input_parameter
(
p_audit_execution_id in number,
p_parameter_name in varchar2,
p_value in varchar2,
p_parameter_kind in number
)
is
l_parameter_kind varchar2(64);
begin
if p_parameter_kind = wb_rt_api_exec.PARAMETER_KIND_SYSTEM
then
l_parameter_kind := 'SYSTEM';
else
l_parameter_kind := 'CUSTOM';
end if;
dbms_output.put_line('| ' || p_parameter_name || '%' || l_parameter_kind || '=' || '''' || p_value || '''');
wb_rt_api_exec.override_input_parameter
(
p_audit_execution_id,
p_parameter_name,
p_value,
p_parameter_kind
);
end;
procedure override_input_parameters
(
p_audit_execution_id in number,
p_parameters varchar2,
p_parameter_kind in number
)
is
l_anchor_offset number := 1;
l_start_offset number := 1;
l_equals_offset number;
l_comma_offset number;
l_value_offset number;
l_esc_offset number;
l_esc_count number;
l_esc_char varchar2(4);
l_parameter_name varchar2(4000);
l_parameter_value varchar2(4000);
function strip_escape
(
p_escapedString varchar2
)
return varchar2
is
l_strippedString varchar2(4000);
l_a_char varchar2(4);
l_b_char varchar2(4);
l_strip_offset number := 1;
begin
loop
exit when p_escapedString is null or l_strip_offset > length(p_escapedString);
l_a_char := SUBSTR(p_escapedString, l_strip_offset, 1);
if l_strip_offset = length(p_escapedString)
then
l_strippedString := l_strippedString || l_a_char;
exit;
else
if l_a_char = '\'
then
l_b_char := SUBSTR(p_escapedString, l_strip_offset + 1, 1);
if l_b_char = '\' or l_b_char = ','
then
l_strippedString := l_strippedString || l_b_char;
l_strip_offset := l_strip_offset + 1;
end if;
else
l_strippedString := l_strippedString || l_a_char;
end if;
end if;
l_strip_offset := l_strip_offset + 1;
end loop;
return l_strippedString;
end;
begin
loop
l_equals_offset := INSTR(p_parameters, '=', l_start_offset);
exit when l_equals_offset = 0;
l_start_offset := l_equals_offset + 1;
loop
l_comma_offset := INSTR(p_parameters, ',', l_start_offset);
if l_comma_offset = 0
then
l_comma_offset := length(p_parameters) + 1;
exit;
else
l_esc_count := 0;
l_esc_offset := l_comma_offset - 1;
loop
l_esc_char := SUBSTR(p_parameters, l_esc_offset, 1);
exit when l_esc_char != '\';
l_esc_count := l_esc_count + 1;
l_esc_offset := l_esc_offset - 1;
end loop;

if MOD(l_esc_count, 2) != 0
then
l_start_offset := l_comma_offset + 1;
else
exit;
end if;
end if;
end loop;
l_parameter_name := LTRIM(RTRIM(SUBSTR(p_parameters, l_anchor_offset, l_equals_offset - l_anchor_offset)));
l_parameter_value := strip_escape(SUBSTR(p_parameters, l_equals_offset + 1, l_comma_offset - (l_equals_offset + 1)));
-- Override Input Parameter
override_input_parameter(p_audit_execution_id, l_parameter_name, l_parameter_value, p_parameter_kind);
exit when l_comma_offset >= length(p_parameters)-1;
l_start_offset := l_comma_offset + 1;
l_anchor_offset := l_start_offset;
end loop;
end;
procedure override_custom_input_params
(
p_audit_execution_id in number,
p_parameters varchar2
)
is
l_parameter_kind number := wb_rt_api_exec.PARAMETER_KIND_CUSTOM;
begin
override_input_parameters(p_audit_execution_id, p_parameters, l_parameter_kind);
null;
end;
procedure override_system_input_params
(
p_audit_execution_id in number,
p_parameters varchar2
)
is
l_parameter_kind number := wb_rt_api_exec.PARAMETER_KIND_SYSTEM;
begin
override_input_parameters(p_audit_execution_id, p_parameters, l_parameter_kind);
null;
end;
begin
execute immediate ('alter session set current_schema = ' || p_repos_owner) ;
--
-- Initialize Return Code
--
l_return := wb_rt_api_exec.RESULT_FAILURE;
--
-- Import Parameters
--
dbms_output.put_line('Stage 1: Decoding Parameters');
l_task_type_name := p_task_type ;
if UPPER(l_task_type_name) = 'PLSQL'
then
l_task_type := 'PLSQL';
elsif UPPER(l_task_type_name) = 'SQL_LOADER'
then
l_task_type := 'SQLLoader';
elsif UPPER(l_task_type_name) = 'PROCESS'
then
l_task_type := 'ProcessFlow';
else
l_task_type := l_task_type_name;
end if;
l_task_name := p_task_name ;
l_location_name := p_location_name ;
dbms_output.put_line('| location_name=' || l_location_name);
dbms_output.put_line('| task_type=' || l_task_type);
dbms_output.put_line('| task_name=' || l_task_name);
--
-- Decode Parameters
--
begin
--
-- Prepare Execution
--
dbms_output.put_line('Stage 2: Opening Task ');
dbms_output.put_line('Stage 2:' || l_task_type);
dbms_output.put_line('Stage 2:' || l_task_name);
dbms_output.put_line('Stage 2:' || l_location_name);
l_audit_execution_id := wb_rt_api_exec.open(l_task_type, l_task_name, l_location_name);
dbms_output.put_line('| l_audit_execution_id=' || to_char(l_audit_execution_id));
commit;
--
-- Override Parameters
--
dbms_output.put_line('Stage 3: Overriding Parameters');
override_system_input_params(l_audit_execution_id, p_system_params);
override_custom_input_params(l_audit_execution_id, p_custom_params);
--
-- Execute
--
dbms_output.put_line('Stage 4: Executing Task');
l_audit_result := wb_rt_api_exec.execute(l_audit_execution_id);
if l_audit_result = wb_rt_api_exec.RESULT_SUCCESS
then
l_audit_result_disp := 'SUCCESS';
elsif l_audit_result = wb_rt_api_exec.RESULT_WARNING
then
l_audit_result_disp := 'WARNING';
elsif l_audit_result = wb_rt_api_exec.RESULT_FAILURE
then
l_audit_result_disp := 'FAILURE';
else
l_audit_result_disp := 'UNKNOWN';
end if;
dbms_output.put_line('| l_audit_result=' || to_char(l_audit_result) || ' (' || l_audit_result_disp || ')');
-- Finish Execution
dbms_output.put_line('Stage 5: Closing Task');
wb_rt_api_exec.close(l_audit_execution_id);
commit;
dbms_output.put_line('Stage 6: Processing Result');
if l_oem_style
then
if l_audit_result = wb_rt_api_exec.RESULT_SUCCESS
then
l_return := 0;
elsif l_audit_result = wb_rt_api_exec.RESULT_WARNING
then
l_return := 0;
else
l_return := l_audit_result;
end if;
else
l_return := l_audit_result;
end if;
dbms_output.put_line('| exit=' || to_char(l_return));
exception
when no_data_found
then
raise_application_error(-20001, 'Task not found - Please check the Task Type, Name and Location are correct.');
end;
return l_return ;
end;
/

Second, create the FUN_LAUNCH_OWB function to run with sqlplus :

CREATE OR REPLACE FUNCTION "FUN_LAUNCH_OWB" RETURN NUMBER AS
res number;
BEGIN
res := run_owb_job('RUN_OWB_OWNER_9204','MAIN_DEPLOY','PROCESS','WF_MAIN');
RETURN 0;
END FUN_LAUNCH_OWB;
/

2.2 10g

First create the Run_Owb_Job function as :

CREATE OR REPLACE function REP_OWB_OWNER.run_owb_job
( p_repos_owner in varchar2 default null
, p_location_name in varchar2 default null
, p_task_type in varchar2 default null
, p_task_name in varchar2 default null
, p_system_params in varchar2 default '","'
, p_custom_params in varchar2 default '","'
, p_oem_friendly in number default 0
) return number
is
l_oem_style boolean := case (p_oem_friendly)
when 0 then false
else true
end ;
l_audit_execution_id number; -- Audit Execution Id
l_audit_result number := wb_rt_api_exec.RESULT_FAILURE; -- Result Code
l_audit_result_disp varchar2(64) := 'FAILURE'; -- Result Display Code
l_task_type_name varchar2(64); -- Task Type Name
l_task_type varchar2(64); -- Task Type
l_task_name varchar2(64); -- Task Name
l_location_name varchar2(64); -- Location Name
l_return number ;
procedure override_input_parameter
(
p_audit_execution_id in number,
p_parameter_name in varchar2,
p_value in varchar2,
p_parameter_kind in number
)
is
l_parameter_kind varchar2(64);
begin
if p_parameter_kind = wb_rt_api_exec.PARAMETER_KIND_SYSTEM
then
l_parameter_kind := 'SYSTEM';
else
l_parameter_kind := 'CUSTOM';
end if;
dbms_output.put_line('| ' || p_parameter_name || '%' || l_parameter_kind || '=' || '''' || p_value || '''');
wb_rt_api_exec.override_input_parameter
(
p_audit_execution_id,
p_parameter_name,
p_value,
p_parameter_kind
);
end;
procedure override_input_parameters
(
p_audit_execution_id in number,
p_parameters varchar2,
p_parameter_kind in number
)
is
l_anchor_offset number := 1;
l_start_offset number := 1;
l_equals_offset number;
l_comma_offset number;
l_value_offset number;
l_esc_offset number;
l_esc_count number;
l_esc_char varchar2(4);
l_parameter_name varchar2(4000);
l_parameter_value varchar2(4000);
function strip_escape
(
p_escapedString varchar2
)
return varchar2
is
l_strippedString varchar2(4000);
l_a_char varchar2(4);
l_b_char varchar2(4);
l_strip_offset number := 1;
begin
loop
exit when p_escapedString is null or l_strip_offset > length(p_escapedString);
l_a_char := SUBSTR(p_escapedString, l_strip_offset, 1);
if l_strip_offset = length(p_escapedString)
then
l_strippedString := l_strippedString || l_a_char;
exit;
else
if l_a_char = '\'
then
l_b_char := SUBSTR(p_escapedString, l_strip_offset + 1, 1);
if l_b_char = '\' or l_b_char = ','
then
l_strippedString := l_strippedString || l_b_char;
l_strip_offset := l_strip_offset + 1;
end if;
else
l_strippedString := l_strippedString || l_a_char;
end if;
end if;
l_strip_offset := l_strip_offset + 1;
end loop;

return l_strippedString;
end;
begin
loop
l_equals_offset := INSTR(p_parameters, '=', l_start_offset);
exit when l_equals_offset = 0;
l_start_offset := l_equals_offset + 1;
loop
l_comma_offset := INSTR(p_parameters, ',', l_start_offset);
if l_comma_offset = 0
then
l_comma_offset := length(p_parameters) + 1;
exit;
else
l_esc_count := 0;
l_esc_offset := l_comma_offset - 1;
loop
l_esc_char := SUBSTR(p_parameters, l_esc_offset, 1);
exit when l_esc_char != '\';
l_esc_count := l_esc_count + 1;
l_esc_offset := l_esc_offset - 1;
end loop;
if MOD(l_esc_count, 2) != 0
then
l_start_offset := l_comma_offset + 1;
else
exit;
end if;
end if;
end loop;
l_parameter_name := LTRIM(RTRIM(SUBSTR(p_parameters, l_anchor_offset, l_equals_offset - l_anchor_offset)));
l_parameter_value := strip_escape(SUBSTR(p_parameters, l_equals_offset + 1, l_comma_offset - (l_equals_offset + 1)));
-- Override Input Parameter
override_input_parameter(p_audit_execution_id, l_parameter_name, l_parameter_value, p_parameter_kind);
exit when l_comma_offset >= length(p_parameters)-1;
l_start_offset := l_comma_offset + 1;
l_anchor_offset := l_start_offset;
end loop;
end;
procedure override_custom_input_params
(
p_audit_execution_id in number,
p_parameters varchar2
)
is
l_parameter_kind number := wb_rt_api_exec.PARAMETER_KIND_CUSTOM;
begin
override_input_parameters(p_audit_execution_id, p_parameters, l_parameter_kind);
null;
end;
procedure override_system_input_params
(
p_audit_execution_id in number,
p_parameters varchar2
)
is
l_parameter_kind number := wb_rt_api_exec.PARAMETER_KIND_SYSTEM;
begin
override_input_parameters(p_audit_execution_id, p_parameters, l_parameter_kind);
null;
end;
begin
execute immediate ('alter session set current_schema = ' || p_repos_owner) ;
--
-- Initialize Return Code
--
l_return := wb_rt_api_exec.RESULT_FAILURE;
--
-- Import Parameters
--
dbms_output.put_line('Stage 1: Decoding Parameters');
l_task_type_name := p_task_type ;
if UPPER(l_task_type_name) = 'PLSQL'
then
l_task_type := 'PLSQL';
elsif UPPER(l_task_type_name) = 'SQL_LOADER'
then
l_task_type := 'SQLLoader';
elsif UPPER(l_task_type_name) = 'PROCESS'
then
l_task_type := 'ProcessFlow';
else
l_task_type := l_task_type_name;
end if;
l_task_name := p_task_name ;
l_location_name := p_location_name ;
dbms_output.put_line('| location_name=' || l_location_name);
dbms_output.put_line('| task_type=' || l_task_type);
dbms_output.put_line('| task_name=' || l_task_name);
--
-- Decode Parameters
--
begin
--
-- Prepare Execution
--
dbms_output.put_line('Stage 2: Opening Task ');
dbms_output.put_line('Stage 2:' || l_task_type);
dbms_output.put_line('Stage 2:' || l_task_name);
dbms_output.put_line('Stage 2:' || l_location_name);
l_audit_execution_id := wb_rt_api_exec.open(l_task_type, l_task_name, l_location_name);
dbms_output.put_line('| l_audit_execution_id=' || to_char(l_audit_execution_id));
commit;
--
-- Override Parameters
--
dbms_output.put_line('Stage 3: Overriding Parameters');
override_system_input_params(l_audit_execution_id, p_system_params);
override_custom_input_params(l_audit_execution_id, p_custom_params);
--
-- Execute
--
dbms_output.put_line('Stage 4: Executing Task');
l_audit_result := wb_rt_api_exec.execute(l_audit_execution_id);
if l_audit_result = wb_rt_api_exec.RESULT_SUCCESS
then
l_audit_result_disp := 'SUCCESS';
elsif l_audit_result = wb_rt_api_exec.RESULT_WARNING
then
l_audit_result_disp := 'WARNING';
elsif l_audit_result = wb_rt_api_exec.RESULT_FAILURE
then
l_audit_result_disp := 'FAILURE';
else
l_audit_result_disp := 'UNKNOWN';
end if;
dbms_output.put_line('| l_audit_result=' || to_char(l_audit_result) || ' (' || l_audit_result_disp || ')');
-- Finish Execution
dbms_output.put_line('Stage 5: Closing Task');
wb_rt_api_exec.close(l_audit_execution_id);
commit;
dbms_output.put_line('Stage 6: Processing Result');
if l_oem_style
then
if l_audit_result = wb_rt_api_exec.RESULT_SUCCESS
then
l_return := 0;
elsif l_audit_result = wb_rt_api_exec.RESULT_WARNING
then
l_return := 0;
else
l_return := l_audit_result;
end if;
else
l_return := l_audit_result;
end if;
dbms_output.put_line('| exit=' || to_char(l_return));
exception
when no_data_found
then
raise_application_error(-20001, 'Task not found - Please check the Task Type, Name and Location are correct.');
end;
return l_return ;
end;
/

second create the FUN_LAUNCH_OWB function to run with sqlplus :

CREATE OR REPLACE FUNCTION REP_OWB_OWNER."FUN_LAUNCH_OWB" RETURN NUMBER AS
res number;
BEGIN
res := run_owb_job('REP_OWB_OWNER','OWF_LOCATION','PROCESS','FI_MAIN');
RETURN 0;
END FUN_LAUNCH_OWB;
/

2.3 11g
create the FUN_LAUNCH_OWB function to run with sqlplus :

CREATE OR REPLACE FUNCTION OWBSYS."FUN_LAUNCH_OWB" RETURN
NUMBER AS
res number;
BEGIN

OWBSYS.wb_rt_script_util.set_workspace('REP_OWB_OWNER.WORKSPACE_OWNER');
res := OWBSYS.wb_rt_script_util.run_task(
'LOCATION_WORKFLOW_11GR2',
'PROCESSFLOW',
'WF_MAIN',
NULL,
NULL,
0,
0);
RETURN 0;

END FUN_LAUNCH_OWB;
/