How do I unit test this PL/SQL procedure? (using utplsql)

Question

Package is very very basic. Loops through a cursor, and updates 2 values where the record_ids are equal.

What's an appropriate unit test for this sort of procedure?

I'm going to add some skeleton code because the answers so far, while good, tie to the crux of my issue here: What do I test?

PROCEDURE set_shift_times_to_null( RETVAL OUT VARCHAR2,
                                 ERRBUF OUT VARCHAR2,
                                RECORDS_UPDATED OUT NUMBER) IS 

   CURSOR evening_shift_employees_cur IS
   select employee
   FROM employees
   where SHIFT='EVENING'
   ;

BEGIN
   RECORDS_UPDATED := 0;
   RETVAL := '2';

   FOR evening_shift_employees IN evening_shift_employees_cur LOOP
      UPDATE NIGHT_SHIFT 
      Set SOME_DUMB_FIELD = evening_shift_employees.employee;

      RECORDS_UPDATED := RECORDS_UPDATED + 1;
   END LOOP;
   COMMIT;
   RETVAL := 0;
EXCEPTION WHEN OTHERS THEN
   ROLLBACK;
   ERRBUF := 'Error occurred - ' || SQLERRM;
END set_shift_times_to_null;

Answer 1

The appropriate unit test it to validate the affected tables to check that the updated records are what expected.

You can create temporary tables with the results you expect and the unit testing code compare the results. Of course is hard work but if you want to test you have to do something like this.

It depends on the work of procedure, but if you want to be sure that test is fine you have to check as possibilities as possible.

A lot of the conditions have to be validated with constraints and the test unit procedures have to execute code that force the database to check that constraints (inserts, and so on).

Answer 2

Basically, you want to exercise all the possibilities of your procedure:

• Test with equal record-ids.
• Test with non-equal record-ids.
• Test with invalid (non-integer? negative?) record-ids.

Also, test the boundary conditions:

• Test with record-ids off by one (ex: 104 and 105).
• Test with maximum record-id (MAX_INT?).
• Test with zero-value record-id.

Here is a nice example of good unit-testing practices.

EDIT: I don't know of a robust unit-testing tool for database queries. I would set up a test table of evening_shift_employees with various record-id conditions as described above. Then, as suggested by FerranB, check that the records are updated as expected for validation.

Answer 3

What I ended up doing was the following:

1. Take record count of records that main cursor will update fields to null
2. execute procedure ( returns a value of rows updated )
3. Take record count of same records as first time
4. The difference between the record counts should equal the number of records updated in the procedure. If so, the test passes.

Does this make sense, or is it circular logic?

Answer 4

A couple of suggestions.

Use SQL%ROWCOUNT:

BEGIN
  UPDATE NIGHT_SHIFT
   Set SOME_DUMB_FIELD = evening_shift_employees.employee;
  v_rows_processed := SQL%ROWCOUNT;
  dbms_output.put_line('There were '||v_rows_processed||' rows updated');
END;

Don't Use When Others (why do you want to lose the stack trace).Just use Exceptions, you will be relying on the caller to check the contents of the ERRBUF.

begin
  insert into t values ( 1 );
  exception when others then 
  log_error; 
  raise; 
end;

log_error implementation looks like:

create or replace procedure log_error
as
  pragma autonomous_transaction;
  l_whence varchar2(1024);
  l_msg    varchar2(1020) default sqlerrm;
  l_code   number default sqlcode;
  begin
    l_whence := whence;
    insert into error_table
     ( timestamp, whence, msg, code )
    values
     ( sysdate, whence, l_msg, l_code );
    commit;
   exception
  when others then
   rollback;
   raise;
  end;

Consider not using any pl/sql. on the surface the update appears completely 'doable' without any cursor. Perhaps an updateable inline view:

update (
  select e.sal as emp_sal, e.comm as emp_comm,
    ns.sal as ns_sal, ns.sal/2 as ns_comm
  from employees e, night_shift ns
  where e.deptno = ns.deptno
  ) 
set emp_sal = ns_sal, emp_comm = ns_comm

Answer 5

For anyone else who sees this, I found this in the documentation for utplsql: PROCEDURE utAssert.eqtable (

   msg_in IN VARCHAR2,
   check_this_in IN VARCHAR2,
   against_this_in IN VARCHAR2,
   check_where_in IN VARCHAR2 := NULL,
   against_where_in IN VARCHAR2 := NULL,
   raise_exc_in IN BOOLEAN := FALSE
);

It's under the assert documentation; looks like it does exactly what I was trying to do.