Home » SQL & PL/SQL » SQL & PL/SQL » Raise exception in case clause PL/SQL function (11.2.0.1.0)
Raise exception in case clause PL/SQL function [message #678734] Mon, 06 January 2020 01:10 Go to next message
OraFerro
Messages: 387
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 #678735 is a reply to message #678734] Mon, 06 January 2020 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

RAISE is a PL/SQL instruction not a SQL function.
So you can use it in a PL/SQL CASE expression not in a SQL one.

Re: Raise exception in case clause PL/SQL function [message #678736 is a reply to message #678735] Mon, 06 January 2020 02:11 Go to previous messageGo to next message
OraFerro
Messages: 387
Registered: July 2011
Senior Member
Thank Michel,

So how do you think I should handle this? Do I store the return values of the case and then check the value and raise exception? Is there any other way?

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 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
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

Re: Raise exception in case clause PL/SQL function [message #678738 is a reply to message #678737] Mon, 06 January 2020 02:30 Go to previous message
OraFerro
Messages: 387
Registered: July 2011
Senior Member
Thanks a lot Michel, this was helpful.
Previous Topic: Error performing insert
Next Topic: ORA-01031: insufficient privileges | Database level trigger
Goto Forum:
  


Current Time: Sun Sep 20 06:43:21 CDT 2020