The fine line between clever and stupid

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.