Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 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
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 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.