RE: a pl/sql question
- From: <k.sriramkumar@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 30 Apr 2004 10:31:17 +0530
Hi Guang,
Can you pls trace the session running this code and post the relevant sections
of the output?. The code you have posted here lacks details .I suggest the
problem could be well stated with supporting details like the volume of data in
the tables/selectivity of columns/trace output. Pls elaborate this statement..
<Quote>
I am thinking is that the sub pl/sql block within this function is creating
some "overhead"
<Unquote>
pls explain what ovearhead this statement is causing..Is the SQL statement
causing the overhead on CPU/Disk/Memory/You?
Cheers
Sriram Kumar
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx on behalf of Guang Mei
Sent: Fri 4/30/2004 1:26 AM
To: Oracle-L-freelists
Cc:
Subject: a pl/sql question
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
-- Binary/unsupported file stripped by Ecartis --
-- Type: application/ms-tnef
-- File: winmail.dat
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
