Born Before Computers – fogey foibles and forsight for the humble insert

Apparently, I am considered by some to be stuck in my ways.
For example, Deb now refuses to stand with me in the supermarket queue because of my tendency to argue with the automated checkout.

This even extends into my working life where I have a colleague who is a bit more hip and with it when it comes to writing code.
The rest of us in the Oracle team – slightly older than this person it must be said – are apparently “BBC”.
This isn’t some reference to the venerable British Broadcasting Corporation, nor even to the BBC micro which was popular back in the 80′s.
Evidently, it is something of a disadvantage to have been “Born Before Computers”.
I’ll confess, I do write most of my database code in a text editior and run it via SQL*PLUS. Whilst I use an IDE for looking at stored database code, I’m not that keen on using it as a code environment.
As is usually the case, sometimes things are done in a certain way for years because that’s the best way to do it…and sometimes it’s simply because “that’s how we’ve always done it”.
The trick is, knowing which is which.

All of which brings us to the INSERT statement.

Test Case

Time for a table ….

CREATE TABLE programmer_types(
	type_name VARCHAR2(50),
	description VARCHAR2(100))
/

Simple table, simple insert statement ( minimising the utilisation of valuable typing molecules) …

INSERT INTO programmer_types 
VALUES( 'COOL KID', 'IDE is King, or possibly Queen depending')
/

No problem with that, it works, it does what it’s supposed to and is less typing than the more traditional…

INSERT INTO programmer_types( type_name, description)
VALUES( 'DINOSAUR', 'I remember when we used to use punch cards')
/

Just in case you think I’m starting to ramble off the point, let’s try putting our insert statement into a different context …

Stored Code

CREATE OR REPLACE PROCEDURE add_programmer_types_pr( 
	i_type_name IN programmer_types.type_name%TYPE,
	i_description IN programmer_types.description%TYPE) IS
BEGIN
	INSERT INTO programmer_types VALUES( i_type_name, i_description);
END;
/

So…

BEGIN
	add_programmer_types_pr(
		i_type_name => 'DBA',
		i_description => 'Keep your filthy code off my Database !');
END;
/

Yep that works. But supposing we make a change to the table ?

ALTER TABLE programmer_types ADD ( smug_mode VARCHAR2(1));

And now run the procedure again …

BEGIN
	add_programmer_types_pr(
		i_type_name => 'WEB DUDE',
		i_description => 'Relational is sooo old hat');
END;
/

…and we get a nasty surprise :

	add_programmer_types_pr(
	*
ERROR at line 2:
ORA-06550: line 2, column 2:
PLS-00905: object MIKE.ADD_PROGRAMMER_TYPES_PR is invalid
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored


SQL> 

Less common but even more fun, is the situation where the column order for a table changes :

CREATE TABLE programmer_types_tmp AS
    SELECT description, type_name
    FROM programmer_types
/

DROP TABLE programmer_types
/

CREATE TABLE programmer_types AS
    SELECT * FROM programmer_types_tmp
/

DROP TABLE programmer_types_tmp
/

Just to make sure that this has swapped the columns around :

SQL> select column_name, column_id
  2  from user_tab_cols
  3  where table_name = 'PROGRAMMER_TYPES';

COLUMN_NAME			COLUMN_ID
------------------------------ ----------
DESCRIPTION				1
TYPE_NAME				2

SQL> 

So now, if we run our procedure …

BEGIN
	add_programmer_types_pr(
		i_type_name => 'WEB DUDE',
		i_description => 'Relational is sooo old hat');
END;
/

PL/SQL procedure successfully completed.

SQL> 

Well, it has worked…but what has it actually done :

SQL> SELECT type_name, description
  2  FROM programmer_types;

TYPE_NAME	     DESCRIPTION
-------------------- ----------------------------------------
COOL KID	     IDE is King, or possibly Queen depending
DINOSAUR	     I remember when we used to use punch car
		     ds

DBA		     Keep your filthy code off my Database !
Relational is sooo o WEB DUDE
ld hat


SQL> 

Unsurprisingly, it hasn’t picked up the change in the column order so we now have data in the wrong columns. This is otherwise known as a great big mess. Especially if you don’t spot it for a while and then have to unpick it all.

Now, supposing we’d written the procedure like this :

CREATE OR REPLACE PROCEDURE add_programmer_types_pr( 
	i_type_name programmer_types.type_name%TYPE,
	i_description programmer_types.description%TYPE) IS
BEGIN
	INSERT INTO programmer_types(type_name, description)
	VALUES( i_type_name, i_description);
END;
/

If we execute this after the column order has been changed…

BEGIN
	add_programmer_types_pr(
		i_type_name => 'WEB DUDE',
		i_description => 'Relational is sooo old hat');
END;
/

PL/SQL procedure successfully completed.

…and do the same check :

SQL> SELECT type_name, description
  2  FROM programmer_types;

TYPE_NAME	     DESCRIPTION
-------------------- ----------------------------------------
COOL KID	     IDE is King, or possibly Queen depending
DINOSAUR	     I remember when we used to use punch car
		     ds

DBA		     Keep your filthy code off my Database !
WEB DUDE	     Relational is sooo old hat

SQL> 

Yes, because the target columns are specified in the insert statement, the correct values are inserted into the appropriate columns.

If we run it after the column has been added …

BEGIN
	add_programmer_types_pr(
		i_type_name => 'WEB DUDE',
		i_description => 'Relational databases are sooo old hat');
END;
/

anonymous block completed

The code is undoubtedly more robust in terms of not breaking when we change the structure of the underlying table, but is that really ALWAYS what we want ?
Whilst this procedure will not fail after adding the column, it will NEVER insert a value into the new column.

This is just one of the reasons that doing an impact analysis is a pretty good idea before making structural changes to tables.
The simplest way to do this is :

SQL> select owner, name, type
  2  from dba_dependencies
  3  where referenced_owner = 'MIKE'
  4  and referenced_name = 'PROGRAMMER_TYPES'
  5  and referenced_type = 'TABLE';

OWNER			       NAME			      TYPE
------------------------------ ------------------------------ ------------------
MIKE			       ADD_PROGRAMMER_TYPES_PR	      PROCEDURE

SQL> 

This will at least give you a starting point for identifying any possible issues. If you want a more granular view, you can try something like this.

Conclusion

As so often, a little bit of extra effort on the keyboard when writing code can save lots of time and pain down the line. Remember, there’s no reason to suppose that the code you are writing now won’t still be in production years down the line. There’s also the chance that you might have to come back to this code at some point in the future and try and work out exactly what it was you were trying to do.

Anyway, I’m off to skulk around the supermarket again. Deb has refused to let me liven things up by bringing a lump-hammer to be my “Unexpected item in the bagging area.”
Apparently that’s not the done thing.
Honestly, some people are so stuck in their ways !

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s