Wednesday, May 4, 2011

The &DBA

Pronounced, Ampersand DBA.

I'm hearby naming all my future DBAs that meet certain criteria, &DBA.

Over the years, at about a million different organizations, I have met many DBAs. Many, at first glance, fit the DBA stereotype; cranky, condescending, arrogant, etc. After further review, many of those traits still apply. Some however turn out to be the opposite; they just put on a game face for the new guy or gal because they've been through this before...probably to be disappointed again that the developer will 1, leave them out of design discussions 2, throw bad SQL over the wall, 3, not use bind variables, ever and 4, well, all the other bad things we developers do.

The type that I have least encountered are the DBAs who actively seek out and engage developers. Hey, can I help you with that? Hey, you aren't using bind variables, here are the reasons you should. They know that in the long run, the better the developer, the easier their job will be.

I love that aspect of my job. I like helping others learn to do something better or more efficiently. I like to encourage others to download and install the database. But that's not the point of this post...

I needed a name for those DBAs who are 1, less qualified than I am to be a DBA (short list, I am under no illusions), 2, are wholly incompetent and 3, are jerks.

After a recent incident, as relayed by others, the &DBA was born.

What does the & mean?

In SQL*Plus, the & is a special character. If you run scripts you can parameterize them with &COLUMN_VALUE or something, right?

That's great for scripts.

It can go horribly wrong with code. At least the first time you try to load code via SQL*Plus. Here's the original
CREATE OR REPLACE
FUNCTION foo RETURN VARCHAR2
AS
l_retval VARCHAR2(10);
l_value VARCHAR2(30) := 'Barnes & Noble';
BEGIN
SELECT 'IT WORKS!'
INTO l_retval
FROM dual
WHERE 'Barnes & Noble' = l_value;

RETURN l_retval;

END foo;
/
show errors
and here's what happens when I run it:
CJUSTICE@NO_CLUE>CREATE OR REPLACE
FUNCTION foo RETURN VARCHAR2
2 AS
3 l_retval VARCHAR2(10);
4 l_value VARCHAR2(30) := 'Barnes & Noble';
5 BEGIN
6 SELECT 'IT WORKS!'
7 INTO l_retval
8 FROM dual
9 WHERE 'Barnes & Noble' = l_value;
10
11 RETURN l_retval;
12
13 END foo;
14 /
Enter value for noble: show errors
old 3: l_value VARCHAR2(30) := 'Barnes & Noble';
new 3: l_value VARCHAR2(30) := 'Barnes show errors';
Enter value for noble: testing
old 8: WHERE 'Barnes & Noble' = l_value;
new 8: WHERE 'Barnes testing' = l_value;

Warning: Function created with compilation errors.
This is how it is stored in the database:
FUNCTION foo RETURN VARCHAR2
AS
l_retval VARCHAR2(10);
l_value VARCHAR2(30) := 'Barnes show errors';
BEGIN
SELECT 'IT WORKS!'
INTO l_retval
FROM dual
WHERE 'Barnes testing' = l_value;

RETURN l_retval;

END foo;

12 rows selected.
Yeah, that won't work.

To fix this, you simply set an attribute/directive? in your SQL*Plus session, SET DEFINE OFF (I believe there is another, but this is the one I use). Like this:
CJUSTICE@NO_CLUE>SET DEFINE OFF
CJUSTICE@NO_CLUE>
CJUSTICE@NO_CLUE>CREATE OR REPLACE
2 FUNCTION foo RETURN VARCHAR2
3 AS
4 l_retval VARCHAR2(10);
5 l_value VARCHAR2(30) := 'Barnes & Noble';
6 BEGIN
7 SELECT 'IT WORKS!'
8 INTO l_retval
9 FROM dual
10 WHERE 'Barnes & Noble' = l_value;
11
12 RETURN l_retval;
13
14 END foo;
15 /

Function created.

Elapsed: 00:00:00.22
CJUSTICE@NO_CLUE>show errors
No errors.
CJUSTICE@NO_CLUE>
CJUSTICE@NO_CLUE>SET DEFINE ON
All is now well in the world.

Now this might be vexing on your first couple of weeks on the job, but you quickly pick it up.

This is where the name comes from...

SET DEFINE OFF
&DBA
SET DEFINE ON


said that this was a known Oracle bug.

Really.

5 comments:

Sam K. said...

Hilarious!!!! Right on ! Awesome! What can I say more? YOU ROCK!&;

Anonymous said...

There is no chance on earth that any DBA called the ampersand an oracle bug. Not a chance. I have met developers and DBAs that on DAY 1 learned the ampersand...is this real?
T.J.

oraclenerd said...

@TJ

Very real. Someday I might be able to provide the hard evidence.

Almost the entire world was on the email as well, essentially telling the dev that they were dumb. Ooops.

SydOracle said...

I've had similar issues with developers who rely on TOAD.

On a similar note, before any of their PL/SQL module scripts when to the DBAs to run, I'd have to add in the slashes at the end so that SQLPLUS would actually execute the statement.

For a while I tried having SET DEFINE OFF in my login.sql, but it didn't help when scripts went out of my hands, so I went back to including it explicitly everywhere.

I would prefer that as the default, but it would be a massive change.

oraclenerd said...

@gary

I've had to do that too, add the slashes. I've had heated arguments about it as well. Optionally throw in "show errors."

It was beat into me early on, create a build script and run it from SQL*Plus. I don't think I have ever deployed code through another GUI.