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.
SET table1.column = table2.expression1
INNER JOIN table2
ON (table1.column1 = table2.column1)
Converted above query to Oracle :
MERGE INTO table1
USING (SELECT table1.ROWID row_id, table2.expression1
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
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
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
Click Here to Expand Forum to Full Width