The Rule of the Lazy Class

Approach.Botonomy.com has an interesting article on Why the Puritan Work Ethic has No Place in IT.

The article echoes many of my personal sentiments that your administrators and developers need to be encouraged to work smarter, not harder, even to the point of having “free time.”

… You want an environment where sysadmins kick back and read IT magazines occasionally, because their run-of-the-mill administrative tasks (adding users, managing disk space, etc.) are all scripted and/or automated. They can then focus their energies on the unexpected and unavoidable issues that crop up from time-to-time.

Beyond handling the unexpected, through having this “free time” administrators will have the ability to identify areas in need of improvement. If your administrators are running around fixing stuff all the time your team has a problem! Not only will morale, and therefore retention suffer but your administrators will have no time for evaluating new opportunities.

The best teams celebrate those who sit back and let their computers do their work for them. You want to have a project team that considers repetitive development activities to be tasteless. Sometimes necessary, but generally frowned upon.

Check out the full article and think a bit about what your team could be doing if they weren’t fighting fires all the time.

Of course this is exactly why I am writing a book on Oracle Shell Scripting due out next year.

project management, it management, information technology, database administration, system administration

Database Connection Strings

ConnectionStrings.comHere’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

Free Oracle 10g Data Dictionary Poster

Burleson Consulting is offering this free poster of the Oracle 10g data dictionary views

10g Data Dictionary Poster


This is a nice compliment to the Tusc V$Views poster. You pay $5.99 for shipping, but believe me, the first time you use this in a pinch to find the name of one of those pesky dictionary tables it will have paid itself off.

Get yours now from Burleson Consulting and while you’re there check out Burleson Consulting’s Daily Oracle News

oracle, data dictionary, database administration, dba, dbms

Oracle Auto Increment Columns – Part 2

Three separate people have commented on my previous article on How to Create Auto Increment Columns in Oracle asking how they can retrieve the value of an auto increment column for use later in their code. Well Daniel, Shaun and Zach, here’s the answer.

After you have referenced sequence.NEXTVAL for a particular sequence (or it is referenced on your behalf by, say, a trigger), you can then reference sequence.CURRVAL to get the value just used for NEXTVAL.

To illustrate this we’ll use the table, sequence, and trigger created in my previous article.

If we insert a row into the table test, the trigger test_trigger automatically calls test_sequence.NEXTVAL.

SQL> insert into test (name) values ('Matt');

1 row created.

We now have test_sequence.CURRVAL available in that session.

SQL> select test_sequence.currval from dual;

CURRVAL
----------
8

In this simple example we can confirm this is the same value just used with this simple query:

SQL> select * from test
where name='Matt';

ID NAME
---------- ------------------------------
8 Matt

Now if we wanted to use this value in another SQL statement, say for an insert on a table which uses this as a foreign key constraint, we can include it on our insert like this:

SQL> insert into tool (owner_id, tool)
values (test_sequence.CURRVAL, 'hammer');

1 row created.

SQL> select * from tool;

OWNER_ID TOOL
---------- ------------------------------
8 hammer

Of course, if we just want to see the value of test_sequence.CURRVAL we can select it from our favorite table dual.

SQL> select test_sequence.currval from dual;

CURRVAL
----------
8

For the table, sequence and trigger used here see my original article on auto increment fields.