How to avoid no data exception?

Question

I am working on a project and I have a database schema written in Oracle db for a library. I have some issues when trying to build a function which should provide some recommendations for the user.

The recommendations should be given based on the last books genres some user had borrowed in the past month and the most read books of all users. Every time I try to run the function I get a no data error even if I have values in the tables.

    CREATE OR REPLACE FUNCTION getGenre(bookISBN IN VARCHAR2) RETURN VARCHAR2
    AS
      v_bookGenre VARCHAR2(100);
    BEGIN
      select genre into v_bookGenre from (select genre from books where bookISBN=isbn);
      return v_bookGenre;
    END getGenre;
    /
    CREATE OR REPLACE TYPE t IS TABLE OF varchar2(200);
    /
    create or replace type typeRecom is VARRAY(200) of VARCHAR2(200);
    /
    CREATE OR REPLACE FUNCTION topRecommandations(idUser IN VARCHAR2) 
    RETURN typeRecom
    AS

      lastGenres t :=t();
      readedBooks t:=t();
      topTen typeRecom;

      v_i NUMBER := 1;
      v_j NUMBER := 1;

      verifDateBook NUMBER:=0;
      verifDateLoan NUMBER:=0;

    BEGIN
      select count(*) into verifDateBook from books;
      if(verifDateBook = 0) then
        raise no_data_found;
      end if;

      select count(*) into verifDateLoan from loans;
      if(verifDateLoan = 0) then
        raise no_data_found;
      end if;

      lastGenres.extend();
      for i in (select genre from books join loans on books.isbn=loans.bookId where loanDate>add_months(sysdate,-1) and idUser=loans.regNo order by genre desc) loop
        DBMS_OUTPUT.PUT_LINE(i.genre);
        lastGenres.extend();
        lastGenres(lastGenres.count) := i.genre;
      end loop;
      /*select title into bookTitles from book join loan on book.isbn=loan.regNo;*/
       readedBooks.extend();
       for i in (select bookId from loans group by bookId order by count(bookId) desc) loop
        readedBooks.extend();
        readedBooks(readedBooks.count) := i.bookId;
      end loop;
      --select bookId into readedBooks from loan order by count(bookId) desc;

      for v_i IN 1..50 LOOP
        for v_j in 1..50 LOOP
          DBMS_OUTPUT.PUT_LINE(lastGenres(v_j));
          DBMS_OUTPUT.PUT_LINE(getGenre(readedBooks(v_i)));
          if(lastGenres(v_j)=getGenre(readedBooks(v_i))) then
            topTen(v_i) := getGenre(readedBooks(v_j));
          end if;
        EXIT WHEN topTen.count=10;
        END LOOP;
      EXIT WHEN topTen.count=10;
      end LOOP;
      for v_cont in 1..10 LOOP
            --if(topTen.count <= 10) then
            DBMS_OUTPUT.PUT_LINE(topTen(v_cont));
          --end if;
      end loop;
      return topTen;
      EXCEPTION
      WHEN no_data_found THEN 
        DBMS_OUTPUT.PUT_LINE('Nu exista valori.');

    END topRecommandations;

Expected result: a list with 10 recommended books. Actual result: no data found error.

Answer 1

Unless I'm wrong, the only place that might return NO_DATA_FOUND is the getGenre function, as it has a SELECT some value into a variable, and no exceptions are handled. Other SELECTs use aggregates (which will return 0 if there's nothing there).

Therefore, fix that function, e.g.

CREATE OR REPLACE FUNCTION getGenre(bookISBN IN VARCHAR2) 
  RETURN VARCHAR2
AS
  v_bookGenre VARCHAR2(100);
BEGIN
  select genre 
    into v_bookGenre
    from books where bookISBN = isbn;

  return v_bookGenre;

EXCEPTION
  when no_data_found then
    return null;
END getGenre;

By the way, why did you use an inline view in that function? Why didn't you simply return genre from the table itself (like I did)?