Raise exception in case clause PL/SQL function [message #678734] |
Mon, 06 January 2020 01:10 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Dear All,
Is is allowed to raise an exception inside a case clause in a PL/SQL function?
I have the following example:
CREATE OR REPLACE FUNCTION F_test
(
d_date date
)
/*
*/
RETURN NUMBER AS
I_INTEREST_RATE NUMBER;
-- Exceptions
excep1 EXCEPTION;
NO_RATE_FOUND exception;
BEGIN
WITH
DD AS
(
SELECT sysdate s_DATE FROM dual
)
SELECT
CASE
WHEN s_DATE > D_DATE
THEN -1 -- RAISE excep1 --
WHEN s_DATE <= D_DATE
THEN 5.9
ELSE -2 --RAISE NO_RATE_FOUND --
END INTO I_INTEREST_RATE
FROM DD;
RETURN (I_INTEREST_RATE);
-- Log errors in case of exception
EXCEPTION
WHEN excep1 THEN
RAISE_APPLICATION_ERROR(-20810,'xxxxx:' || D_DATE);
WHEN NO_RATE_FOUND THEN
RAISE_APPLICATION_ERROR(-20813,'No rate/date found for on: ' || D_DATE);
WHEN OTHERS THEN
RAISE;
END ;
The above is a sample function where -1 and -2 used to be the return values before trying to add exceptions. When exceptions are added (-1 and -2 are removed and the exceptions were raised)
CASE
WHEN s_DATE > D_DATE
THEN RAISE excep1
WHEN s_DATE <= D_DATE
THEN 5.9
ELSE RAISE NO_RATE_FOUND
END INTO I_INTEREST_RATE
I get compilation error: 1 PL/SQL: ORA-00905: missing keyword. How can I fix this or is it not allowed to raise an exception inside a case clause?
Thanks,
Ferro
|
|
|
|
|
Re: Raise exception in case clause PL/SQL function [message #678737 is a reply to message #678736] |
Mon, 06 January 2020 02:28 |
|
Michel Cadot
Messages: 68658 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
I suggest something like:
create or replace function f (p_date date) return number is
V_EXCEP1 constant pls_integer := -1;
V_NO_RATE constant pls_integer := -2;
excep1 EXCEPTION;
NO_RATE_FOUND exception;
I_INTEREST_RATE NUMBER;
begin
WITH
DD AS
(
SELECT sysdate s_DATE FROM dual
)
SELECT
CASE
WHEN s_DATE > p_DATE
THEN v_excep1
WHEN s_DATE <= p_DATE
THEN 5.9
ELSE v_NO_RATE
END INTO I_INTEREST_RATE
FROM DD;
case I_INTEREST_RATE
when v_excep1 then raise excep1;
when v_NO_RATE then raise NO_RATE_FOUND;
else return I_INTEREST_RATE;
end case;
EXCEPTION
WHEN excep1 THEN
RAISE_APPLICATION_ERROR(-20810,'xxxxx:' || D_DATE);
WHEN NO_RATE_FOUND THEN
RAISE_APPLICATION_ERROR(-20813,'No rate/date found for on: ' || D_DATE);
end;
/
Or better, if possible in your actual issue:
create or replace function f (p_date date) return number is
V_EXCEP1 constant pls_integer := -1;
V_NO_RATE constant pls_integer := -2;
I_INTEREST_RATE NUMBER;
begin
WITH
DD AS
(
SELECT sysdate s_DATE FROM dual
)
SELECT
CASE
WHEN s_DATE > p_DATE
THEN v_excep1
WHEN s_DATE <= p_DATE
THEN 5.9
ELSE v_NO_RATE
END INTO I_INTEREST_RATE
FROM DD;
case I_INTEREST_RATE
when v_excep1 then RAISE_APPLICATION_ERROR(-20810,'xxxxx:' || p_DATE);
when v_NO_RATE then RAISE_APPLICATION_ERROR(-20813,'No rate/date found for on: ' || p_DATE);
else return I_INTEREST_RATE;
end case;
end;
/
[Edit flaw in copy/paste]
[Updated on: Mon, 06 January 2020 10:32] Report message to a moderator
|
|
|
|