[prev in list] [next in list] [prev in thread] [next in thread]
List: odtug-sqlplus-l
Subject: RE: To be or Not 2B, when should function be deterministic? Plz Hlp ... TIA.
From: "Raymond De Bruyn" <rdebruyn () sympatico ! ca>
Date: 2007-09-26 21:14:50
Message-ID: F001.0061015E.20070926140531 () fatcity ! com
[Download RAW message or body]
<html><div style='background-color:'><DIV class=RTE>
<P>Even if the collection isn't available to all sessions, this can still be quite \
useful.</P> <P>I have code that loops through data that includes foreign \
keys. As created I have to lookup some of these foreign key records while I loop \
through the data set. I have a package for each table, each package has a pl table \
declared as that table%rowtype and a getter function. When the getter function is \
called with a key value, it checks if thePLtable.EXISTS(theKEYvalue). If it does, it \
returns the row. If not, it selects the row, adds it to the pl table and returns \
it.</P> <P>I only do this when the key value repeats (so it's beneficial to \
cache) and for some reason I can't do a table join when I originally get the \
data set. I also know that the number of rows is no more than 200 and it's a web \
application so the sessions are short lived - no baggage lying around.</P> \
<P>Ray<BR><BR></P></DIV> <DIV></DIV>
<BLOCKQUOTE style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #a0c6e5 2px \
solid; MARGIN-RIGHT: 0px"><FONT style="FONT-SIZE: 11px; FONT-FAMILY: \
tahoma,sans-serif"> <HR color=#a0c6e5 SIZE=1>
<DIV></DIV>From: <I>"John Flack" \
<JohnF@smdi.com></I><BR>Reply-To: <I>ODTUG-SQLPLUS-L@fatcity.com</I><BR>To: <I>Multiple \
recipients of list ODTUG-SQLPLUS-L \
<ODTUG-SQLPLUS-L@fatcity.com></I><BR>Subject: <I>RE: To be or Not \
2B, when should function be deterministic? Plz Hlp ... \
TIA.</I><BR>Date: <I>Wed, 26 Sep 2007 10:50:38 -0800</I><BR>No, I haven't \
tested performance, but we've never had much trouble \
with<BR>performance. This isn't really for performance, it is really for \
ease<BR>of development. But I have had a few instances where I've used \
a<BR>vc_arraytype as a bind variable, where the alternative would be \
to<BR>concatinate the string into a dynamic SQL command - this can protect \
you<BR>from SQL Injection, and ought to improve performance - though I don't<BR>have \
the stats to back this up.<BR><BR>-----Original Message-----<BR>Berthoff, \
Tom<BR>Sent: Wednesday, September 26, 2007 2:21 PM<BR>To: Multiple recipients of list \
ODTUG-SQLPLUS-L<BR>Hlp ... TIA.<BR><BR>John,<BR><BR>Have you done any timed tests \
using the collection approach? Does it<BR>perform better than direct queries of small \
data sets?<BR><BR>Tom<BR><BR>-----Original Message-----<BR>John Flack<BR>Sent: \
Wednesday, September 26, 2007 2:01 PM<BR>To: Multiple recipients of list \
ODTUG-SQLPLUS-L Hlp ... TIA.<BR><BR>When I say "return a collection", I don't \
necessarily mean a PL/SQL<BR>collection. There are distinct advantages in \
creating a collection<BR>TYPE and returning that:<BR><BR>CREATE TYPE vc_arraytype AS \
TABLE OF VARCHAR2(4000);<BR><BR>FUNCTION lookup_table (p_table_name VARCHAR2) RETURN \
vc_arraytype IS<BR> r_lookup_table vc_arraytype;<BR>BEGIN<BR> \
SELECT code_vals BULK COLLECT INTO r_lookup_table FROM my_lookups<BR>WHERE \
table_name = UPPER(p_table_name);<BR> RETURN r_lookup_table;<BR>END \
lookup_table;<BR><BR>Now you can treat the function as a table:<BR> \
SELECT * FROM TABLE(lookup_table('my_lookup')); I often use this in an<BR>IN \
clause:<BR> WHERE code_val IN (SELECT * FROM \
TABLE(lookup_table('my_lookup')))<BR>This is more precise than returning a string and \
saying:<BR> WHERE INSTR(lookup_table('my_lookup'),code_val) > 0 What \
if possible<BR>code values are 1-10? An INSTR for '1' will find the \
'10'. Wrong.<BR><BR>I've found my vc_arraytype collection type to be so \
useful, that I'm<BR>writing an article about it for the ODTUG Technical \
Journal.<BR><BR>-----Original Message-----<BR>Michelle, Suzanne<BR>Sent: Wednesday, \
September 26, 2007 12:11 PM<BR>To: Multiple recipients of list ODTUG-SQLPLUS-L Hlp \
... TIA.<BR><BR>Very interesting idea, that, the associative array ... can \
one<BR>compilation be made available to ALL users? or just in each user's<BR>session? \
I am not sure a collection would be useful. We don't then parse<BR>out the compiled \
string, we just compare against it. There are many<BR>levels to how these output \
strings (of flattened LOVs) are used, it's<BR>few values and the whole database, with \
about 12 years of data has less<BR>than 1 million rows in all the tables put together \
(we're just beginning<BR>to think about partitioning) ... so ... I think I'd get more \
bang out of<BR>the associative array buck ...<BR><BR>On the forms' side (ver 6i), if \
a record has a particular status code,<BR>then subsequent actions are allowed / \
disallowed ... e.g., a plan that<BR>is canceled has two considerations ... because it \
is canceled, it is<BR>finished, and thus follows the finished status rules, but ... \
because it<BR>is canceled, it ALSO has other special rules that apply. We \
have<BR>complicated sets of data that get counted by group X but not by group \
Y<BR>and only if it is raining on Tuesdays. It becomes easiest to just say<BR>"is my \
status code in <some> string" (INSTR > 0) then do whatever else<BR>instead \
do something else or nothing or warn the forms' user that<BR><something> bad | \
good has occurred, etc.<BR><BR>Suzanne (@ 2Bwy A13.32)<BR>desk: 646-252-8663, cell: \
347-907-1125<BR><BR>-----Original Message-----<BR>John Flack<BR>Sent: Tuesday, \
September 25, 2007 11:05 PM<BR>To: Multiple recipients of list ODTUG-SQLPLUS-L Hlp \
... TIA.<BR><BR>Because the data returned by the function comes from the database, it \
is<BR>not deterministic. Even though the database does not change very \
often,<BR>it COULD change at any time. However, I have often done things \
like<BR>this:<BR>CREATE PACKAGE BODY my_pkg IS<BR> last_input \
VARCHAR2(80) := 'NOTHING';<BR> last_output VARCHAR2(80);<BR>FUNCTION \
my_fun (in_parm IN VARCHAR2) RETURN VARCHAR2 IS<BR> out_var \
VARCHAR2(80);<BR>BEGIN<BR> IF in_parm = last_input \
THEN<BR> out_var := last_output;<BR> \
ELSE<BR> SELECT my_data INTO out_var FROM my_tab WHERE my_key \
= in_parm;<BR> last_input := \
in_parm;<BR> last_output := out_var;<BR> END \
IF;<BR> RETURN out_var;<BR>END my_fun;<BR>END my_pkg;<BR>This way, it \
caches the last result and doesn't have to re-query the<BR>database unless the input \
changes. I've even used an associative array<BR>to cache an entire lookup \
table when the package is first called.<BR><BR>By the way, why return a set of \
values as a string? You have to parse<BR>the string to get the individual \
values. Why not return them as \
a<BR>collection?<BR><BR>________________________________<BR><BR>Sent: Tue 9/25/2007 \
8:15 PM<BR>To: Multiple recipients of list ODTUG-SQLPLUS-L .. TIA.<BR><BR><BR><BR>I \
have a function that calcs a set of values and returns them as a<BR>string.<BR><BR>It \
modifies no data, it takes as a single parameter, specifically,<BR>VarChar2, which \
identifies the specific list of values a calling program<BR>or form needs to evaluate \
an action, e.g., the final status codes a<BR>Service Plan can have, a set of \
cancellation codes for said Service<BR>Plans, or other Diversion Requests or other \
lists of values for other<BR>purposes. If you want to know what lists are available, \
"ALL" passed in<BR>will give the caller the set of lists.<BR><BR>Some of these list \
value sets come from tables, others are hard coded<BR>(and will eventually come from \
tables, hence delivery is being hidden by<BR>the function).<BR><BR>My understanding \
of 'deterministic' is "same value in" yields "same<BR>value out" ... So, what will \
happen if lists come from tables, and then<BR>any given LOV gets a new value? Will \
the function re-evaluate itself and<BR>memory be refreshed? Is it just for any one \
session, or is it for all<BR>sessions? (these values could be for all sessions, as \
they are rules for<BR>all the data, not just user X or Role Y or Data Q). So, can \
said<BR>LOV-flattening function be deterministic, and, if so, will it \
reevaluate<BR>itself if one of the list-driving tables' values is changed in some \
way?<BR>OR can it be forced to change / reevaluate itself via trigger, \
maybe?<BR><BR>Such data changes are not going to happen often (we've had the \
pretty<BR>much the same status codes for 10 yrs, know our data well, but now \
are<BR>aggregating it / counting it / checking it in new ways, the status \
codes<BR>help us sort / count / validate its relative stages).<BR><BR>[NB: Right now, \
while the lists are hard-coded, value change doesn't<BR>happen at all (well, not \
much, we add a new list, but not new values to<BR>existing lists), but oh, boy, wait \
until I allow the lists to be<BR>spec'd!! I'm getting tired of hard coding these \
lists ... which are<BR>essentially criteria for "other things will happen depending \
on if my<BR>status code belongs to a particular list." I want to make up lists as \
I<BR>need them for whatever the next task is. Maybe I should have asked this<BR>on \
the Rules List?]<BR><BR>--<BR>For more information on this topic or to become a \
member, visit our web<BR>site at http://www.ODTUG.com Be sure to check \
out our Seriously<BR>Practical (SP) Conferences coming up this year!<BR><BR>OPP2007 \
East - Oracle PL/SQL Training - September 18-19, \
2007<BR>http://www.odtugopp.com<BR><BR>--<BR>Author: John Flack<BR> INET: \
JohnF@smdi.com<BR><BR>Fat City Hosting, San Diego, California -- \
http://www.fatcity.com<BR>---------------------------------------------------------------------<BR>To \
REMOVE yourself from this mailing list, send an E-Mail message<BR>to: \
ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the<BR>message BODY, \
include a line containing: UNSUB ODTUG-SQLPLUS-L (or the<BR>name of mailing list you \
want to be removed from). You may also send<BR>the HELP command for other \
information (like subscribing).<BR><BR><BR>IMPORTANT: The information contained in \
this email and/or its<BR>attachments is confidential. If you are not the intended \
recipient,<BR>please notify the sender immediately by reply and immediately \
delete<BR>this message and all its attachments. Any review, use, \
reproduction,<BR>disclosure or dissemination of this message or any attachment by \
an<BR>unintended recipient is strictly prohibited. Neither this message \
nor<BR>any attachment is intended as or should be construed as an \
offer,<BR>solicitation or recommendation to buy or sell any security or \
other<BR>financial instrument. Neither the sender, his or her employer nor \
any<BR>of their respective affiliates makes any warranties as to the<BR>completeness \
or accuracy of any of the information contained herein or<BR>that this message or any \
of its attachments is free of viruses.<BR><BR>--<BR>For more information on this \
topic or to become a member, visit our web<BR>site at \
http://www.ODTUG.com Be sure to check out our Seriously<BR>Practical (SP) \
Conferences coming up this year!<BR><BR>OPP2007 East - Oracle PL/SQL Training - \
September 18-19, 2007<BR>http://www.odtugopp.com<BR><BR>--<BR>Author: Berthoff, \
Tom<BR> INET: Tom.Berthoff@sig.com<BR><BR>Fat City Hosting, San Diego, \
California -- http://www.fatcity.com<BR>---------------------------------------------------------------------<BR>To \
REMOVE yourself from this mailing list, send an E-Mail message<BR>to: \
ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the<BR>message BODY, \
include a line containing: UNSUB ODTUG-SQLPLUS-L (or the<BR>name of mailing list you \
want to be removed from). You may also send<BR>the HELP command for other \
information (like subscribing).<BR><BR>--<BR>For more information on this topic or to \
become a member, visit our web site at http://www.ODTUG.com Be sure to \
check out our Seriously Practical (SP) Conferences coming up this \
year!<BR><BR>OPP2007 East - Oracle PL/SQL Training - September 18-19, \
2007<BR>http://www.odtugopp.com<BR><BR>--<BR>Author: John Flack<BR> INET: \
JohnF@smdi.com<BR><BR>Fat City Hosting, San Diego, California -- \
http://www.fatcity.com<BR>---------------------------------------------------------------------<BR>To \
REMOVE yourself from this mailing list, send an E-Mail message<BR>to: \
ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in<BR>the message BODY, \
include a line containing: UNSUB ODTUG-SQLPLUS-L<BR>(or the name of mailing list you \
want to be removed from). You may<BR>also send the HELP command for other \
information (like subscribing).<BR></FONT></BLOCKQUOTE></div></html>
--
For more information on this topic or to become a member, visit our web site at \
http://www.ODTUG.com Be sure to check out our Seriously Practical (SP) Conferences \
coming up this year!
OPP2007 East - Oracle PL/SQL Training - September 18-19, 2007
http://www.odtugopp.com
--
Author: Raymond De Bruyn
INET: rdebruyn@sympatico.ca
Fat City Hosting, San Diego, California -- http://www.fatcity.com
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ODTUG-SQLPLUS-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).
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic