0

I have a particular table containing a list of Books along with their name and costs. I would like to write a function that finds and prints the name of the book with lowest cost. I wrote a function and called it , and I don't seem to be getting any errors ( Function compiled and Pl/SQL procedure successfully completed). However I don't see any actual output. Here's my code:

CREATE OR REPLACE FUNCTION min_cost
 RETURN VARCHAR2 IS 
    minCostOfBooks VARCHAR2(50);
BEGIN
 SELECT NAME 
 INTO minCostOfBooks
 FROM BOOKS
 WHERE cost = ( SELECT MIN(cost) FROM BOOKS);

 RETURN minCostOfBooks;
END;

And I call it like this: 

DECLARE

 d VARCHAR2(50);

BEGIN 

 d := min_cost();
 dbms_output.put_line('Book(s) with lowest cost ' || d);

END;

It's hard to know what the problem is because I don't seem to be seeing any errors.

Saloni Mude
  • 1

1 Answers1

0

You probably need to use:

SET SERVEROUTPUT ON;

To enable DBMS_OUTPUT to output to your console in whatever IDE you are using.

Also, your function has an issue that if several books have the same minimum cost then the function will throw an exception as the SQL statement will return too many rows; similarly if there are no books. You probably want something like:

CREATE OR REPLACE FUNCTION min_cost
RETURN VARCHAR2 IS 
   minCostOfBooks VARCHAR2(50);
BEGIN
  SELECT NAME 
  INTO minCostOfBooks
  FROM   (
    SELECT NAME
    FROM   BOOKS
    ORDER BY cost ASC
  )
  WHERE ROWNUM = 1;

  RETURN minCostOfBooks;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;

or

CREATE OR REPLACE FUNCTION min_cost
RETURN VARCHAR2 IS 
   minCostOfBooks VARCHAR2(50);
BEGIN
  SELECT MIN( NAME ) KEEP ( DENSE_RANK FIRST ORDER BY cost ASC )
  INTO   minCostOfBooks
  FROM   BOOKS;

  RETURN minCostOfBooks;
END;
MT0
  • 143,790
  • 11
  • 59
  • 117
  • 1
    You don't need to handle "no data found" in an exception block; if no data is found, then `minCostOfBooks` will be `null` by default. –  Sep 07 '17 at 21:24
  • You still need it in the first version using `ROWNUM` but, yes, it can be removed from the second version. – MT0 Sep 07 '17 at 21:26
  • ...no you don't, I just checked :-) I re-created the function to select salary from SCOTT.EMP - a similar function, and I rigged it to select from DEPTNO = 50 (which does not exist in EMP). It still works as expected: the inner query returns no rows, so the outer query also returns no rows, so the result is `null` assigned to the variable. –  Sep 07 '17 at 21:29
  • I also just edited your first solution to remove an errant semicolon. –  Sep 07 '17 at 21:30
  • @mathguy Without the exception block, I get `ORA-01403: no data found` in Oracle 12c when it is invoked from the anonymous block. – MT0 Sep 07 '17 at 22:03
  • Oh... I didn't try the anonymous procedure (I will in a moment); I just tested by calling in a SQL select statement: `select min_cost from dual;`. Let me give it a try. –  Sep 07 '17 at 22:05
  • OK, I see. I get the same error if I run the anonymous block. Here is what is going on. First, I had run into this before, I just forgot; when you call the function from SQL, "no data found" is NOT thrown as an exception; but if you call the function from PL/SQL code, the exception is thrown if it is not handled in the function. I am not sure I ever understood WHY the error is not thrown if encountered in a SQL statement; it is what it is. –  Sep 07 '17 at 22:15
  • Then: The difference between the two solutions is that, in the second solution, MIN(...) over an empty table is actually defined, specifically as `NULL`, so even if the table itself is empty, you don't have the "no data found" issue. Obviously you do with the first solution. Good to know! –  Sep 07 '17 at 22:16
  • @mathguy I knew I'd posted an answer on that question before - see https://stackoverflow.com/a/37546493/1509264 – MT0 Sep 07 '17 at 22:19
  • Yup - I read about it in the same AskTom article you referenced there. –  Sep 07 '17 at 22:21