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.

SQL> CREATE TABLE test
(id NUMBER PRIMARY KEY,
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
START WITH 1
INCREMENT BY 1;

Sequence created.


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

CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT
ON test
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT test_sequence.nextval INTO :NEW.ID FROM dual;
END;
/

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.

SQL> SELECT * FROM test;

ID NAME
---------- ------------------------------
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:

SQL> CREATE TABLE test
(id NUMBER PRIMARY KEY,
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;

ID NAME
---------- ------------------------------
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
-- ----------------------------------------------------------------
dbms_job.submit(
:jobno,
'statspack.snap;',
trunc(sysdate+1)+6/24,
'trunc(
least(
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'')
)
,''HH'')',
TRUE,
:instno);
commit;
end;
/

If you’re not familiar with Burleson Consulting’s site dba-oracle.com 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

Blogs.Oracle.com

OracleOracle has taken an interesting step, and I hope others will follow.

Blogs.Oracle.com seems to have been created as an Oracle blogroll! Why is this important? Well, for one thing it means Oracle is encouraging their employees to blog, but additionally they are also linking to non-employee blogs.

Here’s the introduction from Blogs.Oracle.com:

Welcome to the Oracle blogging community, where Oracle executives, employees, and non-employees alike exchange views about best practices for using Oracle and industry-standard technologies. This continuous feedback loop helps Oracle stay in touch with the needs of the overall community, so keep those comments coming!

This not only validates what others have been saying regarding the value of blogging in the corporate environment, but also recognizes the role that non-employees play in the big picture.

Check it out at Blogs.Oracle.com. Right now there are a couple dozen employee blogs and fourty-something non-employee blogs. Hopefully they’ll stay on top of adding new folks as they come along.

blogging, information technology, internet, technology, web, web 2.0, web office, blog, database, database administration, database programming, dba, pl/sql, plsql, sql, oracle

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;

TO_CHAR(TRUNC(SYSDA
-------------------
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.

SQL> BEGIN
DBMS_JOB.CHANGE(JOB => 21, WHAT => NULL, NEXT_DATE => NULL, INTERVAL => 'TRUNC(SYSDATE + 1) + 23/24';
END;
/

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