Home » SQL & PL/SQL » SQL & PL/SQL » EXCEPTION Handling Stored Procedure
EXCEPTION Handling Stored Procedure [message #201309] |
Fri, 03 November 2006 08:03  |
pw1975
Messages: 24 Registered: June 2006
|
Junior Member |
|
|
Hi Folks,
my Code looks like following:
CREATE OR REPLACE PROCEDURE P_Hssm_Hist_Accessed (p_unc_path IN VARCHAR2, p_t_stamp IN VARCHAR2, p_import IN VARCHAR2) IS
l_statement VARCHAR2(4000);
l_filesize_total NUMBER(22,3);
l_filecount_total NUMBER(22,3);
l_mindays INTEGER;
BEGIN
.....
l_statement := 'INSERT INTO T_R_HISTORY (
SELECT
UNC_PATH
, T_STAMP
, ''Alte Dateien'' DESCR_DE
, ''aged files'' DESCR_EN
, FILESIZE_AGGR
, ''' || l_filesize_total || ''' FILESIZE_TOTAL
, FILECOUNT_AGGR
, ' || l_filecount_total || ' FILECOUNT_TOTAL
, 4 TYP
, ''ACCESSED'' ATTRIBUTE
FROM (
SELECT
UNC_PATH
, T_STAMP
, COUNT(FILENAME) FILECOUNT_AGGR
, SUM(FILESIZE) FILESIZE_AGGR
FROM (
SELECT
T_STAMP
, UNC_PATH
, FILENAME
, FILESIZE
FROM ' || p_import || '
WHERE
UNC_PATH = ''' || p_unc_path || '''
AND T_STAMP = TO_DATE(''' || p_t_stamp || ''',''DD.MM.YYYY HH24:MI:SS'')
AND (SYSDATE - ACCESSED) > ' || l_mindays || '
AND FILENAME IS NOT NULL
)
GROUP BY
T_STAMP
, UNC_PATH
)
)';
EXECUTE IMMEDIATE l_statement;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO T_R_HISTORY VALUES (p_unc_path, TO_DATE(p_t_stamp, 'DD.MM.YYYY HH24:MI:SS'), 'Alte Dateien', 'aged files', 0, l_filesize_total, 0, l_filecount_total, 4, 'ACCESSED');
P_Hssm_Log('P_HSSM_HIST_ACCESSED - Default values (no data found)',p_unc_path,p_t_stamp);
WHEN OTHERS THEN
P_Hssm_Log('P_HSSM_HIST_ACCESSED - Error', p_unc_path, p_t_stamp);
END;
There are only two important parts of the upper query. The first part is the INSERT INTO ... VALUES (SELECT ....)
and the second: EXCEPTION HANDLING.
The inner SELECT of the INSERT returns an empty resultset. But the Exception NO_DATA_FOUND is not triggered at all. Is there anything missing in the Code?
Thanks a lot in advance!
Cheers,
pw
|
|
|
|
|
Goto Forum:
Current Time: Wed Feb 12 22:03:00 CST 2025
|