SQL & PL/SQL

Hi,

I have two tables, BILL and VALIDATIONS, both have (LDAP_UID, TERM) as composite key. I wrote a procedure to do the following:

1. I use this composite key to retrieve some non-key fields from the BILL table, form a new row and insert it into the VALIATIONS table;
2. Before inserting into the VALIDATIONS table, I use a SELECT statement to check whether the composite key already exists in the VALIDATIONS table, and do not do the insertion unless the composite key is not found.

The procedure is as follows:

create or replace procedure save_transactions
   (i_ldap_uid IN varchar2, i_term IN varchar2)
AS
   v_validated_ldap_uid validations.ldap_uid%type;
   v_student_id validations.student_id%type;
   v_first_name validations.first_name%type;
   v_last_name validations.last_name%type;
   v_term validations.term%type;
BEGIN
   select ldap_uid
   into v_validated_ldap_uid
   from validations
   where ldap_uid = i_ldap_uid
   and term = i_term;

   if SQL%ROWCOUNT = 0 then
      select student_id, first_name, last_name
      into v_student_id, v_first_name, v_last_name
      from bill
      where ldap_uid = i_ldap_uid
      and term = i_term;
      insert into validations values (
      v_student_id, i_ldap_uid, v_first_name, v_last_name,
      i_term, sysdate, (to_char(sysdate, 'yymmdd'))||
      (select to_char(count(TRANSACTION_DATE)+1, 'fm0999') from VALIDATIONS where to_char(TRANSACTION_DATE) = to_char(sysdate))
      );
   end if;
END;
/

The procedure is created and compiled clean, without any error. But error occurs when using it. If the row is already in the VALIDATIONS table, the {color:blue}if SQL%ROWCOUNT = 0 {color} goes fine and the insertion code block is bypassed. However, if the row is not found in the VALIDATION table, I got the following error:

SQL> execute save_transactions('john.brown', '200902')
BEGIN save_transactions('john.brown', '200902'); END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "ZBOV.SAVE_TRANSACTIONS", line 10
ORA-06512: at line 1

Well, to me, the whole point of using SQL%ROWCOUNT is for testing. If it can not test SQL%ROWCOUNT = 0, how much else can it test? However, I got the error and I must be wrong. But what is is wrong?

Many thanks for your help!


Newman