I was recently working through Chapter 9 of the 12c Database 2 Day Developer’s Guide and found the following bug in the sample code.

Here is the original code:

FUNCTION add_department
    ( p_department_name   IN departments.department_name%TYPE,
      p_manager_id        IN departments.manager_id%TYPE )
    RETURN departments.department_id%TYPE
  IS
    l_department_id departments.department_id%TYPE;
  BEGIN
    INSERT INTO departments ( department_id, department_name, manager_id )
    VALUES ( departments_sequence.NEXTVAL, p_department_name, p_manager_id );

    RETURN l_department_id;
  END add_department;

The function creates a new department and should return the newly created department id. Except it doesn’t. The variable used to return the newly created department id, l_department_id is never set so it will always returns null.

One solution to this bug is to use the Returning Into clause and the revised code would be:

...
    INSERT INTO departments ( department_id, department_name, manager_id )
    VALUES ( departments_sequence.NEXTVAL, p_department_name, p_manager_id )
    RETURNING department_id INTO l_department_id;
 
    RETURN l_department_id;
...

It’s a trivial issue so why I am writing about it? The reason is that seeing this problem I could have easily moved on and finished the chapter but I noticed the Feedback button and thought that others will also encounter this problem and may waste time wondering why the department id is not being returned.

After clicking the feedback button and using my OTN account (you also have the choice of remaining anonymous), describing the issue along with the suggested fix as described above I pressed submit and thought no more about it.

Within 24 hours I had an email reply from someone at Oracle (and someone not using a do_not_reply email address) thanking me for pointing out the error and assuring me it will be fixed in future editions. Whilst it remains to be seen if this will be done, the response to my feedback has left a very positive impression with me which made me glad I made the effort to report this issue.

So if you spot something within the masses of Oracle documentation, report it and help fix those broken windows.