Re: Form level procedures and functions

tma1000_at_cus.cam.ac.uk (T.M. Armitage) writes:

> [...] I was wondering if some kind soul
> could send me some examples of their own procedures and functions so that I
> can learn by example (far easier than reading the manuals :-)).

For what it's worth, I enclose a couple of examples at the end of this mail message. No claims as to their originality or interest.

> Specifc questions are :-
>
> 1. Is there a Do CASE command in pl-sql ?

Don't think so. I use IF-THEN-ELSE as follows:

	if (exp) then
		s1;
	elsif (exp) then
		s2;
	elsif (exp) then
		s3;
	else
		s4;
	end if;



> 2.  I understand that pl-sql supports the data type BOOLEAN - can I pass a


>     boolean data type back to an on-validate-field trigger ?

I haven't done it, but I don't see why it shouldn't be allowed in an ON-VALIDATE-FIELD trigger. Have you tried it and found problems?

David Hazledine                                                EMBL Data Library
Database Administrator                                                PF 10.2209
EMBL Data Library                                      69012 Heidelberg, Germany

Internet: Hazledine_at_EMBL-Heidelberg.DE

1. PL/SQL to fill a detail block in a form. I know Oracle's examples tend to use an error handler to trap "No data found" when doing this sort of thing, but I feel an explicit SELECT COUNT(*) is conceptually clearer.

      declare
        nrows number;
      begin
        go_block('ncbi_match_accnumber');
        select count(*) into nrows from ncbi_match_accnumber
          where query# = :ncbi_match_result.query#;
        if (nrows > 0) then
          execute_query;
        else
          clear_block;
        end if;
      end;


2.	PL/SQL to insert a row into a table and execute some triggers.

      declare
        nrows number;
        prefix char;
      begin
        /* Link selected acc# to NCBI acc# if link isn't already recorded */
        select count(*) into nrows from ncbi_matched where
          ncbi_acc# = :ncbi_match_result.ncbi_acc# and
          acc# = :ncbi_match_accnumber.acc#;
        if (nrows = 0) then
          insert into ncbi_matched
          (ncbi_acc#, acc#, date_matched)
          values
          (:ncbi_match_result.ncbi_acc#,:ncbi_match_accnumber.acc#,sysdate);
        end if;

        /* Perform additional processing for EMBL and DDBJ acc#s */
        prefix := substr(:ncbi_match_accnumber.acc#,1,1);
        if (prefix in ('V','W','X','Y','Z')) then
          execute_trigger('get_entryname');
          execute_trigger('add_journal_ref');
          execute_trigger('update_confidential');
          execute_trigger('update_secondary');
        elsif (prefix = 'D') then
          execute_trigger('notify_ddbj');
        end if;
      end;