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

From: Giovanni Azua <bravegag_at_hotmail.com>
Date: Thu, 19 Dec 2002 16:56:16 +0100
Message-ID: <atsq9h$26l8g$1@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 - 09:56:16 CST