Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: about FBI.. don't panic please ;-)
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