I'm trying to do an exception with raise

Question

I'm trying to make an exception with RAISE in PL / SQL. I did the script but when I want to run it does not show me one of the two messages and it just gives me 'anonymous block completed'.

     Accept cititor prompt 'Introduceti un cititor'; 
      DECLARE data_la_limita EXCEPTION; data_returnare    varchar(10);
    --cititor varchar(10); 
     BEGIN 
     SELECT s.data_dereturnat INTO data_returnare FROM fisa_imprumuturi s left join legitimatii s1 
    on s1.nrlegitimatie = s.nr_legitimatie left 
   join cititori s2 on s2.codcititor = s1.codcititor 
    WHERE s2.numecititor like '%&cititor%'; IF (data_returnare > sysdate ) THEN
         RAISE data_la_limita;
   END IF; 
    EXCEPTION
          WHEN data_la_limita THEN
           DBMS_OUTPUT.PUT_LINE('Cititorul a trecut peste data returnarii!');
          WHEN OTHERS THEN
           DBMS_OUTPUT.PUT_LINE('Cititorul trebuie sa returneze la data: ' || data_returnare); END;

I tried to replace sysdate with a date manually ('01-May-2019') but is not working.

To take the data when a person passes the return data..if not, show me the date when it should be returned — ferye21, Dec 15 '19 at 21:03
Your comments have not clarified the question but rather have clouded it. Please **edit your question** to include some sample data for `fisa_imprumuturi` and `legitimatii`, and also the expected output for different input values of `cititor`. That is, which values you expected to raise an exception (and what exception) and which values you expect to cause the program to complete successfully. — APC, Dec 31 '19 at 09:48

APC · Answer 1 · 2019-12-31T09:42:38.297

I'm trying to make an exception with RAISE in PL / SQL ... but when I want to run it does not show me one of the two messages

You're not seeing the messages because you are running your program in an environment which doesn't show DBMS_OUTPUT. You have coded an EXCEPTION handler which suppresses your exceptions and does not re-raise them. This is bad practice because DBMS_OUTPUT is not a mechanism for propagating exceptions: when output is suppressed or the program runs as an autonomous background routine (which is the main use of PL/SQL) there is no way to tell that the program failed.

You could enable SEVEROUTPUT (in SQL*Plus) or configure a DBMS_OUTPUT tab (in an IDE like SQL Developer). Doing this means you would see the messages next time your program runs.

But it would be better just to raise exceptions and let the calling program handle them. Given that, you should probably re-write your program something like this:

Accept cititor prompt 'Introduceti un cititor'; 

DECLARE 
  data_la_limita EXCEPTION; 
  data_returnare    varchar(10);
    --cititor varchar(10); 
BEGIN 

  SELECT s.data_dereturnat 
  INTO data_returnare 
  FROM fisa_imprumuturi s 
       left join legitimatii s1 on s1.nrlegitimatie = s.nr_legitimatie left 
       join cititori s2 on s2.codcititor = s1.codcititor 
    WHERE s2.numecititor like '%&cititor%'; 

  IF (data_returnare > sysdate ) THEN
         RAISE data_la_limita;
  END IF; 

EXCEPTION

  WHEN data_la_limita THEN
    raise_application_error(-20000, 'Cititorul a trecut peste data returnarii!');

END;

It is better not to code a WHEN OTHERS handler unless we're logging the message. Even then we should execute a RAISE to pass the actual error instead of an unhelpful generic message. The calling program needs to know what went wrong so it can make the correct decision about what to do next (e.g. ignore and continue, abort and re-raise, something else).

I'm trying to do an exception with raise

1 Answers1