4 Replies Latest reply: Mar 22, 2013 2:09 PM by gaverill RSS

    Use of MAKE_REF in Materialized view.

    832009
      Hi All,

      I am trying to make a materialized view of a sql query where MAKE_REF function is used. While creating the materialized view, because of this, I am getting the belo error.

      Error report:
      SQL Error: ORA-22979: cannot INSERT object view REF or user-defined REF
      22979. 00000 - "cannot INSERT object view REF or user-defined REF"

      Can you please suggest what can be done to create the materialized view with same data?

      Thanks in advance !

      Cheers

      Roy
        • 1. Re: Use of MAKE_REF in Materialized view.
          odie_63
          Hi,

          Virtual REFs created via MAKE_REF cannot be inserted in a physical table.

          Are you trying to create an object MV out of relational data ?
          Could you explain your requirement with some sample data ?
          • 2. Re: Use of MAKE_REF in Materialized view.
            832009
            Below is the sample query extracted from the main query.

            this is a view query only . I need to make Materialized view from this. Can you please suggest any alternate way to achieve the same.

            SELECT
            MAKE_REF(TASK_DTLS_v, DECODE(BTV.ROWID_TASK,NULL,'°',BTV.ROWID_TASK) ) TASK,
            MAKE_REF(PROJEKTTYP_v, DECODE(P.ROWIDPROJECT,NULL,'°',P.ROWIDPROJECT) ) PROJECT
            FROM
            EMP_v BT,
            PROJECT_V P,
            TASK_V BTV
            WHERE BT.PSYBAUTEILID = P.PSYBAUTEILID
            AND BTV.PSYBAUTEILVERSIONID = BT.PSYBAUTEILVERSIONID ;
            • 3. Re: Use of MAKE_REF in Materialized view.
              832009
              yes may be you are right. I am trying to make M-view from relational data. As the view contains periodic data so for the performance improvement it is to be converted into m-view.

              can you please suggest any other approach instead of Make_Ref. ?

              Thanks in advance!

              Cheers
              • 4. Re: Use of MAKE_REF in Materialized view.
                gaverill
                "can you please suggest any other approach instead of Make_Ref. ?"

                Just a thought, but can you create a materialized view containing only the needed object view key columns, then create a view on that where you construct your REF columns.

                Gerard