Database Software

This message is a generalized follow on from an earlier thread, 1476441 and begins with a quote from that thread.

Hi Folks,
I'm having identical sorts of problems trying to run Workflows and Mappings from Enterprise Manager (OEM) and Scheduler. This is using Oracle RDBMS 10gR2 and OWB 10gR2 (Paris). (BTW, I tried the fixes described above, but unfortunately they did not help).

I got to the state described in the quoted text, with Workflows.. Then I tried to simplify the problem. I created a simple harness to just execute a single Mapping alone - no workflow. It is called STORED_PROC_NAME and is created under the user OWBUSER (the target schema). This works fine from SQL*Plus and from SQL Developer. When executed from either OWBUSER or from under OWBOWNER (the Repository owner), the code looks like:

BEGIN
STORED_PROC_NAME();
END

and it runs just fine, displaying 'Stage 1 . . .' and so on with all the usual displays. I check the results via TOAD, and everything is good, including in the Run Time views. In WB_RT_AUDIT_EXECUTIONS, the CREATED BY is OWBUSER, and the UPDATED BY is OWBOWNER.

However, put that code into Scheduler as an anonymous PL/SQL block (Job owned by OWBUSER) and it fails. Interestingly, the CREATED ON field in ALL_RT_AUDIT_EXECUTIONS shows the user name of the Job Creator (OWBSYS in my case). RETURN RESULT is empty (not OK or FAILURE).

Change the anonymous PL/SQL to a Stored Procedure, and it fails in just the same way.

The stack I'm running is like this:

SQL*Plus or SQL Developer or OEM/Scheduler
STORED_PROC_NAME
RUN_OWB_CODE (from the website)
wb_rt_api_exec (various entry points; for example .open and .execute)
MAPPING_NAME

STORED_PROC_NAME contains declarations for a result and the audit ID, followed by a call to the run_owb_code procedure compiled in OWBOWNER, with a Public Synonym and an EXECUTE GRANT to OWBUSER..

The error I get in ALL_RT_AUDIT_EXEC_MESSAGES is ORA-01017: invalid username/password; logon denied, the CREATED BY field contains OWBOWNER. It looks to me as though the current schema is now OWBOWNER, wb_rt_api_exec is being called with the .open entry point, and afer writing the initial information into the Run Time Audit tables, dying either right there or at the .execute point when trying to call MAPPING_NAME in OWUSER.

I suspect that this problem is to do with invoker's / definer's rights setup, prior to invoking wb_rt_api_exec in run_owb_code. I further suspect that the OEM/Scheduler call sequence disables the Roles normally available to OWBUSER. But I cannot put an ALTER SET ROLE in owb_run_code (ORA-06565), as it is a Stored Procedure. I noted that this technniqe is being used in both oem_exec_template, sqlplus_exe_template, and wb_rt_api_exec_template.

I can understand it being used in sqlplus_exec_template, as this is all dynamic stuff. But why would it be used in an OEM template? As in a static, scheduled job? There is nowhere for such a template to fit into 10g OEM, as far as I can see.

As I say, the OEM templates are for 9i. There is nothing for 10gR2 at all, as far as I can tell The only reference to 10gR2 is a note in oem_exe_template saying 'don't use this, use wb_rt_api_exec.run_task', and a parameter list for the latter in the Scripting Guide. No examples, proper usage, or best practices at all, anywhere, as far as I can see.

So, does anyone have any thoughts, documentation, pointers, links, or best of all, working examples of driving OWB Process Flows and Mappings from OEM/Scheduler.

Because I am at a loss. How do you run OWB compnents from OEM/Scheduler? All help gratefully received.

Cheers,
Donna

p.s. for those not familiar with these templates, run_owb_code is a procedure, very similar to run_my_owb_stuff, which is a function. wb_rt_api_exec_example is an updated version of run_my_owb_stuff. It is apparently intended for use with SQL*Plus.