This discussion is archived
10 Replies Latest reply: Mar 4, 2013 11:21 PM by Aman.... RSS

Materialized View

yxes2013 Newbie
Currently Being Moderated
Hi All,

10.2.0.4


I am creating a materialized view with query rewrite, but it failed on a column conversion. How do I make work around for this column please....
 TO_CHAR(DECODE (SALARY, 0, 0, TRUNC (COMM / INCENT * 100, 2) ),'FM999.00') turnout,
                      *
ERROR at line 7:
ORA-30357: this PL/SQL function cannot be supported for query rewrite
I thank you all,

zxy
  • 1. Re: Materialized View
    CKPT Guru
    Currently Being Moderated
    yxes2013 wrote:
    Hi All,

    10.2.0.4


    I am creating a materialized view with query rewrite, but it failed on a column conversion. How do I make work around for this column please....
    TO_CHAR(DECODE (SALARY, 0, 0, TRUNC (COMM / INCENT * 100, 2) ),'FM999.00') turnout,
    *
    ERROR at line 7:
    ORA-30357: this PL/SQL function cannot be supported for query rewrite
    I thank you all,

    zxy
    Here is one example, take a look http://www.jusungyang.com/DWfolder/MaterializedViews/FunctionAndQueryRewrite.txt
  • 2. Re: Materialized View
    asahide Expert
    Currently Being Moderated
    As you may know,
    Advanced Query Re-write manual is here.
    <<http://docs.oracle.com/cd/B19306_01/server.102/b14223/qradv.htm>>


    Regards,
  • 3. Re: Materialized View
    yxes2013 Newbie
    Currently Being Moderated
    ORA-30357:
    this PL/SQL function cannot be supported for query rewrite
    Cause:      The statement referenced a PL/SQL function that is not marked DETERMINISTIC.
    Action:      Perform one of the following actions:
    - Remove the use of the PL/SQL function.
    
    - Mark the PL/SQL function as DETERMINISTIC.
    
    - Disable the REWRITE option on the materialized view. The function should be marked DETERMINISTIC only if it always returns the same result value for any given set of input argument values, regardless of any database state or session state. Do not mark the function as DETERMINISTIC if it has any meaningful side-effects.
    How can I make the functional column - DETERMINISTIC?
  • 4. Re: Materialized View
    yxes2013 Newbie
    Currently Being Moderated
    Here is one example, take a look http://www.jusungyang.com/DWfolder/MaterializedViews/FunctionAndQueryRewrite.txt
    I can not understand this example dear :(

    Can you just give me workaround on how to fix this?
    TO_CHAR(DECODE (SALARY, 0, 0, TRUNC (COMM / INCENT * 100, 2) ),'FM999.00') turnout,
    *
    ERROR at line 7:
    ORA-30357: this PL/SQL function cannot be supported for query rewrite
    What is the purpose of query rewrite anyway? :(
    If I remove it, what will happen to my materialized view?


    Thanks a lot,
  • 5. Re: Materialized View
    yxes2013 Newbie
    Currently Being Moderated
    The forum server is hanging and very slowwwwwwwww.....do u feel it too? :(
  • 6. Re: Materialized View
    sb92075 Guru
    Currently Being Moderated
    yxes2013 wrote:
    The forum server is hanging and very slowwwwwwwww.....do u feel it too? :(
    UNBREAKABLE Oracle strikes again
  • 7. Re: Materialized View
    Shivananda Rao Guru
    Currently Being Moderated
    Hello,
    What is the purpose of query rewrite anyway? :(
    Refer this http://docs.oracle.com/cd/B10500_01/server.920/a96520/qr.htm


    Regards,
    Shivananda
  • 8. Re: Materialized View
    yxes2013 Newbie
    Currently Being Moderated
    maybe they are using breakable weendoz :D

    I suggest they use "android" OS on their servers ;)

    Edited by: yxes2013 on 4.3.2013 18:51
  • 9. Re: Materialized View
    yxes2013 Newbie
    Currently Being Moderated
    ooops my apologies, I think the problem is with our network thats why its very slow. I am downloaded a big file like 2Gb :D
    The network admin rebooted our network server, not telling him I am the culprit.
    I just heard him blaming someone downloading big files.
  • 10. Re: Materialized View
    Aman.... Oracle ACE
    Currently Being Moderated
    yxes2013 wrote:
    ooops my apologies, I think the problem is with our network thats why its very slow. I am downloaded a big file like 2Gb :D
    The network admin rebooted our network server, not telling him I am the culprit.
    I just heard him blaming someone downloading big files.
    You shouldn't had mentioned here too! You never know that he is going to come looking for some answers on an Oracle foum ;-) .

    Aman....

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points