April 2009

Note added 3 April 2009: When I wrote this post, we were counting down toward 2 April as the date for our preliminary go/no-go decision. That date is now behind us, and we have made the preliminary decision to Go. We are accepting further enrollments. —Cary Millsap

Thursday 2 April 2009 is our last call for enrollment in C. J. Date's course, "How to write correct SQL, and know it: a relational approach to SQL." I'm looking forward to this course more eagerly than anything I've attended in the past ten years, ...maybe twenty.

SQL and I never really got along too well. When I first joined Oracle Corporation in 1989, I was new to relational databases. I had done one hierarchical database project in college. I enjoyed the project ok, but it wasn't something I ever wanted to do again. When I joined Oracle, I didn't know much about relational technology or SQL. In my formative first couple of years at Oracle, though, I just never learned to like the SQL language. Prior to my Oracle career, I designed languages and wrote compilers for a living. From a language design standpoint, it just seemed that SQL (at least "Oracle SQL") could have become something really cool, but it didn't. For Oracle to treat an empty string as NULL, for example, is a decision which I still can't believe made it into the light of day...

I had a lot of respect over the years for the people I met who knew how to make SQL do what they wanted it to do. Dominic Delmolino was one of the first people I ever met who could make SQL do things I had no idea it could do. I'm still amazed when I see the things that Tom Kyte can do with SQL. I was never one of the SQL people.

Following this posting it was put to me that you could get Application Designer to build scripts with the commands to add the privilege by adding a second command to the create table DDL model, like this:

CREATE TABLE [TBNAME] ([TBCOLLIST]) TABLESPACE [TBSPCNAME] STORAGE (INITIAL **INIT** NEXT **NEXT** MAXEXTENTS **MAXEXT** PCTINCREASE **PCT**) PCTFREE **PCTFREE** PCTUSED **PCTUSED**;
GRANT SELECT ON [TBNAME] TO psreadall;

Yes, this does work when creating the table. The additional command is put into the create table script generated by Application Designer

DROP TABLE PS_PERSON
/
CREATE TABLE PS_PERSON (EMPLID VARCHAR2(11) NOT NULL,
BIRTHDATE DATE,
BIRTHPLACE VARCHAR2(30) NOT NULL,
BIRTHCOUNTRY VARCHAR2(3) NOT NULL,
BIRTHSTATE VARCHAR2(6) NOT NULL,
DT_OF_DEATH DATE,
LAST_CHILD_UPDDTM DATE) TABLESPACE HRLARGE STORAGE (INITIAL 40000
NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80
/

GRANT SELECT ON PS_PERSON TO PSREADALL
/

However, the second command does not appear in the alter script.