The fine line between clever and stupid ....oracle

Stojka Mongo began to be concerrned when he came across table AT_ILOAD_DAYS that looked like this:

CREATE TABLE at_iload_days
( daystring      VARCHAR2(8)
, batch_created  VARCHAR2(1)  DEFAULT 'N' );

where "daystring" contained values like "20030901".

Then he found this convenient procedure to "fill" it:

CREATE OR REPLACE PROCEDURE pr_generate_iload_days
AS
   -- Procedure to fill days from most recent entry in at_iload_days up to current sysdate */
   d_date_string    VARCHAR2(8) DEFAULT NULL;
   d_maxdate_string VARCHAR2(8) DEFAULT NULL;
BEGIN
   -- sysdate will be max date to generate:
   d_maxdate_string := TO_CHAR(SYSDATE, 'yyyymmdd');

   -- Fetch most recent datestring from at_iload_days + 1 day into d_date_string
   -- (default to commercial launch date)
   EXECUTE IMMEDIATE
   'select to_char((to_date(nvl(max(daystring),''20030505''),''yyyymmdd'')+1),''yyyymmdd'') from at_iload_days'
   INTO d_date_string;

   -- We shouldn't load data that will exist in the future, should we?
   IF d_date_string <= d_maxdate_string THEN
       EXECUTE IMMEDIATE
       'insert into at_iload_days (daystring,batch_created) values (' ||
       '''' ||d_date_string || '''' || ',''N'')';

       EXECUTE IMMEDIATE 'COMMIT';
   END IF;
END pr_generate_iload_days;

Our favourite line:

EXECUTE IMMEDIATE 'COMMIT';

It's hard to tell but I think that procedure can be rewritten as:

CREATE OR REPLACE PROCEDURE pr_generate_iload_days
AS
BEGIN
   INSERT INTO at_iload_days (daystring)
   SELECT TO_CHAR
          ( TO_DATE(NVL(MAX(daystring),'20030505'),'yyyymmdd')+1
          , 'yyyymmdd' )
   FROM   at_iload_days
   HAVING NVL(MAX(daystring),'20030505') <= TO_CHAR(SYSDATE, 'yyyymmdd')
END pr_generate_iload_days;

although if DAYSTRING had actually been a date, it would have just been:

CREATE OR REPLACE PROCEDURE pr_generate_iload_days
AS
BEGIN
   INSERT INTO at_iload_days(day)
   SELECT NVL(MAX(day), DATE '2003-05-05') +1
   FROM   at_iload_days
   HAVING NVL(MAX(day), DATE '2003-05-05') <= SYSDATE;
END pr_generate_iload_days;

Thanks 3360 for the title for this post.