Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: 8i pl/sql question

RE: 8i pl/sql question

From: Guang Mei <gmei_at_incyte.com>
Date: Tue, 16 Dec 2003 08:09:26 -0800
Message-ID: <F001.005DA17D.20031216080926@fatcity.com> 





Below is the code for myfunction1 in the package, It calls a bunch of other
functions, such as getBlastMatches, escapeGene, genes.gene2protein and
addItem.



Guang



---




type blastMatch is record (

  geneName	gene.name%type,
  percent	number




);



function myfunction1 (seqid in number,

		   sid in number,
		   secondsid in number default -1) return varchar2 is
  tbl	blastMatches;
  cnt	number;
  item	varchar2(256);
  str	varchar2(256);




begin


  cnt := getBlastMatches(seqid, sid, tbl, secondsid);
  if cnt<1 then return null; end if;



  for i in 1..cnt loop


    item := escapeGene(sid, tbl(i).geneName,

		       genes.gene2protein(tbl(i).geneName, sid)) || ' ('
      || tbl(i).percent || '%)';




    if not addItem(str, item, '; ', 80) then exit; end if;
  end loop;


  return str;


exception


  when others then return null;


end myfunction1;



--



function getBlastMatches (seqid in number,

			  spid in number,
			  matchTable out blastMatches,
			  secondspid in number default -1) return number is
  cursor bcur is
	select	queryid, subjid, 100.0*identity/matchlen pct
	  from	blastresults
	 where	((subjspid in (spid,secondspid) and queryid = seqid) or
		(queryspid in (spid,secondspid) and subjid = seqid)) and
		(identity/matchlen >= .200 or positive/matchlen >= .400)
	 order	by blast.pvalToNumber(pval) asc, score desc;
  match		number;
  cnt		number := 0;
  gname		gene.name%type;





begin



  for bmatch in bcur loop


    if bmatch.queryid=seqid then match := bmatch.subjid;
    else match := bmatch.queryid; end if;



    BEGIN


      select name into gname from gene,seqtable
        where geneid=gene.id and aaseqid = match and
		gene.use = 'Y' and seqtable.use='Y';
    EXCEPTION
      when no_data_Found then gname := NULL;


    END;


    if gname is not null then


      cnt := cnt + 1;


      matchTable(cnt).geneName := gname;
      matchTable(cnt).percent := round(bmatch.pct, 0);

      if cnt = maxMatches then return cnt; end if;


    end if;



  end loop;



  return cnt;


exception


  when others then return 0;


end getBlastMatches;





-----Original Message-----



Jamadagni, Rajendra


Sent: Tuesday, December 16, 2003 10:45 AM
To: Multiple recipients of list ORACLE-L




what does myfunction1() do?



Raj






Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !




-----Original Message-----



Sent: Tuesday, December 16, 2003 10:24 AM
To: Multiple recipients of list ORACLE-L




The first argumant (myID) is a variable that is different every time the
function gets called. The second argument is a hard code number (just as in
my orginal message). So I guess I could not use DETERMINISTIC here. I have
not heard of DETERMINISTIC before but I will take a look of this becuase it
probably can be used at a couple of places if it works as you described.
Thanks.



Guang



-----Original Message-----



John Flack


Sent: Tuesday, December 16, 2003 8:44 AM
To: Multiple recipients of list ORACLE-L




Does myFunction1 often get called with the same arguments?  In your example,
the second argument is never repeated, but in the real thing, would the same
second argument be likely to repeat?  If myFunction1 gets the same
arguments, will it always return the same value?  If so, then it is a
deterministic function, and you can declare it as one by putting the keyword
DETERMINISTIC before the IS/AS in its header.  This way the optimiser will
know not to recalculate the function if it is called again with the same
arguments, but will reuse the value it calculated before.



-----Original Message-----



Sent: Monday, December 15, 2003 7:54 PM


To: Multiple recipients of list ORACLE-L




can you return multiple values from a modified version of myfunction(1) ??
If so, then you can replace multiple calls by only one. And no, bulk binds
is only within dml/select statements.



Raj






Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !




-----Original Message-----



Sent: Monday, December 15, 2003 4:34 PM


To: Multiple recipients of list ORACLE-L




Hi:



Oracle 8173 DB.



I have a package funtion, part of it is doing something like this:


  utl_file.put_line(fpn,  myFunction1(myID, 1));
  utl_file.put_line(fpn,  myFunction1(myID, 8));
  utl_file.put_line(fpn,  myFunction1(myID, 6));
  utl_file.put_line(fpn,  myFunction1(myID, 35));
  utl_file.put_line(fpn,  myFunction1(myID, 33));
  utl_file.put_line(fpn,  myFunction1(myID, 7));
  utl_file.put_line(fpn,  myFunction1(myID, 102));
  utl_file.put_line(fpn,  myFunction1(myID, 10));
  utl_file.put_line(fpn,  myFunction1(myID, 9));
  utl_file.put_line(fpn,  myFunction1(myID, 15));
  utl_file.put_line(fpn,  myFunction1(myID, 2));





myFunction1 (returns a varchar2 string) here is another function in the same
package and


it calls a bunch of other functions.



Is there a way to speed up the performance of the above lines by using "bulk
bind"?


I am brain-dead now and can not seem to find if it can be done and/or how it
can be done.



TIA.


Guang



--



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


--



Author: Guang Mei


  INET: gmei_at_incyte.com


Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message


to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).








This e-mail message is confidential, intended only for the named
recipient(s) above and may contain information that is privileged, attorney
work product or exempt from disclosure under applicable law. If you have
received this message in error, or are not the named recipient(s), please
immediately notify corporate MIS at (860) 766-2000 and delete this e-mail
message from your computer, Thank you.




**********5


--



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


--



Author: Jamadagni, Rajendra


  INET: Rajendra.Jamadagni_at_espn.com


Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message


to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 



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


-- 



Author: Guang Mei


  INET: gmei_at_incyte.com


Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message


to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Tue Dec 16 2003 - 10:09:26 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US