Skip navigation links

MySQL Forums :: Oracle :: HOW TO CONVERT ORACLE TO MYSQL


Advanced Search

Re: How to convert this Oracle PL/SQL Function to MySQL
Posted by: Nausher Cholavaram ()
Date: November 30, 2007 01:53PM

I've been reading & lurking on the MySQL Forums for a week now, in order to learn about converting a couple of Oracle Stored Procedures I had to MySQL.

Here are my notes from this exercise, currently it is unedited, I will
soon have this edited and cleaned up.

Till then I present

Nausher's (Unedited) Guide to converting Oracle Stored Procedures to MySQL
==========================================================================

1) FUNCTIONS in MySQL cannot take OUT/INOUT parameters, change any such Oracle functions
to PROCEDURE.

1) Make use of the DELIMITER construct, or your MySQL Stored Procedures
just wont work.

2) Change "AND ROWNUM < x" to " LIMIT x"
Note: There is no "AND" before the LIMIT clause.

3) Change SQL%ROWCOUNT with the following constructs
For select queries -> FOUND_ROWS()
For update/insert/delete -> ROW_COUNT()

4) Change the CREATE PROCEDURE construct
'CREATE PROCEDURE xyz
AS declarations
BEGIN '
somefield CONSTANT NUMBER(1) := 3;

to
'CREATE PROCEDURE xyz()
BEGIN
DECLARE declarations'

4a)
Change the parameters, as MySQL has the parameter direction IN|OUT|INOUT first then name and type.
Find RegEx - (([a-zA-Z]+) (IN) ([a-zA-Z0-9\(]+) and. ([a-zA-Z]+) (OUT) ([a-zA-Z0-9\(]+)
Replacment Expr - \2 \1 \3

4b) Declarations follow the following order i) Variables ii) Cursors iii) Handlers
Replace all declare statements to use the DECLARE statement
Find RegEx - (db[a-zA-Z]+) ([a-zA-Z0-9\(\)]+)
Replace Expr -DECLARE \1 \2
For Ora expressions with the CONSTANT keyword, (MySQL doesn't support)
Find RegEx - ([a-zA-Z0-9_]+) constant ([a-zA-Z0-9\(\)]+) := ([a-zA-Z0-9\'\-]+)
Replace Expr - DECLARE \1 \2 DEFAULT \3

5) Change CONSTANT types to DEFAULT

Oracle
------
PROCEDURE abc
IS
somefield CONSTANT NUMBER(1) := 3;
BEGIN

MySQL
-----
DECLARE somefield INT(1) DEFAULT 3;

6) Replace 'EXIT WHEN condition' statements with the 'LEAVE label:' construct IF meets condition
Oracle
------
LOOP
EXIT WHEN SQL%ROWCOUNT = 0;
END LOOP;

MySQL
-----
msgloop: LOOP
IF exit condition met THEN
LEAVE msgloop;
END LOOP msgloop;

7) Convert Data types using the MySQL Migration Toolkit i.e. OracleDump.pl (http://forums.mysql.com/read.php?61,43890,43890)
or refer the table 2.4. at
http://download.oracle.com/docs/cd/E10405_01/doc/appdev.120/e10380/oracle_mysql_compared.htm#g1034154

8) Convert Oracle variable definitions to MySQL definitions
i.e. "xyz:=123" => "SET xyz=123" you can use RegEx to do this.
Example expressions using notepad++ are
Find RegEx - "(db[a-zA-Z]+) :="
Replacment Expr - "SET \1="
And for assignment expressions
Find RegEx - "([a-zA-Z]+) := ([a-zA-Z0-9\'\-]+)"
Replacment Expr - "SET \1 = \2"
Preserving white space,
Find RegEx - "^([ ]+)([a-zA-Z]+):= ([a-zA-Z0-9\'\-]+)"
Replacment Expr - "\1SET \2 := \3"

Similiary for CONSTANT DECLARATIONS use
Find RegEx - ([a-zA-Z_]+) constant ([a-zA-Z0-9)(]+) := ([0-9]+);
Replace Expr - DECLARE \1 \2 DEFAULT \3;

9) Similiarly change FUNCTIONS like this
Oracle -
FUNCTION getFunction ( dbId IN NUMBER) RETURN NUMBER
IS
<Function Body>

MySQL -
CREATE FUNCTION getFunction ( dbId IN INT) RETURNS INT
RETURN
<Function Body>

10) Replace functions as follows
Oracle => MySQL
-----------------
NVL() => IFNULL()
Find RegEx - NVL\(([a-zA-Z_\.]+)\, ([a-zA-Z0-9\'\-]+)\)
Replace Expr - IFNULL(\1, \2)

11) CURSOR Declaration change like the following
DECLARE CURSOR C_cur(type declr) IS <SELECT STATEMENT> (Ora)
To
C_cur CURSOR FOR <SELECT STATEMENT> (MySQL)
(Note: MySQL uses, curvariable CURSOR, this is different from Oracle.

12) Change labels (possibly for GOTO) from <<END_FUNC>> => :END_FUNC
13) Change Exception Handling as per the following -
Oracle -
EXCEPTION
WHEN NO_DATA_FOUND THEN stmts..;
MySQL -
DECLARE CONTINUE HANDLER FOR NOT FOUND stmts..; -- Set a variable here like SET not_found =1
Check for the variable, to decide the error condition, when running the query.
REPEAT
stmts..;
UNTIL not_found END REPEAT;

14) If you have Oracle cursor with the FOR loop
You can change it thus
i) First declare a Boolean - DECLARE done BOOL DEFAULT FALSE;
ii) And a continue handler, which will set this boolean -
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done := TRUE;

iii) And then re-write the FOR loop as a loop with an IF check
i.e.
Change -
-- cursor definition for MYCURSOR
CURSOR MYCURSOR(someid number) IS
FOR I IN MYCURSOR
LOOP
-- stmts;
END LOOP;
To -
-- cursor definition for MYCURSOR
DECLARE CURSOR MYCURSOR FOR -- select query
OPEN MYCURSOR;
loop_mycursor: LOOP
FETCH MYCURSOR INTO variables --(a,b,c where a b c have been declared before);
IF done THEN
CLOSE MYCURSOR;
LEAVE loop_mycursor;
END IF;
-- stmts;
END LOOP;

15) Ensure your Oracle VARCHAR & VARCHAR2 references which have no default values are provided with some
e.g. VARCHAR(15). Note: there is no VARCHAR2 type in MySQL as of 5.0.1

16) MySQL (5.1) does not support the GOTO statement, you can change a block using teh following construct
to access the GOTO statement.
lbl: BEGIN
LEAVE lbl;
END lbl;

17) Convert function calls from oracle to mysql
E.g.
If converting INT to VARCHAR or vice versa, don't have to use an equiv functions to TO_CHAR() as MySQL
will automatically cast types, if that doesn't work try using a CONCAT() to force INT to VARCHAR and CAST/CONVERT().
TO_CHAR() to FORMAT() -- (use LPAD() as well, if you are looking to pad the data)
TO_CHAR(,'xxx') to HEX() -- For creating Hex Strings

Use the following RegEx to simplify conversion :-
Find RegEx - "ltrim\(to_char\(([a-zA-Z]+), \'[x]+\'\)\)"
Replacment Ex - "HEX(\1)"

For Dates -
Convert
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MI') , to
DATE_FORMAT(CURRENT_TIMESTAMP, '%Y%m%d%H%i') ,
Note: Replace SYSDATE with CURRENT_TIMESTAMP
Refer - http://www.adp-gmbh.ch/ora/sql/to_char.html for Date conversion formats.

18) Change any join qeueries to use MySQL Syntax.
Shortcuts like (+) need to be replaced with LEFT OUTER JOIN (complete ANSI syntax)
Oracle uses (+) in the predicate to indicate if to return the row even if this column is NULL.
E.g.
select count(*) from v$session s, v$process p where p.addr=s.paddr (+) and s.sid (+) = -1;
Here even if s.paddr , s.sid have NULL values rows will be returned.
Whereas, in ANSI syntax
select count(*) from v$process p left join v$session s on p.addr=s.paddr where s.sid = -1;
The left join is done first, then the rows are filtered, thus any rows with s.sid = NULL will be filtered.
Way out is to, add the predicate to the JOIN ON clause,
select count(*) from v$process p left join v$session s on p.addr=s.paddr and s.sid = -1;

E.g.
SELECT A.STATUS, A.VISIBLE, NVL(A.STATUS_STRING_CODE, -1), NVL(A.STATUS_STRING, ''), NVL(A.LOCALE_STRING, ''),
NVL(A.DEVICE_GROUP, 0), NVL(A.CLIENT_IP_ADDR, ''), NVL(A.CLIENT_PORT_NO, 0), NVL(C.DEVICE_GROUP, 0),
NVL(A.MSISDN, '')
INTO dbStatus, dbVisible, dbStatusStrCode, dbStatusStr, dbLocalStr, dbDeviceGrp, dbIpAddr, dbPortNo, dbOffDevGrp,
dbMsisdn
FROM USERS A, USER_DEVICES B, DEVICES C
WHERE A.USER_ID = dbPalId
AND A.USER_ID = B.USER_ID (+)
AND B.DEVICE_ID = C.DEVICE_ID (+);

Changed to (MySQL) -
SELECT A.STATUS, A.VISIBLE, IFNULL(A.STATUS_STRING_CODE, -1), IFNULL(A.STATUS_STRING, ''), IFNULL(A.LOCALE_STRING, ''),
IFNULL(A.DEVICE_GROUP, 0), IFNULL(A.CLIENT_IP_ADDR, ''), IFNULL(A.CLIENT_PORT_NO, 0), IFNULL(C.DEVICE_GROUP, 0), IFNULL(A.MSISDN, '')
FROM USERS A LEFT JOIN USER_DEVICES B ON B.USER_ID = A.USER_ID LEFT JOIN DEVICES C ON B.DEVICE_ID = C.DEVICE_ID
WHERE A.USER_ID = 1;

In case you have multiple tables in the Join Query chain the ANSI JOIN statements i.e. tableA LEFT JOIN tableB ON condn1 LEFT JOIN tableC ON condn2;
Thus
SELECT COUNT(*) as oracount
FROM USER_PALS A, USER_PALS B, USERS C
WHERE A.PAL_USER_ID = B.USER_ID (+)
AND B.PAL_USER_ID = C.USER_ID (+)
AND A.USER_ID = 1
Will become
SELECT COUNT(*) as myscount
FROM USER_PALS A LEFT JOIN USER_PALS B ON A.PAL_USER_ID = B.USER_ID LEFT JOIN USERS C ON B.PAL_USER_ID = C.USER_ID
WHERE A.USER_ID = 1


19) Since there are no sequences in MySQL, and the MySQL LAST_INSERT_ID() function
only returns values on the last insert statement, change
Oracle stmts like - SELECT SOME_SEQ.NEXTVAL FROM DUAL;
To this in MySQL - SELECT MAX(SOME_COL) + 1 FROM SOME_TABLE;

20) IF ELSE conditions,
The MySQL construct is ELSEIF and not ELSIF (as per Oracle).
Additionally if you are incrementing a variable
eg. SET A := A + 2; make sure to add brackets, as the MySQL parser wont accept otherwise
i.e. SET A := (A + 2);

21) Change procedure invocations to CALL getProcedure() *-check
22) MySQL does not like the Oracle No-op stmt NULL;(E.g. in IF THEN ELSE, SWITCH ... CASE)
replace with empty "BEGIN END;" block, as MySQL tolerates
empty BEGIN END blocks.

Options: ReplyQuote


Subject Views Written By Posted
HOW TO CONVERT ORACLE TO MYSQL 39717 linda hashim 01/03/2005 10:56PM
Re: HOW TO CONVERT ORACLE TO MYSQL 23699 frank_alw 02/10/2005 05:20PM
Re: HOW TO CONVERT ORACLE TO MYSQL 11129 Crucifix 08/26/2005 03:28AM
Re: HOW TO CONVERT ORACLE TO MYSQL 9998 prabhakaran n 03/09/2006 06:57AM
Re: HOW TO CONVERT ORACLE TO MYSQL 9235 Roland Bouman 03/09/2006 02:09PM
Re: HOW TO CONVERT ORACLE TO MYSQL 6721 hanju jang 06/20/2006 06:42PM
How to convert this Oracle PL/SQL Function to MySQL 17422 errolbug 10/10/2005 04:16AM
Re: How to convert this Oracle PL/SQL Function to MySQL 24444 Roland Bouman 10/14/2005 06:49AM
Re: How to convert this Oracle PL/SQL Function to MySQL 29312 Nausher Cholavaram 11/30/2007 01:53PM
Re: How to convert this Oracle PL/SQL Function to MySQL 4187 anupam tripathi 08/06/2008 03:24AM
Re: HOW TO CONVERT ORACLE TO MYSQL 5037 Max Walton 01/23/2006 10:37AM
Re: HOW TO CONVERT ORACLE TO MYSQL 4098 Roland Bouman 01/23/2006 02:26PM
How to convert Oracle bulk collection, table of records into mysql 6462 Sultana Jahan Rini 02/12/2006 03:42AM
Re: How to convert Oracle bulk collection, table of records into mysql 4226 Roland Bouman 02/12/2006 04:18AM
Re: How to convert Oracle bulk collection, table of records into mysql 5623 Sultana Jahan Rini 02/12/2006 04:51AM
Re: How to convert Oracle bulk collection, table of records into mysql 11119 Roland Bouman 02/12/2006 07:29AM
Error handling 3431 NITESH SINHA 09/30/2006 01:18AM
Re: HOW TO CONVERT ORACLE DB APP TO MYSQL 4064 ipbasu 02/06/2007 12:17AM
Re: HOW TO CONVERT ORACLE DB APP TO MYSQL 5683 Roland Bouman 02/06/2007 06:18AM
Re: HOW TO CONVERT ORACLE TO MYSQL 4804 srikanth goud 06/23/2006 08:17PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.