Getting a specific time of day in Oracle

OracleHere’s a way to force an Oracle date calculation to find a specific time of day every time. This is useful when using the DBMS_JOB package for job scheduling jobs as it gives you full control over the time of day a job runs.

The Oracle date data type is accurate down to the second, but if you want to ignore this precision and get to a certain time you can use the TRUNC function to truncate the date down to just the day, then add as many hours or minutes as you want.

By default the TRUNC command will truncate a date and time to midnight on the start of that date, so to consistently get, for example, 2:00 pm, we can truncate the time and add 14 hours.

SQL> SELECT to_char(trunc(sysdate) + 14/24, 'MM/DD/YYYY HH:MI AM') FROM dual;

02/13/2006 02:00 PM

Here we truncate to midnight and add 14/24 of a day (14 hours). This works on the idea that the default increment for math with oracle dates is 1 day, so if we add 14/24 days we’re adding 14 hours. You could also use the NUMTODSINTERVAL function as described here, but it makes the command longer.

Applying this to scheduled jobs

When scheduling jobs with the DBMS_JOB package you specify the interval with a string which evaluates to an Oracle date. The interval is evaluated every time a job runs to calculate the next time the job should run. Typically the interval is something like 'SYSDATE + 1' if you want the job to run once a day.

The interval is evaluated each time the job starts. If the job runs at 11:00 PM, the interval would evaluate to 11:00 pm the following day; however, if you force the job to run with the DBMS_JOB.RUN at 2:30 pm, the interval would be evaluated to 2:30 pm the next day and you have changed your job to run at 2:30 every day thereafter.

To make sure things always run at the same time we can apply the method above to the interval when we submit the job.


This would update job number 21 to use the string 'TRUNC(SYSDATE + 1) + 23/24' to calculate the next time this job should run. In this case, we take SYSDATE, plus 1 day, truncate it to midnight of that day, and add 23 hours. The end result is, no matter what time the job is run the interval will evaluate to 11:00 pm the next day.

oracle, database, dba, database programming, database administration, sql, plsql, pl/sql

Oracle conditions and how they handle NULL

Two students in my database class came to me yesterday with questions about a nested query they had written. The query looked fine, but when executed the query returned no rows. After considerable investigation I realized the subquery being evaluated against was not only returning the obvious values, but also returned NULL.

It turns out NULL gets very special treatment in Oracle. NULL is treated as unknown. Basically it cannot be evaluated against anything because you can’t evaluate something you can’t measure. To try to make this clear I offer the following examples:

Note: dual is a table used frequently in testing. It has no data but can be used to return calculations, text, test conditions, etc.

Here’s a basic select and condition that will always succeed:

SELECT 'true' FROM dual WHERE 1 = 1;


I think we can agree that, at least in the reality where we care to evaluate database queries, that 1=1, so this query returns the rows selected. In this case we have only selected one row, the string ‘true’

Now let’s take a look at the unusual behavior of NULL. First, here’s a query that should return no rows:

SELECT 'true' FROM dual WHERE 1 = NULL;

no rows selected

This makes sense because NULL does not equal 1, but now let’s look at another form of this statement:

SELECT 'true' FROM dual WHERE 1 != NULL;

no rows selected

Logically we think that 1 is different from NULL, so this should have returned ‘true’, but Oracle has a different idea. Oracle evaluates this by asking “Does 1 not equal an unknown value?” This makes as much sense to Oracle as asking “Does 3.17 equal a tree?” or “Is my birthday red?” so no matter what makes sense to us, Oracle evaluates this condition as FALSE.

We can take this one step further by executing the following query:


no rows selected

This illustrates that Oracle is completely unwilling to even try to evaluate NULL, but it starts to make sense that you would not say one unknown is, or isn’t equal to another unknown; therefore, NULL cannot be said to either be equal, or not equal to NULL.

Now let’s take a look at an IN condition.

SELECT 'true' FROM dual WHERE 5 NOT IN (1, 2, 3);


This IN statement returns TRUE because 5 is NOT IN the set of 1, 2, 3. Now let’s look at a slight variation.

SELECT 'true' FROM dual WHERE 5 NOT IN (1, 2, 3, NULL);

now rows selected

While 5 does not explicitly appear in the set, we do not know what NULL is. Since we cannot evaluate on the unknown NULL, the condition fails and no rows are returned.

So we can see that NULL must be handled as a special case. To handle this we must use IS NULL or IS NOT NULL. If we want to evaluate two values to see if they are both NULL we could use the following:



Here we see that, while NULL = NULL is not a valid condition, NULL IS NULL works just fine. Now let’s consider this in the context of a subquery.

SELECT first_name, last_name FROM faculty
WHERE id NOT IN (SELECT instructor_id FROM class);

This query will be valid only if the subquery SELECT instructor_id FROM class does not return any NULL values. If there are entries in the class table which have NULL values in the instructor_id column, the WHERE condition will always fail and no rows will be returned.

To make this statement more reliable (since we may plan not to have any NULL values now but some may make it in there) we can add a condition to the subquery.

SELECT first_name, last_name FROM faculty
SELECT instructor_id FROM class
WHERE instructor_id IS NOT NULL);

Now the result set from the subquery will never contain NULL and the condition will be properly evaluated.

oracle, dba, database administration, database, database programming