MySQL and Oracle

MySQL and Oracle

Published on: Category: Oracle

Over the last couple of months, Patrick Barel has been working on a MySQL-based system for his current customer. This made him realize, once again, how easy we can do things in an Oracle-based system. 

On one hand, it is kind of awkward to be unable to do what you are familiar with. On the other hand, it makes you come up with solutions when issues arise. Some are better than others, but in the end all the solutions work.

What follows are just a few of the quirks I came across, and for which I had to find another solution.

No packages

In my PL/SQL code, I like to use packages for just about everything. They allow me to logically group programs that belong together. In MySQL, there is no object like package. Everything is a stand-alone function or procedure, which means there are no private programs. In this project, however, I have a user that ‘owns’ the objects (tables, view, programs etc.) and users that are granted access to the objects they need. They have no access to tables and just read access to views.

No create or replace

In Oracle, you can create or replace almost every object (except for tables). In MySQL, you can use create or replace only on views. In every other situation, you have to drop the object first before you can create a new version. Not a big issue, since I am using scripts for the objects anyway, so one of the first lines is to drop the object.

Drop if exists

We want to minimize errors when running scripts, even if the errors aren’t a problem. If you try to drop an object that doesn’t exist, the database (both Oracle and MySQL) will return an error; something like ‘object to be dropped doesn’t exist’. I actually don’t see this as an error because the end result is the same. But DBAs are allergic to these kinds of errors, or any error. MySQL provides us with the IF EXISTS syntax. This will never result in an error, just the desired situation: the object is gone.

I have created something similar in Oracle. In this case, it is an anonymous block, but you could also create a stored procedure. Additionally, if you need to suppress a different error code, you can adapt the code accordingly.

  1. declare
  2. table_or_view_does_not_exist exception;
  3. pragma exception_init(table_or_view_does_not_exist, -00942);
  4. object_does_not_exist exception;
  5. pragma exception_init(object_does_not_exist, -04043);
  6. sequence_does_not_exist exception;
  7. pragma exception_init(sequence_does_not_exist, -02289);
  8. begin
  9. execute immediate q'[drop table t purge]'; -- execute the drop
  10. exception
  11. when table_or_view_does_not_exist
  12. or object_does_not_exist
  13. or sequence_does_not_exist then null; -- on purpose, hide any error
  14. end;
  15. /

Functions need purity info

Every function you create needs information about its purity level. It doesn’t have to be accurate (as I found out ;-)) but you have to add at least one of the following items: DETERMINISTIC, NO SQL, or READS SQL DATA. You can and probably should add one or more items, although some combinations are not allowed, which makes sense, I think.

Delimeter

The default delimiter for a statement is the semi-colon (;). In Oracle, this is officially the slash (/). When you want to create a program, the first thing you do is tell MySQL to use a different delimiter to end a statement.

  1. Delimiter //
  2. Create procedure foo
  3. Begin
  4. Select ‘bar’;
  5. End//
  6. Delimiter ;

No DBMS_OUTPUT.PUT_LINE

I don’t know about you, but when I’m building code and I don’t exactly know how things work (such as new features, or even a new database environment) I put a lot of DBMS_OUTPUT.PUT_LINE statements in my code, so I can see what it is doing. That is, only during development and when I don’t have access to a nice debugging IDE.

But there is no such thing as a DBMS_OUTPUT.PUT_LINE in MySQL. As far as I know, there is no way to output messages to the console. If there is, please let me know in the comments ;-).

Therefore, I created a procedure PL myself (if you have a choice, choose a shorter program name than the 20-character DBMS_OUTPUT.PUT_LINE ;-)). If you just select something without an into, the results get echoed to the console.

  1. DROP PROCEDURE if exists PL;
  2. DELIMITER //
  3. CREATE PROCEDURE PL (output VARCHAR(255))
  4. BEGIN
  5. SELECT output;
  6. END;
  7. //
  8. DELIMITER ;

Missing SQLERRM

There is no function to return text for an error code. At least, I couldn’t find it. But I needed a function to return the text associated with the error code that my code could return. What better name for such a function than SQLERRM ;-)? It doesn’t return the text for the MySQL error codes, but at least I can work a bit like I do in my Oracle databases.

No sequences

There are no sequences available in MySQL. At least, not in the way we know them in Oracle. I needed a cross session counter to return numbers from 1 to 99 in order and loop back to 1 at the end.

In Oracle, I would create a sequence like this:

  1. create sequence s
  2. minvalue 1 maxvalue 99
  3. start with 1 increment by 1
  4. cycle nocache
  5. /

But, like I said, nothing like that is available in MySQL. So, I came up with my own version. It consists of a table and some code:

  1. drop table if exists sequences;
  2.  
  3. create table sequences
  4. ( name varchar(32)
  5. , min integer
  6. , max integer
  7. , increment tinyint
  8. , last integer
  9. , primary key (name)
  10. )ENGINE = InnoDB;
  11.  
  12. insert into sequences(name, min, max, increment, last) values ('my_seq', 1, 99, 1, 0);
  13.  
  14. drop procedure if exists my_seq_val;
  15. drop function if exists my_seq_currval;
  16. drop function if exists my_seq_nextval;
  17. delimiter $$
  18. create procedure my_seq_val(
  19. in increment boolean
  20. , out result integer)
  21. reads sql data
  22. modifies sql data
  23. comment 'gets (and increments) the last value'
  24. contains sql
  25. sql security invoker
  26. begin
  27. if increment then
  28. update sequences set last=case
  29. when last+increment > max then min
  30. else last+increment
  31. end
  32. where 1=1
  33. and name = 'my_seq';
  34. -- Even though this is a procedure, it gets called from a function
  35. -- Error code 1422: Explicit or implicit commit is not allowed in a stored function or trigger
  36. -- commit;
  37. end if;
  38. select last into result from sequences where name = 'my_seq';
  39. end$$
  40.  
  41. create function my_seq_currval()
  42. returns integer
  43. deterministic
  44. reads sql data
  45. comment 'Gets the current value from the sequence'
  46. contains sql
  47. sql security invoker
  48. begin
  49. declare result integer;
  50. call my_seq_val(false, result);
  51. return result;
  52. end$$
  53.  
  54. create function my_seq_nextval()
  55. returns integer
  56. deterministic
  57. modifies sql data
  58. comment 'Gets the next value from the sequence'
  59. contains sql
  60. sql security invoker
  61. begin
  62. declare result integer;
  63. call my_seq_val(true, result);
  64. return result;
  65. end$$
  66.  
  67. delimiter ;

It’s probably not as fast as the Oracle version, and I don’t know what will happen if two sessions increment the sequence at exactly the same time, but the chance that this will happen is low enough that I’ll take my chances.

Writing files

One of the requirements of the application was to write files with status changes that can be transported to different systems. When it comes to file handling, MySQL is rather limited compared to Oracle (which is also limited itself). It is not possible to overwrite or delete a file. When you try to write a file that already exists, you’ll run into an error. This is one of the reasons why I needed the sequences. The good news, in this case, is that we have a process in place that moves the file from the location where it is written to a different location. This allows us to write a file with the same number at a later stage.

No dynamic SQL in Function/Trigger

To create files with different sequence numbers in the name, I wanted to use a bit of dynamic SQL. This worked really well until I wanted to call my procedure from a function or a trigger. You are not allowed to run dynamic SQL in a function or a trigger. Since it was a rather simple statement and there were ‘only’ 100 options, I ended up with a big case statement where I had to specify a different filename for every value of the sequence.

All in all, it was a fun project to work on. It made me realize (again) what a great environment the Oracle database is; how easily things can be done, and how many possibilities you have. However, if you come up with solutions that require more thought and coding, you can also create a nice system in MySQL.

Patrick Barel
About the author Patrick Barel

Patrick studied Telematics and has worked as a full-time Oracle Developer since 1999. After his study, he followed several PL/SQL courses, including seminars by Steven Feuerstein. He also completed Oracle's OCA and OCP programme in the field of SQL and PL/SQL. Besides working with SQL and PL/SQL, he wrote different plug-ins for PL/SQL Developer (http://www.allroundautomations.com/). Patrick als publishes articles on his own blog (http://blog.bar-solutions.com/) In 2011, Patrick was appointed Oracle ACE for his contribution to the field of SQL and PL/SQL. In 2015, Patrick was awarded the Developer Choice Award. In 2019, Patrick was appointed Oracle ACE Director for his contributions to the Oracle community.

More posts by Patrick Barel
Comments (2)
  1. om 20:08

    On the equivalent of dbms_output.put_line, just out of curiosity, why would a procedure be necessary in stead of using plainly SELECT 'here is my line's?

  2. om 16:04

    Hi Henri,

    Using a procedure has two advantages:
    1) I can add a condition (if I want to) to turn it on and off, without changing all the code.
    2) pl('here is my line') is less characters than select 'here is my line'

    The 2nd one is most important ;-)

Reply