Tag Archives: Insert script

Lazy Insert

Posted on by
Reply

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.

Advertisements