I saw a question about how to split delimited string in MySQL. MySQL does not include a function to split a delimited string. So I decided to write a simple one:

When we call this function as SPLIT( ‘ali,ahmet,mehmet’, ‘,’, 3 ), we get the 3rd item “mehmet”. If we call this function as SPLIT( ‘ali,ahmet,mehmet’, ‘,’, 5 ), it will return an empty string.

MySQL doesn’t support the INTERSECT and MINUS set operators. The INTERSECT operator takes the results of two queries and returns only rows that appear in both result sets. The MINUS operator takes the distinct rows of one query and returns the rows that do not appear in a second result set. We can rewrite these queries by using JOIN operator:

Sample query with the MINUS operator:

In MySQL:

Read More

Oracle 11g allows tables to be marked as read-only using the ALTER TABLE command:

To make it writable again, issue the following command:

In previous versions of Oracle, there was no command to make a table read-only, but we can use a little trick to do it. All we need is to create a constraint and make it disable:

Because the constraint is disabled, it is not enforced but it will not let you change the data to ensure that data still conforms to the constraint’s requirements. Unfortunately, direct path loads will bypass this, so we also need to issue the following command:

This will also prevent user to drop this table. Another option is to mark the tablespace which contains the table as read-only, but it’s not practical because it wll make all the objects read-only residing in the tablespace.

In a hierarchical query, if you specify either ORDER BY or GROUP BY, the hierarchical order will be destroyed. If we need to sort the rows of siblings of the the same level (while keeping the hierarchical order) we can to use ORDER SIBLINGS BY clause. Let’s create a sample table:

Let’s list these rows in a hierarchical order:

Read More

I’ve found an introducing video for the Oracle Application Express (APEX). Although the video positions the APEX as a replacement for “personal databases (Ms Access) and spreadsheets (Excel etc)”, I think it’s more than that. It’s a true RAD tool than you can create useful business applications. Here’s the video: