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