Hi all,
I have a script to get FRA info which is working fine unless the query returns 'no rows selected' when I cant seem to raise an exception.
Is there any exception to capture this empty result set? I was thinking that the NO_DATA_FOUND exception might do it but it doesnt seem to capture when 'no rows selected' is returned.
Any ideas?
The query I test with is;
SELECT a.space_limit,
a.space_used,
(((a.space_limit-a.space_used)/(a.space_limit))*100),
(a.space_used/a.space_limit*100),
SYSDATE,
b.name,
a.name "FRA_NAME"
FROM v$recovery_file_dest a, v$DATABASE b
/
no rows selected
Thanks for any help.
My script with the exceptions is as follows:
CREATE OR REPLACE PROCEDURE frainfo
IS
l_sql varchar(2000);
v_sqlerrm varchar2(500);
BEGIN
delete from ORADBA.GXS_FRA_STATUS;
FOR cur_rec IN (SELECT DB_NAME,DB_LINK
from MESSAGING_PROD_LIST
WHERE STATUS = 'ACTIVE'
ORDER BY 1)
LOOP
BEGIN
l_sql := 'INSERT INTO ORADBA.GXS_FRA_STATUS (SPACE_LIMIT, SPACE_USED, FREE,FULL, INSERT_TIME, DB_NAME, FRA_NAME)
(SELECT * FROM
(SELECT a.space_limit,
a.space_used,
(((a.space_limit-a.space_used)/(a.space_limit))*100),
(a.space_used/a.space_limit*100),
SYSDATE,
b.name,
a.name "FRA_NAME"
FROM v$recovery_file_dest@' || cur_rec.db_link || ' a, v$DATABASE@' || cur_rec.db_link || ' b ))';
EXECUTE IMMEDIATE l_sql;
EXCEPTION
WHEN ZERO_DIVIDE THEN
INSERT INTO oradba.GXS_FRA_STATUS (DB_NAME,ERROR_MESSAGE)
values (cur_rec.db_name, 'No FRA');
WHEN NO_DATA_FOUND THEN
INSERT INTO oradba.GXS_FRA_STATUS (DB_NAME,ERROR_MESSAGE)
values (cur_rec.db_name, 'No FRA');
WHEN OTHERS THEN
v_sqlerrm := substr(SQLERRM,1,90);
INSERT INTO oradba.GXS_FRA_STATUS (DB_NAME,ERROR_MESSAGE)
values (cur_rec.db_name, v_sqlerrm);
END;
COMMIT;
END LOOP;
END;
/