substitution variable in WHERE Caluse

milindksmilindks Posts: 4
edited December 23, 2014 11:34AM in Data Compare for Oracle

I am using Data compare for ORACLE to compare the data between multiple tables in two different schemas (structure and table, column names are exactly same) - I have created where condition on these tables - all these tables have same where condition - like where member_identifier = 123456789 -

Can it be possible to use substitution variable instead of = 123456789 as this condition is used for all the tables and if I want to compare data for another member then I have to edit the where clause to change the where condition from =123456789 to 987654321 for all the tables - can I change it globally or use substation variable where it will prompt me to enter it when I am executing the compare ?



  • Sorry we don't have anything like that built into the product but there are a couple of nasty workarounds I can immediately think of.

    1) Save the project and search/replace the value within the project file - it's just an xml file so shouldn't be too bad
    2) Create another table in the schema that contains the value you which to use and select that value into the where clause so it's modified at runtime

    That's what springs to mind but nothing built-in currently.
    Richard Mitchell
    Project Manager
    Red Gate Software Ltd
  • Thanks, that seems to be good workaround - will try it - but its still workaround :) - may be redgate should consider enhancement - Global change / substitution variable for next release for the WHERE Clause !
  • The next few things going into Data Compare for Oracle are...

    Mapping - the ability to compare tables and columns with different names in source and target
    Filter - backporting the object filtering that has been added to Source Control for Oracle
    Command line - the command line is a poor cousin when compared to the Schema Compare for Oracle command line (which I've been augmenting a lot lately to make it better in CI environments)
    Compare to scripts - comparing and syncing to scripts folder/files instead of live databases - this will lead onto support for lookup tables in Source Control for Oracle

    All of the above have got a lot of people waiting and don't have workarounds.

    Currently I'm concentrated on getting a version of Source Control for Oracle with git support out before Christmas - currently I reckon there's about a 60% chance of that happening.

    Busy busy :)
    Richard Mitchell
    Project Manager
    Red Gate Software Ltd
  • No Problem, Thanks for the suggestion, I used table for workaround and its working fine !
  • Sweet, glad you got it working ok.

    Happy holidays.
    Richard Mitchell
    Project Manager
    Red Gate Software Ltd
Sign In or Register to comment.