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

Oracle doesn't check whether a function is deterministic or not. You have to tell it that it is (even if you are lying through your teeth -because it will still believe you!).

Off the top of my head, your function needs to go like....

create function blah (parameters)
deterministic
as begin... etc etc

Regards
HJR "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 - 14:40:49 CST