Solved

Oracle to Mysql Migration

Posted on 2014-01-09
7
994 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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Creating and Managing Databases with phpMyAdmin in cPanel.
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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now