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.