Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle to Mysql Migration

Posted on 2014-01-09
7
Medium Priority
?
1,211 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
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 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

610 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