Solved

Oracle to Mysql Migration

Posted on 2014-01-09
7
1,130 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 100 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 400 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

707 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