Re: embedded PL/SQL

In article <4h4na8$d12_at_gatekeeper.tasb.org>, xia__chr_at_tasb.org says...

Greetings:

#I wrote some embedded PL/SQl code on pro*c 1.4, it is so simple that I
#an't believe it could fail, but it failed anyway! It seems to me that It
#does ot get
#executed for some reason. In the beginning of the PL/SQL block, i put an
#assignment
#statement: :error_msg := 'nothing happened'; immediately after the
#PL/SQL BLOCK,
#I printed it, IT IS EMPTY.

# EXEC SQL EXECUTE

# DECLARE
# missing_update exception;
# missing_org exception;
# missing_policy exception;
# missing_insert exception;
# orgType varchar(4);
# BEGIN
# :error_msg := 'nothing happening';

# select update_number, to_char(date_changed, 'MM/DD/YY'),
# nvl(ldu_number,'NONE'), to_char(date_changed,
'MM/DD/YY')
# into :updateNum, :updateDate, :lduNum, :lduDate
# from opus.update_history
# where org_number = :baseOrg
# and policy_code = :polCode
# and policy_type = :polType
# and policy_version = :polVersion
# and date_changed = (select max(date_changed)
# from opus.update_history
# where org_number = :baseOrg
# and policy_code = :polCode
# and policy_type = :polType
# and policy_version = :polVersion
# );
           

• no such record found => you will never reach this statement!
• (see PL/SQL 2.0 User's Guide and Reference, 4-24)
  # if SQL%NOTFOUND then -- no such record in update history>
  # raise missing_update;
  # end if;
  #
  # EXCEPTION -- begining exception handling
  
• you won't need! BEGIN
  # when missing_update then
  # :status := 1;
  # :error_msg := 'no record found in update_history';
  # when missing_org then
  # :status := 1;
  # :error_msg := 'no record found in organization';
  # when missing_policy then
  # :status := 1;
  # :error_msg := 'no record found in policy_name';
  # when missing_insert then
  # :status := 1;
  # :error_msg := 'error insert';
  ******* you won't need! END
  # when others then
  # :status := 1;
  # :error_msg := 'error found in pl/sql block';
  

# END;
#
# END-EXEC;
#

Your error_msg will be empty, if your PL/SQL block will be left caused by an unhandled exception.

In my opinion there's only one way to leave this block unhandled: caused by another exception, raised inside your WHEN OTHERS exception handling!
But ... in your case I can't imagine why!

hope this helps

Stefan Received on Tue Mar 05 1996 - 00:00:00 CET