What, exactly, does it mean?
I was trying to create a function based index using a function that I defined. I got this error.
Now I can guess as to what it means - but what exactly is the database telling me?
"Deterministic"????
Printable View
What, exactly, does it mean?
I was trying to create a function based index using a function that I defined. I got this error.
Now I can guess as to what it means - but what exactly is the database telling me?
"Deterministic"????
I bet you used "sysdate", or "user" in the function.
It means that the value returned by the function is not entirely determined by the inputs to the function.
No actually!
It was a functuion that returned the upper case version of a string.
Why not use UPPER I hear you cry! Well, I did. I just wanted to see if using a user-defined function would work just as well. It didn't.
The function accepted a VARCHAR2 string and had a body...
RETURN (UPPER(string));
Works fine in SQLPlus using
SELECT UPPER_FUNC('lowercase') FROM dual;
Returns> LOWERCASE
But when creating the index based on this function I get an error.
Hmmm.
What's the code in the UPPER_FUNC() definition?
By the way, did you declare the UPPER_FUNC() function to be deterministic? Could be that the function based index code is looking for that - it won't determine itself whether the function is deterministic, it needs the function itself to say so.
And how,pray tell, do I do that?
Been around a bit and thats the first I've heard/read about that Dave!
Welcome to Dave's world.
Yes indeed ... welcome.
Here is the documentation on DETERMINISTIC ...
Quote:
You must specify this keyword if you intend to call the function in the expression of a function-based index or from the query of a materialized view that is marked REFRESH FAST or ENABLE QUERY REWRITE.
I was blind and now I can see...!
Muchas Gracias.
JMac
(Now calling myself a Datawarehouse Trainee Jedi - a term of my own invention).