| EXCEPTIONS AND LOOPS?? [message #19000] |
Mon, 25 February 2002 07:52  |
Sabrina
Messages: 76
Registered: February 2002
|
Member |
|
|
I have A procedure:
I have a cursor and a loop and a couple of exceptions in the proc..
Does my exceptions have to outside of the loop?
If i put it inside the loop i get error..
If i put it outside of the loop, it works..
IF I HAVE TO PUT IT OUTSIDE OF THE LOOP, HOW DO I CONTINUE IT SO THAT IT FALLS BACK IN THE LOOP?
But what i want is, that once the exception is encountered,
i want it to do whatever the exception says an then fall back in the loop..
How do i do this?
create or replace PROCEDURE DailySummary
(INPUT_DATE IN DATE) AS
V_ClmID d0.tbl_claims.claimid%TYPE;
CURSOR cursor_tbl_claims is
select C.ClaimID
FROM tbl_claims;
BEGIN
OPEN cursor_tbl_claims;
LOOP
FETCH cursor_tbl_claims INTO
V_ClmID;
EXIT WHEN cursor_tbl_claims%NOTFOUND;
exception
when others then
INSERT INTO tbl_ClaimsHistory
(CLAIMID)
VALUES
(V_ClmID);
when DUP_VAL_ON_INDEX THEN
--DO SOMETHING
CLOSE cursor_tbl_claims;
COMMIT;
END LOOP;
END;
/
PLEASE HELP!
|
|
|
|
| Re: EXCEPTIONS AND LOOPS?? [message #19001 is a reply to message #19000] |
Mon, 25 February 2002 08:10  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You can reduce your code size by using a CURSOR FOR loop and then, to handle the exception within the loop and continue, just but a begin/exception block inside the loop like this:
create or replace PROCEDURE DailySummary
(INPUT_DATE IN DATE) AS
CURSOR cursor_tbl_claims is
select ClaimID FROM tbl_claims;
BEGIN
for r in cursor_tbl_claims loop
begin
-- do something here
dbms_output.put_line(r.claimid);
exception
when others then
-- handle exception
end;
end loop;
COMMIT;
END;
/ |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
