?
Solved

Oracle to Mysql Migration

Posted on 2014-01-09
7
Medium Priority
?
1,174 Views
Last Modified: 2014-01-18
I've been tasked with converting some oracle stored procedures to mysql.  I've never worked with oracle and don't have access to it to do any testing and this is the first time I've done anything with stored procedures.  So, I'm working blind as I learn.

Most of the issues I've been able to figure out, but am having trouble with the exception handling and DBMS_OUTPUT.PUT_LINE statements.  Can someone explain the following oracle statements and how they would be done in mysql?

     exception
          when dup_val_on_index then
              null;
          when others then
              null;
     end;

Open in new window

and
DBMS_OUTPUT.PUT_LINE('Process Started ' || TO_CHAR(SysDate, 'SSSSS'));

Open in new window

0
Comment
Question by:FishMonger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 16

Assisted Solution

by:Swadhin Ray
Swadhin Ray earned 400 total points
ID: 39770370
Oracle exceptions can be handled in MYSQL but not sure if you can get the same errors what you get from Oracle :

Check for exceptions on Oracle:

http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/errors.htm#LNPLS007

Check this link on MYSQL:
http://dev.mysql.com/doc/refman/5.5/en/declare-handler.html
0
 
LVL 2

Expert Comment

by:BarryMcGillin
ID: 39776205
@slobaray poits to the http://dev.mysql.com/doc/refman/5.7/en/declare-handler.html page for error handling which is a simple rewrite for Oracle to MySQL if you need it.

Also for the dbms_output version
DBMS_OUTPUT.PUT_LINE('Process Started ' || TO_CHAR(SysDate, 'SSSSS'));

the mysql version is something like this
SELECT CONCAT('Process Started',DATE_FORMAT(SYSDATE(),'%s%f'));
0
 
LVL 16

Expert Comment

by:Swadhin Ray
ID: 39776232
@ BarryMcGillin: You are absolutely correct .

Select CONCAT('Process Started',DATE_FORMAT(SYSDATE(),'%s%f'));

will print the same message what DBMS_output will give in Oracle.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 28

Author Comment

by:FishMonger
ID: 39783473
Sorry for the delay in responding.  I'm usually more attentive/responsive, but I dropped the ball in this case.

slobaray,
I was hoping for something more than just RTM.  I read the docs prior to starting the thread but something in them was not "clicking" and since I don't have access to oracle to do any direct comparison tests, I was hoping that someone else did and could expand on the info with example comparisons.

Barry,
My first thought and test was using a select statement like your example, but I think it does something slightly different.  My understanding of the oracle docs indicate that DBMS_OUTPUT.PUT_LINE only ouputs its info if the calling process explicitly requests the info via DBMS_OUTPUT.GET_LINES.  Am I wrong?
0
 
LVL 2

Accepted Solution

by:
BarryMcGillin earned 1600 total points
ID: 39785822
No, you're not wrong, but mysql does not have a concept like DBMS_OUTPUT.
BTW, you can get Oracle anytime.  Theres a great Vm you can download and run with Oracle's Virtual Box which gives you a configured Oracle DB you can play with anytime, which you can get here
0
 
LVL 28

Author Comment

by:FishMonger
ID: 39785915
Ok, thanks.  I'll do some more testing on the next procedure I need to convert.

The inability to do any testing with Oracle is not a technical issue, it's a company management issue.  I've been told that I can not setup/use an Oracle test environment.
0
 
LVL 28

Author Comment

by:FishMonger
ID: 39785948
What about the exception handling example in my opening post?  Would this be the mysql equivalent?

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' BEGIN END;

Open in new window

0

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, we’ll look at how to deploy ProxySQL.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question