Resumable Transactions in Oracle

OracleOracleAndy, a relative newcommer to the Oracle blogging community, has a fantastic article on resumable transactions.

Here’s what Andy has to say about them:

For those of you who have not come across this feature of Oracle, it was introduced in 9iR2. It allows you to set up your session so that if a transaction hits a storage problem e.g. out of temp, tablespace is not big enough,or a table reaches max extents, the transaction is suspended. All you have to do is fix the space problem and and transaction will resume. This saves you all the time of waiting for the rollback, adding some space and starting again hoping you’ve got it right this time.

He goes on to explain in great deal how to setup for and use resumable transactions. I didn’t even know you could do this (damn lack of training budget.) Great article Andy!

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

How to Create Auto Increment Columns in Oracle

OracleAfter pointing out how not to create auto increment columns in Oracle, I suppose I should point out how to create auto increment columns in oracle.

Many will gripe about this not being a standard feature in Oracle, but when it’s as easy as two more commands after your CREATE TABLE command I can’t see any good reason to use fancy SQL on every insert.

First let’s create a simple table to play with.

name VARCHAR2(30));

Table created.

Now we’ll assume we want ID to be an auto increment field. First we need a sequence to grab values from.

SQL> CREATE SEQUENCE test_sequence

Sequence created.

Now we can use that sequence in an BEFORE INSERT trigger on the table.

ON test
SELECT test_sequence.nextval INTO :NEW.ID FROM dual;

Trigger created.

This trigger will automatically grab the next value from the sequence we just created and substitute it into the ID column before the insert is completed.

Now we’ll do some inserts:

SQL> INSERT INTO test (name) VALUES ('Jon');

1 row created.

SQL> INSERT INTO test (name) VALUES ('Bork');

1 row created.

SQL> INSERT INTO test (name) VALUES ('Matt');

1 row created.


---------- ------------------------------
1 Jon
2 Bork
3 Matt

Because the sequence is updated independent of the rows being committed there will be no conflict if multiple users are inserting into the same table simultaneously.

If you need to capture the value of the auto increment column you’ll want to check out my other article Oracle Auto increment Columns – Part 2

Matt has posted a similar method in which he uses a sequence in the insert eliminating the need for the trigger. That will work just as well without the need for the trigger. The only drawback to this method is slightly longer SQL commands.

How Not to Create Auto Increment Columns in Oracle

This article is about how not to create auto increment columns. If you’re looking for a more acceptable way, you’ll want to check out this article.

Matt has posted some code on his blog for accomplishing an auto increment in Oracle. While his solution may seem like an easy way out of creating triggers and sequences, it will fall apart in practical use.

Here’s what Matt suggested:

INSERT INTO table (id,name) (SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END, ‘bork’ FROM table);

Basically he is taking the max value currently in the ID column of the table and adding one for the new ID value. Matt mentions on his site that he is not sure how efficient this would be. While it wouldn’t be very efficient, it will probably not be too slow if the ID column has an index on it (which it will automatically have if it is a primary key.)

The bigger problem will come when you have multiple users connected. To illustrate that we’ll create a small test table:

name VARCHAR2(30));
Table created.

Now let’s insert a couple rows in the table:

SQL> INSERT INTO test (id,name) (SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END, 'bork' FROM test);1 row created.

INSERT INTO test (id,name) (SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END, 'Jon' FROM test);

1 row created.

SQL> select * from test;

---------- ------------------------------
1 bork
2 Jon

OK, so far so good, the incrementing works. Now let’s open a second session to the database and insert a row.

Session 2
SQL> INSERT INTO test (id,name) (SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END, 'Matt' FROM test);

This session hangs! If you dig into the data dictionary (or Enterprise Manager) you will find that the first session has a lock on the table that is blocking the second transaction! So session 2 is waiting for session 1 to commit. Let’s do that and see what happens.

Session 1
SQL> commit;Commit complete.

Session 2
INSERT INTO test (id,name) (SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END, 'Matt' FROM test)
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C002948) violated

Now we see the bigger problem. Since the subquery is evaluated before session 1 commits the subquery which is incrementing the ID value based on the MAX(id) is grabbing old data.

So sorry Matt, there’s no bulletproof way around using triggers and sequences for this.

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

More Job Scheduling Examples with DBMS_JOB

OracleBurleson Consulting has some good examples of complex job scheduling.

Of particular interest is this example which causes a job to be run only Monday through Friday:

-- ----------------------------------------------------------------
-- Submit job to begin at 0600 and run every hour, Monday - Friday
-- ----------------------------------------------------------------
next_day(SYSDATE - 23/24,''MONDAY''),
next_day(SYSDATE - 23/24,''TUESDAY''),
next_day(SYSDATE - 23/24,''WEDNESDAY''),
next_day(SYSDATE - 23/24,''THURSDAY''),
next_day(SYSDATE - 23/24,''FRIDAY'')

If you’re not familiar with Burleson Consulting’s site you probably should be. They have some great articles on Oracle administration. They even have linked to Life After Coffee recently on this same topic.

If you’re looking for more general information on the DBMS_JOB package, check out my article Scheduling Jobs using Oracle’s Job Queue.

database, database administration, database development, oracle

SQL: Find the Week Number in a Month

OracleAjama commented on my article Oracle Question and Answer Site asking how to write a SQL function to return the calendar week in a month.

Basically if your week starts on a Sunday, anything leading up to the first Sunday would be week 1, then the first complete week, Sunday through Saturday would be week 2, then week 3, etc. If the month happens to start on a Sunday week 1 would be a complete week.

If you are unfamiliar with manipulating dates in Oracle, you may want to check out my other articles on Oracle, SQL, Dates and Timestamps and Performing Math on Oracle Dates.

Starting simple I worked out how to find the first day of the month. The TRUNC function will allow you to truncate a date to a certain precision, so I started by truncating the date to the month. Today is February 1, so since we’re using sysdate for some testing we coincidentally get back February 1.

SQL> select trunc(sysdate, 'MM') from dual;


Now we have a starting point. From here I used the NEXT_DAY function to find the beginning of the first week of the month. This is found by looking at the first of the month and the 6 days leading up to it. Since next_day excludes the day it is calculating from we subtract 7 for the start date.

SQL > select next_day(trunc(sysdate, 'MM') - 7, 'Sunday') from dual;


Now we have established the beginning of the first week in the month. The first week starts at the end of the previous month as it is only partially in February. With this date in mind we can now calculate how many days we are from then:

SQL> select sysdate - next_day(trunc(sysdate, 'MM') - 7, 'Sunday') from dual;


This tells us we are 3.559 days from the beginning of the first week in the month. To express that in weeks we simply divide by 7.

SQL> select (sysdate - next_day(trunc(sysdate, 'MM') - 7, 'Sunday'))/7 from dual;


This tells us that we are about .5 weeks from that first Sunday. We don’t care about the fractional part so we can use the other form of the TRUNC function to to truncate this to a whole number. That would put us in the 0th week of the month, but since we want to start counting at 1 we add 1 to the number.

SQL> select trunc(((sysdate - next_day(trunc(sysdate, 'MM') - 7, 'Sunday'))/7),0) + 1
from dual;


This tells us accurately that right now we’re in the first week of the month. Now we want to try this logic out with some other dates. It’s easiest to do that if we just create a function for it.

(check_date DATE DEFAULT sysdate, week_start CHAR DEFAULT 'Sunday')
RETURN number
week_number NUMBER;
select trunc(((check_date - next_day(trunc(check_date, 'MM') - 7, week_start))/7),0) + 1 into week_number from dual;
RETURN week_number;

Here I’ve created a function week_in_month to execute this SQL with two parameters. The first, check_date can be any Oracle date. The second parameter, week_start is the day that your week starts. This must be spelled out, like ‘Sunday’, ‘Monday’, etc. The function will return the week number from start of month.

If the check_date is not specified, the current date and time will be used. If the week_start is not specified we’ll default to Sunday.

Now let’s try our function without parameters.

SQL> select week_in_month from dual;


The function correctly identifies that we’re in week 1 of the month by our original definition. Now we’ll look at a few other examples. Remember, where we have not specified a starting day our function will assume Sunday.

SQL> select week_in_month(to_date('01/29/06','MM/DD/YY')) from dual;


Here we see that 1/29/06 was in the 5th week of January.

SQL> select week_in_month(to_date('02/06/06','MM/DD/YY')) from dual;


This shows that February 6 will be in the second week in February.

SQL> select week_in_month(to_date('02/06/06','MM/DD/YY'), 'Wednesday') from dual;


Here we have specified that our week starts on Wednesday, and based on that February 6th will be in the first week in February.

SQL> select week_in_month(to_date('04/30/06','MM/DD/YY')) from dual;


This is a somewhat rare instance, and it may look like there’s a problem with the logic, but because of how April of 2006 falls, the 30th would actually be in the 6th week of the month based on our criteria.

oracle, sql, dba, database administration, database development