Lazy Insert

Do you ever get that annoying colleague quoting Kyte at you … “if you can do it SQL then do it in SQL” ?
One of the more tedious aspects of development is those repetitive insert scripts – usually for adding reference data.
Consider a table like this :-

CREATE TABLE my_ref_data_tab(
   id NUMBER(3),
   ref_val VARCHAR2(30),
   active_ind VARCHAR2(1),
   created_by VARCHAR2(30) NOT NULL,
   creation_date DATE NOT NULL,
   modified_by VARCHAR2(30),
   modified_date DATE,
   CONSTRAINT mrdt_pk PRIMARY KEY ( id),
   CONSTRAINT mrdt_uk UNIQUE( ref_val))
/    

The synthetic key is populated from a sequence.

Now, I need to create 5 records in this table ( it could be any number, but I got bored typing insert statements).
Usually, I’d have to do something like :-

spool myinsert.log
INSERT INTO my_ref_data_tab( id, ref_val, active_ind, created_by, creation_date)
VALUES( mrdt_id.NEXTVAL, 'MIKE', 'Y', USER, SYSDATE)
/
INSERT INTO my_ref_data_tab( id, ref_val, active_ind, created_by, creation_date)
VALUES( mrdt_id.NEXTVAL, 'IS', 'Y', USER, SYSDATE)
/
INSERT INTO my_ref_data_tab( id, ref_val, active_ind, created_by, creation_date)
VALUES( mrdt_id.NEXTVAL, 'A', 'Y', USER, SYSDATE)
/
INSERT INTO my_ref_data_tab( id, ref_val, active_ind, created_by, creation_date)
VALUES( mrdt_id.NEXTVAL, 'LAZY', 'Y', USER, SYSDATE)
/
INSERT INTO my_ref_data_tab( id, ref_val, active_ind, created_by, creation_date)
VALUES( mrdt_id.NEXTVAL, 'SOD', 'Y', USER, SYSDATE)
/
spool off

Loads of unnecessary typing, lots of cut-and-paste…and more often than not, lots of debugging when you realise you’ve made a typo, or cut and paste an insert without changing the value you’re inserting. And what happens when someone decides to change the structure of the table ?

All in all, life is far too short. So….

set serveroutput on size 1000000
spool lazy.log
DECLARE
   l_message VARCHAR2(15) := '1 row inserted.';        
   PROCEDURE ins( p_ref_val VARCHAR2) IS
      BEGIN
         INSERT INTO my_ref_data_tab( id, ref_val, active_ind, 
            created_by, creation_date)
         VALUES( mrdt_id.NEXTVAL, p_ref_val, 'Y', 
            USER, SYSDATE);
      END ins;
BEGIN
   ins( 'MIKE');
   DBMS_OUTPUT.PUT_LINE(l_message);
   ins('IS');
   DBMS_OUTPUT.PUT_LINE(l_message);
   ins('A');
   DBMS_OUTPUT.PUT_LINE(l_message);
   ins('LAZY');
   DBMS_OUTPUT.PUT_LINE(l_message);
   ins('SOD');
   DBMS_OUTPUT.PUT_LINE(l_message);
END;
/
spool off

Note there’s no exception handling here – if anything fails, you’ll want to rollback the lot and figure out what’s happening. On the plus side however, you’ve only got one insert statement to puzzle over rather than five.