3 useful Oracle queries

Yesterday I posted 3 useful MySQL queries that I use almost every day. As I also work with Oracle I have to use the equivalents for that database i.e. I regularly have to create database update scripts – scripts that create tables or add new columns to existing tables or populate new tables with base data. One requirement is that these scripts be re-runnable. For example, if a new table doesn’t exist it should be created, if it does exist then the update script should continue without throwing an error. For these situations I find the following simple queries very useful. Wrap them in functions written in your language of choice and you have an easy way to check the current status of your Oracle database before taking action.

1. Check if a table exists in the current database schema

select table_name
from user_tables
where table_name = '__TABLE_NAME__'
2. Check if a column exists in a table
select column_name as found
from user_tab_cols
where table_name = '__TABLE_NAME__'
and column_name = '__COLUMN_NAME__'
3. Check if a table contains any data
select 1
from __TABLE_NAME__
where rownum = 1
After running each of these queries, simply check if any rows were returned. A non-zero row count indicates the existence of the table, the column or the data as appropriate. These three queries, and a couple of variations on them, can also be found on my Gist page.
An alternative method for checking if a table or column exists before creating it is to just go ahead with your create / alter table and catch any error that comes along. This is not a sensible or correct approach in most situations. The existence of a table or column is a knowable property that is easily checked – throwing an error then scrambling around to determine what caused it by comparing error codes is crude, avoidable and not at all what exception handling is intended for.





Preventing the return of NULL values in Oracle, SQL Server and MySQL queries

Sometimes, when writing a query, you don’t want to return a NULL value. This is particularly important during concatenation where the presence of a NULL in one or more of the fields to be joined may result in a NULL being returned (depending on the database being used).
For example, in MySQL the simple, but contrived, query:

  select Concat(firstname, NULL) as mycol from mytable

will always return NULL because one of the values being concatenated with the Concat function is NULL.
Similarly, say we’d like to produce a report using this query:

  select firstname, lastname from mytable

but wherever the firstname is NULL we’d like to replace it with the text ‘*****’.
This is very easy to achieve and the approach is very similar in MS SQL Server, Oracle and MySQL.

To detect that firstname is null in MS SQL Server we use the obviously named IsNull function:

  select IsNull(firstname,'*****'), lastname from mytable

When this query is run, the IsNull function will evaluate firstname. If it is NULL then the replacement value (‘*****’ in this case) will be returned, otherwise the contents of firstname will be returned.

MySQL is almost the same, except the function we need is named IfNull:

  select IfNull(firstname,'*****'), lastname from mytable

In Oracle the syntax is similar but the function is the more obscurely named NVL(Null VaLue?):

  select NVL(firstname,'*****'), lastname from mytable

Three different databases, three differently named functions, one consistent result – a NULL converted into a more usable result.