Create Form on External Procedure

Tomek

I have mixed environment Oracle EBS and Apex 5.0.2. Apex runs in its own schema (XXAPEX) and various apex application access the data and packages from Oracle EBS APPS schema. To access the data I GRANT SELECT on the table to XXAPEX schema. To execute the procedure in the package I create GRANT EXECUTE on the package to XXAPEX schema.

It all works fine when I create the code manually e.g. RETURN apps.fnd_global.user_id.However if I use the wizard to Create Form on Procedure I receive the error "User MILOWSKI has no privileges on the APPS schema.". Here are the steps in the wizard:

1. procedure owner: APPS

2. Stored procedure name PAGE_MGR.SAVE_RECORD (the procedure is visible in select list)

3. click Next

4. After long time of spinning getting the "User MILOWSKI has no privileges on the APPS schema."

I tried to create a synonym in XXAPEX schema for a package but it does not help.

I suspect if I create a dummy package in XXAPEX schema from which I call the apps.page_mgr.save_record(...) it may work.This option though it's not really something I would like to start implementing for all my pages in order to use the wizard.

Thoughts, suggestions?

Thanks, Tomas

Mike Kutz

Your procedure is in APP. Correct?

What is your Parsing Schema Name?  It sounds like XXAPEX.

What is your Workspace Schema name? I'm going to guess MILOWSKI

You need to grant permissions to the Parsing Schema in order for the APEX Application to work.

I've found that, under some circumstances, you have to grant permissions to the Workspace Schema too.

I forgot if I still needed the perms after I created the page.

MK

Tomek

Not sure What do you think I missed:

Workspace Name: AIS

Schema connected to the workspace: XXAPEX

Apex Login: MILOWSKI

There is only one schema XXAPEX.

MILOWSKI is my username in Apex with default schema set to XXAPEX.

Mike Kutz

Ok.  I think I understand what is happening.

Creating the synonym should have worked.

However, you need to Create Form on Procedure using the synonym XXAPEX.PAGE_MGR not the underlying package APP.PAGE_MGR.  (current guess)

I haven't had time to try to reproduce the problem.

MK

Tomek

Thanks Mike but this is not working either...

I made sure the synonym is in place. In APPS schema executed the following:

GRANT ALL ON page_mgr to xxapex WITH GRANT OPTION;

DROP SYNONYM xxapex.page_mgr;

CREATE SYNONYM xxapex.page_mgr FOR page_mgr;

When I login to Apex and use "Object Browser" I see the synonym is right in place.

However, when creating the "form on procedure" the package page_mgr is not there listed in the list nor I can just type it in. I specify the XXAPEX as procedure owner.

Tomek

Does anyone experienced this problem?