The SQL Guru

“Really!?” Warren was stunned. “They went with us? What’s wrong with them? Why on earth would they have done that!?”

Warren had a good reason to be skeptical. While his employer, Aderrific, was one of the region’s top advertising agencies, they weren’t exactly known for building Customer Relationship Management systems. Yet, their largest client – a major soft drink company famous for a certain challenge – had retained Aderrific to do just that.

“I guess our price was right,” Warren’s fellow developer said. “Either way, I’m sure we’ll be able to build them something decent. Or, at the very least, something decent on the ‘first-ever-attempt-at-anything-that-resembles-CRM’ scale.”

Though Warren and his colleague had raised concerns about the project long ago, they were largely ignored by management. After all, Aderrific wasn’t in the business of turning down business, especially when that business paid very well. Their programming team of two would just have to deal… and build a working CRM system, of course.

Building it Out

Fortunately, Warren did have a fair amount of experience building database-backed applications and had worked with enough clients to feel comfortable gathering software requirements. He also knew that their existing tools – Microsoft Access for simple internal applications and Perl/MySQL for client websites – probably weren’t the best fit. Java, having just celebrated its fifth anniversary, seemed to be the right choice for application development, while Oracle (which their customer already had) was the logical choice for the database.

After feverously trying to climb as many learning curves as possible, Warren and his coworker spent the next few months designing, developing, and implementing the CRM application. One decision they made early on was that, above all else, data integrity was critical. No matter how many bugs or oversights made it into the application, so long as the database was chock-full of constraints, their data would be valid.

One of the key tables in the CRM database was the users table (named “usr”). As the name implies, it contained all the relevant information for a user of the CRM system. The most important column in the table was the user’s email address, as that was used as a login and served as the only method for contacted users of the system. As such, the column had a UNIQUE constraint defined and a rather sophisticated CHECK constraint to make sure the value at least resembled an email address.

The Launch

When their CRM system eventually went live, it had its share of issues. It was a little slow, a little confusing, and a little shy on features, but it worked well enough. One exceptionally annoying bug, however, was the fact that users just couldn’t seem to understand how to write out their email address. A quick peek in the users table revealed many failed attempts: [email protected], jdoe.www@, [email protected], [email protected]/jdoe.

Fortunately, Warren’s boss had the perfect solution for their launch woes: Aderrific would bring on a SQL Guru to review the design, suggest improvements, and recover the countless invalid email addresses from the data. Sure, it was a little late, but it was definitely better than never.

The SQL Guru Delivers

The good news was that, after the consultant spent a few days analyzing and tweaking the database, no further changes were needed. Amazingly, Warren and his fellow developer had done such a bang-up job developing the CRM system.

The bad news, however, was the application had even more bugs than before. Upon further investigation, Warren found that there were two rows in the user table with the same email address. Worse still, neither of those email addresses were formatted even remotely correct.

Warren figured that the database must have somehow been damaged while the SQL Guru optimized things, as there were two different constraints that would prevent this situation from occurring.

“That’s simply not possible,” the SQL Guru procalaimed when Warren explained some of the bugs the application was having, “I hardly touched the database. It must be your application code.”

Not quite convinced, Warren decided to dig a little further. Since all SQL*Plus sessions were logged, he decided to look there. Following is what he came dug up.

<... snip ...>

SQL> select email from crmadmin.usr

2 change column email [email protected] / [email protected];

change column email [email protected] / [email protected];

       *

ERROR at line 2:

ORA-00933: SQL command not properly ended

SQL> select email from crmadmin.usr

2 change [email protected] / [email protected]

3 ;

change [email protected] / [email protected]

       *

ERROR at line 2:

ORA-00933: SQL command not properly ended

SQL> select email from crmadmin.usr

2 change email [email protected]/[email protected]

3 ;

change email [email protected]/[email protected]

       *

ERROR at line 2:

ORA-00933: SQL command not properly ended

SQL> update crmadmin.usr.email

2 change [email protected]/[email protected];

update crmadmin.usr.email

                   *

ERROR at line 1:

ORA-00971: missing SET keyword

SQL> update crmadmin.usr.email

2 set [email protected]/[email protected];

update crmadmin.usr.email

                   *

ERROR at line 1:

ORA-00971: missing SET keyword

SQL> update crmadmin.usr.email

2 change [email protected] set [email protected];

update crmadmin.usr.email

                   *

ERROR at line 1:

ORA-00971: missing SET keyword

SQL> update crmadmin.usr.email

2 set [email protected] set [email protected];

update crmadmin.usr.email

                   *

ERROR at line 1:

ORA-00971: missing SET keyword

SQL> update crmadmin.usr set email

2 [email protected] set [email protected];

[email protected] set [email protected];

*

ERROR at line 2:

ORA-00927: missing equal sign

SQL> update crmadmin.usr set email

2 [email protected] = [email protected];

[email protected] = [email protected];

*

ERROR at line 2:

ORA-00927: missing equal sign

SQL> update crmadmin.usr set email = [email protected] = [email protected];

update crmadmin.usr set email = [email protected] = [email protected];

                                                    *

ERROR at line 1:

ORA-00927: SQL command not properly ended

SQL> update crmadmin.usr set email = [email protected]/[email protected];

update crmadmin.usr set email = [email protected]/[email protected];

                                                          *

ERROR at line 1:

ORA-02019: connection description for remote database not found

SQL> update crmadmin.usr set email = '[email protected]'/'[email protected]';

update crmadmin.usr set email = '[email protected]'/'[email protected]';

                                *

ERROR at line 1:

ORA-01722: invalid number

SQL> pdate crmadmin.usr set email = '[email protected]' to '[email protected]';

SP2-0734: unknown command beginning "pdate crma..." - rest of line ignored.

SQL> update crmadmin.usr set email = '[email protected]'to '[email protected]';

update crmadmin.usr set email = '[email protected]'to '[email protected]';

                                                     *

ERROR at line 1:

ORA-00933: SQL command not properly ended

SQL> update crmadmin.usr set email '[email protected] = '[email protected]';

ERROR:

ORA-01756: quoted string not properly terminated

SQL> update crmadmin.usr set email =

2 where email = '[email protected];

ERROR:

ORA-01756: quoted string not properly terminated

SQL> update crmadmin.usr set email = [email protected]

2 where email = '[email protected];

ERROR:

ORA-01756: quoted string not properly terminated

SQL> update crmadmin.usr set email = '[email protected]'

2 where email = '[email protected];

ERROR:

ORA-01756: quoted string not properly terminated

SQL> update crmadmin.usr set email = '[email protected]';

update crmadmin.usr set email = '[email protected]';

*

ERROR at line 1:

ORA-00001: unique constraint (CRMADMIN.UN_USR_EMAIL) violated

SQL> l

1* update crmadmin.usr set email = '[email protected]'

SQL> /

update crmadmin.usr set email = '[email protected]';

*

ERROR at line 1:

ORA-00001: unique constraint (CRMADMIN.UN_USR_EMAIL) violated

SQL> l

1* update crmadmin.usr set email = '[email protected]'

SQL> update crmadmin.usr set email = '[email protected]'

2 where email = '[email protected];

ERROR:

ORA-01756: quoted string not properly terminated

SQL> update crmadmin.usr set email = '[email protected]'

2 where email = "[email protected]";

where email = "[email protected]";

              *

ERROR at line 2:

ORA-00904: invalid column name

SQL> l

1 update crmadmin.usr set email = '[email protected]'

2* where email = "[email protected]"

SQL> update crmadmin.usr set email = '[email protected]'

2 where usr.email = "[email protected]";

where usr.email = "[email protected]";

                  *

ERROR at line 2:

ORA-00904: invalid column name

SQL> update crmadmin.usr set email = '[email protected]'

2 where usr.email = '[email protected]';

1 row updated.

<... snip ...>

SQL> update usr set email =

2 'www.%com' where email like 'www.%com';

update usr set email =

*

ERROR at line 1:

ORA-00001: unique constraint (CRMADMIN.UN_USR_EMAIL) violated

SQL> alter table usr

2 disable constraint CRMADMIN.UN_USR_EMAIL;

disable constraint CRMADMIN.UN_USR_EMAIL

                           *

ERROR at line 2:

ORA-00933: SQL command not properly ended

SQL> alter table CRMADMIN.USR

2 disable constraint CRMADMIN.UN_USR_EMAIL;

disable constraint CRMADMIN.UN_USR_EMAIL

                           *

ERROR at line 2:

ORA-00933: SQL command not properly ended

SQL> ALTER TABLE USR

2 disable constraint UN_USR_EMAIL;

Table altered.

SQL> update usr set email =

2 'www.%com' where email like 'www.%com';

update usr set email =

*

ERROR at line 1:

ORA-02290: check constraint (CRMADMIN.CK_USR_EMAIL) violated

SQL> ALTER TABLE USR

2 disable constraint CK_USR_EMAIL;

Table altered.

SQL> update usr set email =

2 'www.%com' where email like 'www.%com';

240 rows updated.

It was only the tip of the iceberg. Somehow, the SQL Guru had taken their CRM system – which did, at one point, rate very high on the first-ever-attempt-at-anything-that-resembles-CRM scale – and brought it near the bottom of that bell curve. Tried as they did, Warren and his fellow develop were never quite able to bring it back.

After that project, Aderrific decided to stick to advertising. And fortunately, they haven’t looked back in the nine years since.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!