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,
RETURN NUMBER AUTHID CURRENT_USER as
cc_owner varchar2(256) := 'OWB_CC';
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;
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.