-
ORA-30553: The function is not deterministic
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!
-
-
Yes indeed ... welcome.
Here is the documentation on DETERMINISTIC ...
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).
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|