DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2016

    Migrate MSSQL Queries to ORACLE

    I am in a process to migrate all MS-SQL queries to oracle. As I have bunch of complex and large queries to migrate – I have used ‘Scratch-Editor” of SQL Developer.
    The Scratch Editor converts every ‘UPDATE’ statement of MSSQL queries to ‘MERGE’ statement of oracle.

    MS SQL:
    UPDATE table1
    SET table1.column = table2.expression1
    FROM table1
    INNER JOIN table2
    ON (table1.column1 = table2.column1)
    [WHERE conditions];

    Converted above query to Oracle :
    MERGE INTO table1
    USING (SELECT table1.ROWID row_id, table2.expression1
    FROM table1
    JOIN table2
    ON ( table1.column1 = table2.column1 ) ) src
    ON ( table1.ROWID = src.row_id )

    Any idea why the oracle engine has been designed to converts update to merge?

    I want convert ‘UPDATE’ statement of MSSQL queries to ‘UPDATE’ statement of oracle. Any idea how to achieve that?

    Please provide your suggestion?scratch_editor.jpg

  2. #2
    Join Date
    Sep 2016
    See, when you talk about "the Oracle engine" you are not correct : the "SQL DEVELOPER" is just a GUI, a client program if you wish - it don´t have ANY RELATION with the "Oracle (RDBMS) engine", not at all, besides the fact that both are made by the same company... And the scratch editor (see http://www.thatjeffsmith.com/archive...lator-for-sql/) for more details and tips) is just a TRANSLATOR, ie, a piece of software that tries (TRY is the word, see to convert the SQLSERVER sql dialect to the one used by Oracle RDBMS - it os NOT perfect, it is NOT guaranteed to get a 100% success rate, it is just a help....
    This being clear, your answer is : the piece of software in question is "thinking" that MERGE could be your best option , only... If you think the opposite, rewrite the SQL to do the JOIN update, much alike https://asktom.oracle.com/pls/apex/f...D:164612348068 is showing....
    And to the best of my knowledge, that scratch thing is very simple, very basic and not so much flexible, so I dont think that you can 'configure'/adjust it to use another SQL command in place of any other, so if the generated MERGE don´t suffice/is not good to you, you WILL need to do the rewrite manually...


    J. laurindo Chiappa

    P.S. :

    OF COURSE : very basic one-table SQLs apart, no two RDBMSs work the same/process a SQL the same, so you CAN face issues like the need for key-preserved values for example, the presented link talks about it.... Due to this you CAN NOT expect that any piece of software convert perfectly all your SQL from RDBMS x to the concepts/mechanisms used by RDBMS y....
    So, if you don´t know well the Oracle Concepts and mechanisms, go study and learn them BEFORE any conversion attempts....

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.