Home » SQL & PL/SQL » SQL & PL/SQL » EXCEPTION Handling Stored Procedure
EXCEPTION Handling Stored Procedure [message #201309] Fri, 03 November 2006 08:03 Go to next message
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
Re: EXCEPTION Handling Stored Procedure [message #201310 is a reply to message #201309] Fri, 03 November 2006 08:08 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
No, it won't be triggered. You'll just get no rows inserted, which is not an error. You only get the NO_DATA_FOUND exception for a query like:

select xxxx
into yyy
from table;


If you want to check whether any rows were inserted, query the SQL%ROWCOUNT variable.

[Updated on: Fri, 03 November 2006 08:09]

Report message to a moderator

Re: EXCEPTION Handling Stored Procedure [message #201313 is a reply to message #201309] Fri, 03 November 2006 08:15 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

You might wanna read this so you truly understand the concept of it.

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:10321465390114

Previous Topic: delete but still displayed in the table
Next Topic: Age related problem (merged)
Goto Forum:
  


Current Time: Sat Dec 03 11:45:49 CST 2016

Total time taken to generate the page: 0.06120 seconds