Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 545 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 440 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
substitution strings in select-statemtens

Christian Ropposch
Member Posts: 209
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 [email protected]_WORK should result in select * from [email protected]_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
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 [email protected]_WORK should result in select * from [email protected]_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
Answers
-
Christian,
I have not tried this, but I'm pretty sure you surround substitution strings with ##
So you can try this out..select * from [email protected]#DB_WORK#
-Chris -
Hi Christian,
Try this:select * from "[email protected]" || &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 [email protected]&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 [email protected]_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 -
Yes, unfortunately that's a drawback. But you can set the column headings type to "Custom" and you can at least set the headings.
Greetings,
Rutger -
@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.
This discussion has been closed.