0

I created a function to calculate total amount (shipping + subtototal) for customer If customer isn't cerated I want to see in the output error

DBMS_OUTPUT.PUT_LINE('IDCUSTOMER: ' || customer_id || 'This ID does not exist!');

But when I run code, I don't see in the output error. What I do wrong?

CREATE OR REPLACE FUNCTION calculate_sub_withoout_ship
                          (customer_id bs_basket.idcustomer%type)
  RETURN NUMBER IS
  sub_calc NUMBER := 0;

BEGIN
  SELECT SUM(subtotal + tax)
    INTO sub_calc
    FROM bs_basket
   WHERE idcustomer = customer_id;
  RETURN sub_calc;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('IDCUSTOMER: ' || customer_id ||'This ID does not exist!');
END;
/

SELECT calculate_sub_withoout_ship(1000) FROM dual;

I get NULL and I don't see error.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Dina Butko
  • 1
  • 1
  • Are you using [SQL Plus](https://en.wikipedia.org/wiki/SQL_Plus) or [SQL Developer](https://en.wikipedia.org/wiki/Oracle_SQL_Developer) or [PL/SQL Developer](https://en.wikipedia.org/wiki/PL/SQL_Developer) or [Toad](https://en.wikipedia.org/wiki/Toad_(software)) or something else? – Abra Mar 29 '22 at 16:50
  • I am using SQL Developer, but I get query result (null) and in the dbms output I don't see message that this id doesn't exist – Dina Butko Mar 29 '22 at 16:55
  • 1
    Functions don't raise no_data_found when called from SQL. However, there is nothing that could fail to find a result anyway, because if there are no rows then the sum will just be null, which is not an exception. – William Robertson Mar 29 '22 at 19:33
  • Regarding dbms_output, you can test that separately without any functions to complicate things. SQL Dev has a slightly complicated way of handling it, where other tools have an output tab with a checkbox. – William Robertson Mar 29 '22 at 19:39

0 Answers0