APEX

Christian,

I have not tried this, but I'm pretty sure you surround substitution strings with ##

So you can try this out..

select * from test@#DB_WORK#

-Chris

Hi Christian,

Try this:

select * from "test@" || &DB_WORK.

Let me know if it worked, never tried this before ;)

Greetings,
Rutger

thx for your fast replies, but both "solutions" don't work...

Christian,


I figured it out, you want to eveluate the statement at runtime. guess you're doing this from an interactive report. Unfortunately I can't see any options for "Use Generic Column Names (parse query at runtime only)" as it is in a classic SQL report.

If you use this query:

select * from test@&DB_WORK.

in a classic report and set the radio under the source to "Use Generic Column Names (parse query at runtime only)", it will work.

Greetings,
Rutger

Hello Christian,

Why not create different synonyms for the different users (assuming they are different...) pointing to the different tables@db_link?

Regards,
Roel

http://roelhartman.blogspot.com/
http://www.bloggingaboutoracle.org/
http://www.logica.com/

Ahoj Christian,
i don't really understand why you need to have such "architecture".

You can easily create two distinct apex workspaces, associated to two distinct schemas. Then you can use the "#OWNER#" specification in the SQL inside Apex to refer to the current schema user associated to the workspace.
So when you need to run in TEST mode, you run the application in workspace TEST (associated to the TEST schema), otherwise you run a copy of the application in workspace PRODUCTION.

The source of your SQL will look like:

select * from "#OWNER#"."TABLENAME"

May be you didn't notice it, but this is the default syntax used by Apex when it spawns SQL statements using some of its wizards.

If the supporting objects for APEX itself are kept in a third schema, then you can either create public synonyms for them or invoke them using the OWNER.OBJECT_NAME notation, provided you have the required execute/select/... privileges.

Does it sound more reasonable?

Bye,
Flavio

-----------------------------------------
http://www.oraclequirks.com

@Rutger: thx very much. this solution works. but now there is another problem (i use just classic reports):
if i use the option "use generic column names" the layout of all my already finished reports will be destroyed... ;-(

Edited by: Christian Ropposch on Oct 9, 2008 3:36 PM

@flavioc: i have 3 or 4 different databases from which i retrieve the data and so it's not possible to do it without db-links - in my opinion it's not possible to use this solution. i hope i understood you right - i'm not sure.

@Roel: how do you mean that? the users connect to the apex-app via ntlm - doesn't matter if test or production. to the apex-database i connect via the apex_public_user. so there are no different users to use different synonyms... i hope i understood you right.

Christian,
well, after you last comment my understanding is that you want to "route" the queries dynamically depending on the value of an application item or something like that.
My initial understanding was that you perhaps wanted a more static type of routing, that is why i suggested to use different workspaces for binding the desired schema, where you could place synonyms hiding the db links.

If you want to achieve truly dynamic routing, then may be you should use the "parse SQL at run-time only" option, may be that can do the trick, but i do not guarantee that you won't get stuck somewhere else along the path.

Bye,
Flavio

----------------------------------------
http://www.oraclequirks.com

i try it now with two apex workspaces. one for test and one for production. then i can use the same db-link name for test and production, but they link to different databases...

thx for your help!

bye,
christian