DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Migrate MSSQL Queries to ORACLE

  1. #1
    Join Date
    Sep 2016
    Posts
    1

    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.

    e.g:
    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 )
    WHEN MATCHED THEN UPDATE SET COLUMN_ = src.expression1;


    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
    Posts
    14
    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...

    Regards,

    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