sqlplus not found error checking

[kkitt]

I have the following code sample. After the inital select query, I want to check the return code from the query and if a row was not found the exit with a specific return code else continue processing the next statment checking the return code as I progress through the code. The places that I have the print sql.sqlcode is where i want to put in the error checkting. If I remove the prints then it will work as long as a row is found, else it fails for a null insert.

Is this even possible?

sqlplus -S dev_user/dev_user > kout1.txt <<  EOF

variable v_from varchar2(32);
variable v_to   varchar2(32);
variable v_from varchar2(32);
variable v_to   varchar2(32);
variable v_kp   varchar2(10);
variable v_woseq   number;

whenever sqlerror exit sql.sqlcode;
whenever oserror exit failure;

begin
   select k_fromname, k_toname, k_protocol
     into :v_from, :v_to,:v_kp
     from k_relationship
    where k_notifyname = '$mb';
end;
/
print sql.sqlcode;

begin
   select workorder_sequence.nextval
     into :v_woseq
     from dual;
end;
/
print sql.sqlcode;

begin
   insert into k_workorder values (:v_woseq, :v_from, :v_to, 'ZVKKITTE', 'P', ' ', ' ', ' ', 1000, 'HTTP');
end;
/

   print v_from;
   print v_to;
   print v_kp;
quit

 

[stefanhei]

Hi.
You could use PL/SQL exceptions:
...
begin
select k_fromname, k_toname, k_protocol
into :v_from, :v_to,:v_kp
from k_relationship
where k_notifyname = '$mb';
exception
when no_data_found then ...
when others ...
end;
...

Stefan