Hi folks,
In this post I talk about a few things you may find interesting about Oracle if you are a software developer with MySQL background getting started with Oracle.
I’ve known about Oracle for a long time. Long ago I remember seeing jobs for Oracle DBAs that required Oracle certification.
It seemed like an interesting software to know how to use but there wasn’t a strong push to learn it.
While developing applications professionally, MySQL was generally the database of choice. Later I ran into Postgres in the course of professional work, but not Oracle.
Eventually I got a back-end developer role in a large enterprise and then I met Oracle properly for the first time.
Here are some thoughts on Oracle, looking at it from the perspective of someone with MySQL background.
You’re likely to run into Oracle in large enterprise where lots of data are handled. Not so much in small to medium-scale enterprises. This is because Oracle has lots of powerful features that are targeted towards large enterprises.
For the most part you can write queries using the same old SQL on both MySQL and Oracle databases. SELECTs, JOINS, subqueries work in the same manner. In Oracle, subqueries are called inline views.
Tools: With mysql you use tools like phpMyAdmin and MySQL Workbench. With Oracle the popular clients I’ve come across are Oracle SQL developer and Toad.
When querying data with Oracle via SELECT statement, you can use multiple servers by using the ‘parallel’ keyword e.g. SELECT /*+ parallel */ FOO FROM SOMEDB.SOMETABLE. MySQL doesn’t have that concept.
The way you apply limits and offsets are different. In MYSQL, you can use something like
SELECT ….. LIMIT 10;
But in Oracle, you write
SELECT … FETCH FIRST 10 ROWS ONLY;
In Oracle, when you want to compare values that could possibly be null, you use the NVL function
e.g.
SELECT FOO FROM table1
JOIN table2 on
NVL(table1.some_col, ‘IsNul’) = NVL(table2.some_col, ‘IsNul’)
In terms of connecting to MySQL from your programming environment, if you use Python, then Oracle provides a very good library called cx_Oracle. It provides similar abstractions like you would get with MySQL connector. So you have your cursor which you can use to run queries, etc.
If you use a Mac and need to work with Oracle locally, you would need to use a virtual machine via tools like VirtualBox. With MySQL, you can install it directly on a Mac.
Furthermore, the COMMIT statement is very important in Oracle. You have to commit transactions to persist changes to the database, e.g. creating a table, deleting rows, etc. Usually in MySQL, you just delete rows and that’s it, the change persists.
So, in general, if you’re comfortable with other databases like MySQL or Postgres, you should feel at home with Oracle. Oracle has some peculiarities, but you should be just fine.
That’s it for now. Till next time, happy software development.