CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-sqldeveloper
rubberBoy
rubberBoy

Reputation: 35

Show the output before the exception

I have a function (2 IN parameters) that returns the average of salaries that a person has had, and if the person doesn't exist in the database (id and name aren't present in the DB) I will raise an exception or if he hasn't any salary then another exception will be raised.

The thing is that I have a collection with some id & names that will generate different outputs for different parameters,
half of them will generate the normal output meaning that the people are in the DB and have salary and the other half don't have a salary or don't exist in the DB.

SQL Developer will show the user defined exception even though I put the "correct" people before the "incorrect" people.

What can I do to print the returned message before the exception is raised?

Upvotes: 0

Views: 85

Answers (1)

Littlefoot
Littlefoot

Reputation: 142743

It depends on how you're doing that. People usually do it in a (cursor) loop. If you're doing the same, then you'll need to include additional begin-exception-end block within the loop itself.

I don't know your tables so this is kind of a stupid code, but should give you an idea:

declare
  l_ename emp.ename%type;
  l_sal   emp.sal%type;
begin
  for cur_d in (select deptno, dname from dept) loop
    begin                                         --> begins here
      select e.ename, e.sal 
        into l_ename, l_sal
        from emp e
        where e.deptno = cur_d.deptno;

    exception                                     --> handle errors
      when no_data_found then
        null;
      when too_many_rows then
        null;
    end;                                          --> ends here
  end loop;
end;

Upvotes: 1

Related Questions