MySQL Forums
Forum List  »  Oracle

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
43850
January 03, 2005 10:56PM
25642
February 10, 2005 05:20PM
12645
August 26, 2005 03:28AM
11290
March 09, 2006 06:57AM
10590
March 09, 2006 02:09PM
7847
June 20, 2006 06:42PM
Re: How to convert this Oracle PL/SQL Function to MySQL
55023
November 30, 2007 01:53PM
6301
January 23, 2006 10:37AM
5192
January 23, 2006 02:26PM
4691
September 30, 2006 01:18AM
5142
February 06, 2007 12:17AM


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.