Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

substitution strings in select-statemtens

Christian RopposchOct 9 2008 — edited Oct 9 2008
ahoj!

is it possible to use a substitution string in a select-statement as db-link? if yes, how? for example:
application definition -> substitution string: DB_WORK, substitution value: pwork_pwork
how can i use the substitution string DB_WORK in a select-statement?

select * from test@DB_WORK should result in select * from test@pwork_pwork, but this doesn't work. v('DB_WORK'), &DB_WORK. and :DB_WORK is also not possible. (error message: Query cannot be parsed within the Builder...)

background: i have a test- and production-application. in the test-app i use pwork_pwork as db-link, the production-app should use work_work. the apex-database is for both environments the same.

thx!

bye,
christian

Comments

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?

1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 6 2008
Added on Oct 9 2008
#apex-discussions, #schema, #workspace
12 comments
509 views