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

Home -> Community -> Usenet -> c.d.o.server -> Re: about FBI.. don't panic please ;-)

Re: about FBI.. don't panic please ;-)

From: TimC <ITSNOTCornwell_at_cs.cornell.eduUNLESSYouSaySo>
Date: Thu, 19 Dec 2002 13:19:21 -0500
Message-ID: <att2jt$m95$1@news01.cit.cornell.edu>


Not certain, but can't you declare a function as deterministic? Perhaps this is all you need.

ie. > ) RETURN VARCHAR2 DETERMINISTIC...

Tim

"Giovanni Azua" <bravegag_at_hotmail.com> wrote in message news:atsq9h$26l8g$1_at_ID-114658.news.dfncis.de...
> Hello all,
>
> I was refering in a previous post about having a very
> compound primary key (made up of 6 columns). Well
> given that fact I found easier to make a PL/SQL function
> for converting that 6 columns tuple into a string that could
> be more easily manipulated e.g. passed back/forth as parameter
> between PL/SQL and the middle tier Java Web App. Also
> helping make more maintainable and readable PL/SQL code
> (a pity there are no macros #defines ;-))
>
> Since I can imagine that calling my function for getting the string key
> will force a complete table scan when it appears in the where clauses
> I was trying to create a FBI and the result is this weird message
> saying I provided a non-deterministic function:
>
> CREATE INDEX PURCHASEKEY
> ON PURCHASE_PRODUCT
>

(FOGFC_GETPURCHASEKEY(PERSON_ID,BRANCH_ID,PRODUCT_ID,PURCHASE_DATE,SIZE_ID,C
> OLOR_ID));
>
> The following error has occurred:
> ORA-30553: The function is not deterministic
> (where is the non-determinism of my function???)
>
> CREATE OR REPLACE FUNCTION Fogfc_Getpurchasekey (
> /* IN parameter */
> ipPERSON_ID IN NUMBER
> ,ipBRANCH_ID IN NUMBER
> ,ipPRODUCT_ID IN NUMBER
> ,ipPURCHASE_DATE IN DATE
> ,ipSIZE_ID IN NUMBER
> ,ipCOLOR_ID IN NUMBER
> ) RETURN VARCHAR2
> AS
> BEGIN
>
> RETURN TO_CHAR(ipPERSON_ID) || '_'
> || TO_CHAR(ipBRANCH_ID) || '_'
> || TO_CHAR(ipPRODUCT_ID) || '_'
> || TO_CHAR(ipPURCHASE_DATE, 'DDMMYYYY') || '_'
> || TO_CHAR(ipSIZE_ID) || '_'
> || TO_CHAR(ipCOLOR_ID);
>
> END Fogfc_Getpurchasekey;
> /
>
> TIA,
> Best Regards,
> Giovanni
>
>
Received on Thu Dec 19 2002 - 12:19:21 CST

Original text of this message

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