Forum Stats

  • 3,784,156 Users
  • 2,254,897 Discussions
  • 7,880,713 Comments

Discussions

Categories

substitution strings in select-statemtens

Christian Ropposch
Christian Ropposch Member Posts: 209
edited Oct 9, 2008 11:02AM in APEX Discussions
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
Tagged:
· Share on TwitterShare on Facebook
«1

Answers

  • 592960
    592960 Member Posts: 423
    edited Oct 9, 2008 9:23AM
    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
    · Share on TwitterShare on Facebook
  • 628347
    628347 Member Posts: 203
    Hi Christian,

    Try this:
    select * from "[email protected]" || &DB_WORK.
    Let me know if it worked, never tried this before ;)

    Greetings,
    Rutger
    · Share on TwitterShare on Facebook
  • Christian Ropposch
    Christian Ropposch Member Posts: 209
    thx for your fast replies, but both "solutions" don't work...
    · Share on TwitterShare on Facebook
  • 628347
    628347 Member Posts: 203
    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
    · Share on TwitterShare on Facebook
  • Roel Hartman
    Roel Hartman Member Posts: 2,711 Gold Trophy
    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/
    · Share on TwitterShare on Facebook
  • flavioc
    flavioc Member Posts: 1,125 Silver Badge
    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
    flavioc
    · Share on TwitterShare on Facebook
  • Christian Ropposch
    Christian Ropposch Member Posts: 209
    edited Oct 9, 2008 9:36AM
    @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
    · Share on TwitterShare on Facebook
  • 628347
    628347 Member Posts: 203
    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
    · Share on TwitterShare on Facebook
  • Christian Ropposch
    Christian Ropposch Member Posts: 209
    @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.
    · Share on TwitterShare on Facebook
  • Christian Ropposch
    Christian Ropposch Member Posts: 209
    @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.
    · Share on TwitterShare on Facebook
This discussion has been closed.