Oracle Warehouse Builder (OWB) Weblog

Getting the exception RPE-01012 when trying to deploy a map? Or RPE-02245 with a process flow? Or failed to
execute a map with ORA-01017: invalid username/password. How do you execute via a PLSQL function/procedure from the target user? Here's a few things related to failures on
deployment, execution etc that its useful to be aware of.

Failed to deploy Map with RPE-01012

This is generally just a privilege issue because you are deploying to a
regular database user which has no OWB specific privileges. This is the
'RPE-01012: Cannot deploy PL/SQL maps to the target schema because it
is not owned by the Control Center' error.

Check if the schema
where deploying the map to is in the same database instance as
the runtime platform.  If
so, has the schema (where maps are being deployed into) been added as
an OWB user (Global Explorer panel/Security/Users)? This will actually
grant some roles to the schema to enable maps to be deployed. This error (RPE-01012) comes from a simple check to see if
the schema you are deploying the map to has been enabled as an OWB
target user. If not in the same instance, then you will need the OWB repository installed in the remote instance and the user added as a target user in it.



For example to recreate the problem if I have a map X in module M which
uses location L, L is just a plain old database user P. If I try
deploying the map I will get the RPE-01012. You can actually deploy a
table to this schema which kinds of tricks you into believing
everything is OK (it is not, for maps anyway, they have greater
dependency on audit/credentials etc).



One way to setup the database user is using OWB, you can create an OWB
user based on an existing database user (or create a new one), this
functionality is available to the repository owner under the Security
node in the global explorer.


Ensure the user has the target schema option (I used existing database
user XWEEK).


This will grant privileges to the database user which will allow the
deployment etc. to operate.

Deploying the map should be successful now.

Failed to execute Map with ORA-01017: invalid
username/password
With this case executing a map (say M) fails using the sqlplus exec
template SQL script. The error in the audit states ORA-01017: invalid
username/password; logon denied. You are puzzled because you can
connect to SQLPlus as WH, but get some odd error when you execute the
command (and view the execution audit in the control center for
example);

sqlplus WH/WH @sqlplus_exec_template.sql  DESIGN WH_L PLSQL M "," ","

This
commonly happens when location WH_L representing database user WH has
been registered by the repository owner (DESIGN) or another user and
the preference 'Share location password during runtime' is disabled
(this is the default).


It more than
likely means the WH user has no registration details for WH_L, since
the details are not shared. You can logon to OWB as WH and register the
location WH_L, entering the password. Now go back to sqlplus and you
should execute fine.

How to execute Map/Process from Target user in PLSQL procedure
The
routine has to be invoker rights in order to set the roles for the
session (you cannot see the wb_rt_api_exec.run_task method without
enabling this role). Here is the function I created in the target to
allow the execute of any object.

    -- With this you will have to change the name of your CC owner, I have used OWB_CC
    create or replace function execute_code ( obj_loc  varchar2,
                                              obj_typ  varchar2,
                                              obj_name varchar2,
                                              sys_parm varchar2,
                                              cus_parm varchar2)
                              RETURN NUMBER  AUTHID CURRENT_USER as
      r number;
      cc_owner varchar2(256) := 'OWB_CC';
    begin
      execute immediate 'set role OWB_D_'||cc_owner||', OWB_O_' || cc_owner;
     
execute immediate 'begin :r :=
'||cc_owner||'.wb_rt_api_exec.run_task(:obj_loc, :obj_typ, :obj_name,
:sys_parm, :cus_parm,0,0); end;' using OUT r, IN obj_loc, IN obj_typ,
IN obj_name, IN sys_parm, IN cus_parm;
      return r;
    end;

Execute a process flow from a target schema:
    declare v number; begin v := execute_code('OWF_LOC', 'PROCESS', 'DEMOFLOW/FLOW_FTP', ',', ','); end;
    /

Failing to Deploy Process Flows, getting RPE-02245

This happens when OWB and WF are in different instances. Access from WF to OWB is performed through a proxy user. During deployment the proxy user is granted the necessary object privileges required to execute the deployed process flows. The proxy user must already exist and does not require any privileges apart from CONNECT; it can therefore be an ordinary database user or a OWB user. Create the user through OWB with the same password as the existing remote Workflow OWF_MGR user, now go deploy the process flow.

Anyway hope these are useful.