What’s in a name?!

I make no secret of the point that I love the webcomic xkcd and if I blogged every strip I like I would basically end up mirroring the entire comic here.

With that in mind, there is no way I could pass up posting this commic:

xkcd - Exploits of a Mom


Next time someone asks you what a SQL injection is you can point them at this, then explain nicely.

sql, oracle, pl/sql, plsql, exploit, security, sql exploit, dba, database, database administration, comic, fun, funny, sql injection

Getting dates into the format you want in Oracle

I’m always amazed how much traffic some of my articles on the Oracle date datatype get, but dealing with dates in SQL can be as daunting for beginners as it is tedious for the experts.

Well, here’s another resource to help you sort out those pesky dates. This one focuses on the to_char function and how it can be used to get dates to output in the format you want.

This article from Don Burleson offers a couple handy examples of the to_char function, but more importantly has a table listing the options for output of the date format.

For more reading on the Oracle date format check out my other Oracle stories.

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

A different kind of programming contest

Nobody will argue that testing your code is an essential, but often neglected step to good development. Effective testing not of the whole application, but portions of it is the focus of the Oracle Development Tools User Group PL/SQL Test-A-Thon to be held Febuary, 28-March, 1 of this year in California.

Here’s how the challenge works:
After the end of sessions on the first day, you will be presented with four programs that perform typical operations—nothing exotic. Along with those programs come supporting test data, a list of tests that you need to perform, and the results you should get for each test (most will be successful, but some will fail). You will then have one hour to write a test to show which tests succeed and which fail for the programs. Your test results should be self-verifying. That is, we will not manually verify your tests to see if they worked or not.

Check out more about the contest and about the Oracle Development Tools User Group conference. While not overly active the Oracle Development Tools User Group site has some interesting content as well.

sql, plsql, oracle, development, software development, database, dba

Finding the first or second Monday in a month

Syam asked a while ago how we could find something like the first Monday or third Saturday in a month. Well Syam, it’s taken me a while to respond but here we go.

For this example we’ll use sysdate as input but any Oracle date will work. You can also substitute any other day of the week for Monday.

The first day of the month is probably a good place to start:

SQL> select sysdate from dual;

SYSDATE
---------
18-JUL-06

SQL> select trunc(sysdate, 'MONTH') FROM DUAL;

TRUNC(SYS
---------
01-JUL-06

Now that we’ve got that we can find the first Monday with the next_day function. Of course we need to remember the next_day function looks for the next named day after the date provided so we subtract 1 day from the date in case the first is a Monday.

SQL> select next_day(trunc(sysdate, 'MONTH')-1, 'Monday') from dual;

NEXT_DAY(
---------
03-JUL-06

Now that we have the first Monday of the month we can add 7 days to find the second Monday or 14 to find the third.

SQL> select next_day(trunc(sysdate, 'MONTH')-1, 'Monday')+7 FROM dual;

NEXT_DAY(
---------
10-JUL-06

SQL> select next_day(trunc(sysdate, 'MONTH')-1, 'Monday')+14 FROM dual;

NEXT_DAY(
---------
17-JUL-06

So from here you can change the day you’re looking for or the week number you want it in.

oracle, date functions, sql, database, database development, pl/sql

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.