Subscribe

RSS Feed (xml)

Powered By

Skin Design:
Free Blogger Skins

Powered by Blogger

oracle search

Wednesday, August 6, 2008

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.

No comments: