a pl/sql question

From: Guang Mei <gmei_at_incyte.com>
Date: Thu, 29 Apr 2004 15:56:57 -0400
Message-ID: <NJEDKDKJDGAKAEKKNEEJMEIADCAA.gmei@incyte.com>

Hi:

I have the following pl/sql function code that gets called a lot in our application. I have looked all the querys and they are running fine (have proper indexes, etc). I am now trying to see if I could optimize this code a bit. One thing I am thinking is that the sub pl/sql block within this function is creating some "overhead". But if I put the code "select abbrev into ..." in the function directly (instead of in pl/sql block) then how could I capture it's exception ("when others then select name into jname from journal where id=jid;")? Please let me know you have some ideas. Thanks.

Guang

• here is the code snip:

begin

    select title, volume, pages, year, journalid     into ttl, vol, pg, yr, jid from ref where id=rn;     begin

      select abbrev into jname from journal2abbrev
      where id=jid and medline='Y';
    exception
      when others then select name into jname from journal where id=jid;

    end;
    str := str || ' ' || getAuthorString(rn,maxAuthLen) || ' ' || ttl || ' ';

    str := str || jname || ' ' || to_char(vol) || ', ' || pg;     str := str || ' (' || to_char(yr) || ').';

    return str;
exception

    when others then return null;
end RefString;

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

---

Received on Thu Apr 29 2004 - 14:58:51 CDT