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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).