database development – Life After Coffee

Tracking changes to tables in Oracle

On my story about using the minus SQL operator to compare tables, Blake asked what other more efficient methods could be used for tracking changes between tables.

Here are a couple options you could consider to compare table contents instead of the ‘minus’ operator:

Oracle change tables are probably the best for tracking changes internal to the database. You can see an example of them here.

This is what most Oracle data warehouse ETL is built with.

Custom triggers on insert, update and delete can enable similar functionality to change tables (change tables actually work with internal triggers.) You can have the triggers insert your data into another table or just keep some metadata in a separate table to indicate which rows have changed.

Simply adding an activity date column to your tables, populated with a trigger may be enough to track changes and keep databases in sync. If you know, for example, that the database was cloned at midnight on the 1st of the month, then to see what has changed since then you just need to examine all rows with an activity date after that. The activity date can be useful for several other things as well making this a fairly desirable approach.

Blake has an intriguingly eclectic blog himself which is well worth a visit. Thanks for the question Blake!

oracle, database, dba, database development, database administration

SQL Date Math and the Leap Year

Ying pointed out on my article about finding the last Saturday of the year that adding a year to a DATE datatype works great with syntax like this:

SQL> select sysdate+numtoyminterval(1, 'YEAR') from dual;

SYSDATE+N --------- 27-NOV-07

It works great right up until you hit February, 29:

SQL> select to_date('02/29/2004', 'MM/DD/YYYY')+numtoyminterval(1, 'YEAR') from dual; * ERROR at line 1: ORA-01839: date not valid for month specified

So what can we do about this? Well, as far as I can guess there are only two options. First, you could avoid ever using +numtoyminterval(1, 'YEAR') in your code and instead use +numtodsinterval(365, 'DAY') like this:

SQL> select to_date('02/29/2004', 'MM/DD/YYYY')+numtodsinterval(365, 'DAY') from dual

TO_DATE(' --------- 28-FEB-05

This may cause some confusion as 365 days after January 15, 2004 would be January 14, 2005 and 365 days after January 1, 2004 is actually December 31, 2004, but it should never throw an ORA- error.

UPDATE: Ying commented with this solution which is probably better in most scenarios. By using the add_months function we can step forward a number of months and Oracle will automatically truncate to the last day of the month if the resulting month has fewer days than the starting month.

SQL> SELECT Add_Months(to_date('02/29/2004', 'MM/DD/YYYY'), 12) from dual;

ADD_MONTH --------- 28-FEB-05

The other alternative is to handle the exception programmatically. Either avoid inserting a February 29 or avoid using the +numtoyminterval(1, 'YEAR') only when handling a February 29.

I’d say option 1 is the better choice, but both have their drawbacks.

This is the same error you get if you attempt to add a month to, say January 30th since there is never a February 30th.

SQL> select to_date('01/30/2006', 'MM/DD/YYYY')+numtoyminterval(1, 'MONTH') from dual; select to_date('01/30/2006', 'MM/DD/YYYY')+numtoyminterval(1, 'MONTH') from dual * ERROR at line 1: ORA-01839: date not valid for month specified

I guess this is why most things are good for “30 days” not “1 month”.

Thanks Ying for pointing out this anomaly.

database, oracle, date, to_date, timestamp, time, database administration, database development

SQL to find the last Saturday of the year

Satya commented on my post about finding the first or second Monday in a month asking how to find the last Saturday of a year using SQL. This is a good question as I think it is typical of the battles people fight with dates in Oracle.

So here we go… For this example we’ll use sysdate but you can use a date column or a to_date instead.

SQL> select sysdate from dual;

SYSDATE --------- 13-SEP-06

Now we’ll jump forward a year and start working backwards:

SQL> select sysdate+numtoyminterval(1, 'YEAR') from dual;

SYSDATE+N --------- 13-SEP-07

Now that we’re safely into next year we’ll reel it back to January 1st of next year using the trunc function to truncate the date down to the year.

SQL> select trunc(sysdate+numtoyminterval(1, 'YEAR'), 'YEAR') from dual;

TRUNC(SYS --------- 01-JAN-07

To work with the last week of the year we’ll go back 8 days. We need to go back 8 instead of 7 because we’re going to use the next_day function later which only looks after the date it is passed.

SQL> select trunc(sysdate+numtoyminterval(1, 'YEAR'), 'YEAR') - 8 from dual;

TRUNC(SYS --------- 24-DEC-06

Now we use the next_day function to look for the next Saturday after the date we’ve got.

SQL> select next_day(trunc(sysdate+numtoyminterval(1, 'YEAR'), 'YEAR') - 8, 'SATURDAY') from dual;

NEXT_DAY( --------- 30-DEC-06

So we figured out the last Saturday of this year by taking today’s date, adding one year, going back to January 1 of that year, stepping back 8 days from then and looking for the next Saturday.

Hope this helps Satya! Thanks for the great question.

oracle, dates, database, sql, dba, dbms, database development, database programming

Database Connection Strings

Here’s a cool site to look up those pesky connection strings when you’re coding. ConnectionStrings.com has a fairly extensive list of connection strings for all sorts of data sources and programming languages.

It doesn’t have everything, but there’s an email address if you want to submit more connection strings to the site. Why didn’t I think of this!

Thanks to Tom for sending this to me.

database, dba, database administration, database development, development, oracle, mysql, sqlserver, odbc, access, sql server

Oracle’s Dual Table

On my article about auto increment columns in Oracle Stephane asked about the use of dual in Oracle.

Dual is sort-of a dummy table. It’s a real table, but not one that should ever get updated. It exists in every Oracle database and is useful for troubleshooting and development.

If you describe dual you will see it’s definition:

SQL> desc dual; Name Null? Type ----------------------------------------- -------- ---------------------------- DUMMY VARCHAR2(1)

Dual has only one row, but you can select the value of it.

SQL> select * from dual;

D - X

Dual can be selected by any user. Since it contains just the one row you can use it to return a single result to you, like

SQL> select sysdate from dual;

SYSDATE --------- 11-AUG-06

Now you could have done this against any single-row table. You could even create a single row table to select results like this, but since Oracle provides the dual table everyone tends to use it.

Thanks for the question Stephane. I think many people use Dual without knowing (or even wondering) what it is.

oracle, database administration, dba, database development