10 Replies Latest reply: Mar 5, 2013 1:21 AM by Aman.... RSS

    Materialized View

    yxes2013
      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
          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
            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
              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
                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
                  The forum server is hanging and very slowwwwwwwww.....do u feel it too? :(
                  • 6. Re: Materialized View
                    sb92075
                    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
                      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
                        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
                          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....
                            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....