RE: a pl/sql question

From: <k.sriramkumar_at_iflexsolutions.com>
Date: Fri, 30 Apr 2004 10:31:17 +0530
Message-ID: <10898BE7CA96D611988B000802255AAF03427DED@FMGRT.rt.i-flex.com>

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_at_freelists.org 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_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
	-----------------------------------------------------------------
	

• 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_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 - 23:54:49 CDT