I do date arithmetic in the script that follows, but it is not
calculating the correct number of days between 2 days?
If SYSDATE and v_tgttstmp are both in the same month it works fine
though?
I set the date format with the following statement in my 1st line of
code:
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD';
For example: The following excerpt from my script subtarcts a date
from a status table form SYSDATE:
v_tgttstmp is type DATE
- Calculate Elapsed Days Difference
v_days := SYSDATE - v_tgttstmp;
dbms_output.put_line(sysdate || v_tgttstmp || v_days);
Here's the output of the script when I run it using
DBMS_output.put_line:
As you can see below SYSDATE = 2000 03 02, v_tgttstmp = 2000 02 28 and
v_days = 2 when it should be 3?
SQL> @vantagestatus.sql
Session altered.
2000 03 022000 02 282
2000 03 022000 02 282
PL/SQL procedure successfully completed.
Here's the entire script:
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD';
-- This PL/SQL generates a flat stanza file that is used by a Web
application
-- to provide a Vantage MidWest Extraction status for each of the
Vantage regions.
-- It reads from the vantage_region_status table, extracts the date and
time from the
-- last_extract_date column and applies the appropriate time zone. The
current
-- system date is then compared to the replication date and time for the
- same time zone, and a status of Green = within 2 days, Yellow = 3
days behind and
- Red = 4 days or more behind is arrived at.
- Define Variables
declare
v_region_name vantage_region_status.region_name%TYPE;
v_tgttstmp vantage_region_status.last_extract_date%TYPE;
v_days number(2);
v_status varchar2(1);
v_csvcontrol number;
v_temp_datetime varchar2(12);
v_final_datetime varchar2(28);
fileHandler UTL_FILE.FILE_TYPE;
- Define Procedure to Calculate Status / Green, Yellow or Red
procedure calculate_status (v_statusdays Number) IS
BEGIN
IF v_statusdays <= 2 THEN
v_status := 'G';
ELSIF v_statusdays >= 3 and v_statusdays < 4 THEN
v_status := 'Y';
ELSIF v_statusdays >= 4 THEN
v_status := 'R';
END IF;
v_status := 'Y';
- Now Output Status to a "CSV" Flat file
IF v_csvcontrol = 1 THEN
fileHandler := UTL_FILE.FOPEN('/orautlwork',
'olap110kvantagestatus', 'w');
ELSE
fileHandler := UTL_FILE.FOPEN('/orautlwork',
'olap110kvantagestatus', 'a');
END IF;
select to_char(v_tgttstmp,'fmMon DD, YYYY') into v_temp_datetime
from dual;
v_final_datetime := v_temp_datetime || ' 10:00PM Central';
UTL_FILE.PUTF(fileHandler, v_region_name||' ||
'||v_final_datetime||' || '||v_status);
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file or
path not in INIT.ORA.');
END calculate_status;
- Mainline Processing
BEGIN
- Set CSV to be Purged
v_csvcontrol := 1;
- Open Vantage Region Status Table subquery cursor
FOR Vantage_Rec IN (SELECT region_code, region_name, last_extract_date
FROM ccs.vantage_region_status) LOOP
- Process the Region
v_region_name := Vantage_Rec.region_name;
v_tgttstmp := Vantage_Rec.last_extract_date;
- Calculate Elapsed Days Difference
v_days := SYSDATE - v_tgttstmp;
dbms_output.put_line(sysdate || v_tgttstmp || v_days);
- Calculate Red/Yellow/Green Status
calculate_status(v_days);
- Increment the output file counter so we drop into append mode on
pass 2.
v_csvcontrol := 2;
END LOOP;
END;
/
exit;
Any help would be appreciated!!
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Mar 02 2000 - 00:00:00 CST
