ORA-30553: The function is not deterministic
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: ORA-30553: The function is not deterministic

Hybrid View

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    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"????

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Hmmm.

    What's the code in the UPPER_FUNC() definition?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    And how,pray tell, do I do that?

    Been around a bit and thats the first I've heard/read about that Dave!

  7. #7
    Join Date
    May 2002
    Posts
    2,645
    Welcome to Dave's world.

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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
  •  


Click Here to Expand Forum to Full Width