RE: 8i pl/sql question
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: John Flack 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).
RE: 8i pl/sql question
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).
RE: 8i pl/sql question
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).
RE: 8i pl/sql question
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 cnt1 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,
8i pl/sql question
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).
RE: 8i pl/sql question
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).
