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.

Pause for thought

Requirement:

Can I let a procedure wait for a specific time (10 seconds) before inserting values into a table?

One solution (according to a poster on OTN - don't try this at home):

declare
  dStart DATE := SYSDATE;
  nDiff  NUMBER;
begin
  dbms_output.put_line('dStart: '||TO_CHAR(dStart, 'dd.mm.yyyy hh24:mi:ss'));
  LOOP
    nDiff := (SYSDATE - dStart)*86400;
    EXIT WHEN nDiff >= 10;
  END LOOP;
  dbms_output.put_line('nDiff: '||nDiff);
  dbms_output.put_line('END: '||TO_CHAR(SYSDATE, 'dd.mm.yyyy hh24:mi:ss'));
end;
/

Or if you prefer,

create or replace function timeout
    return number
is
begin
    return to_number(to_char(sysdate,'SSSSS'));
end timeout;
/

Function created.

create or replace procedure tcal( t in number ) as
    a  number:=1;
    st number:=to_number(to_char(sysdate,'SSSSS'));
    x varchar2(12);
    y varchar2(12);
begin
    while(a<99999999) loop
        dbms_output.enable(100000); ---> Just to make loop busy...
        a := a + 1;
        exit when (timeout - st)>=t;
    end loop;

    x:=to_char(trunc(sysdate)+st/(24*60*60),'HH:MI:SS AM');

    dbms_output.put_line('       Started: '||x);

    y:=to_char(trunc(sysdate)+timeout/(24*60*60),'HH:MI:SS AM');

    dbms_output.put_line('       Ended:   '||y);
    dbms_output.put_line(timeout-st||' seconds reached...');
end ;
/

Procedure created.

Now that is all well and good, you may say, after all the cpu isn't doing anything else at the moment and these days they rarely catch fire, but couldn't we just use the supplied procedure DBMS_LOCK.SLEEP? Well apparently it is impractical. Those with a quiet afternoon to spare might like to follow the reasons why, at forums.oracle.com/forums/thread.jspa?threadID=402345.