4 Replies Latest reply: Sep 23, 2013 3:29 AM by Ramin Hashimzadeh RSS

    Materialized View based on stored procedure data

    Michael Tsilikidis

      Hello everyone,

       

      is it possible to base a Materialized View on results returned from a stored procedure?

      If not, do you see any other way except of filling a table with data from the stored procedure and then basing the MV on it?

       

      Thanks in advance.

        • 1. Re: Materialized View based on stored procedure data
          Ramin Hashimzadeh

          Hi Michael,

          First  you must know that Stored Procedure can not return any value, if you mean create mview based on function return value, answer is YES you can create mview:

           

          create or replace function test return number is
          begin
            RETURN 5;
          END;
          create materialized view mv2 as
          select test from dual;

           

          ----

          Ramin Hashimzade

          • 2. Re: Materialized View based on stored procedure data
            Michael Tsilikidis

            Sorry I didn't express myself correctly. The procedure actually doesn't return a value, it fills one of its parameters (of ref cursor type) with data. That's what I meant.

            • 3. Re: Materialized View based on stored procedure data
              BluShadow

              MichaelTsilikidis wrote:

               

              Sorry I didn't express myself correctly. The procedure actually doesn't return a value, it fills one of its parameters (of ref cursor type) with data. That's what I meant.

               

              No it doesn't.

               

              Ref cursors do not store data, so that's not possible.

              A ref cursor is just a pointer to a query... and you cannot "select from" a ref cursor, you can only fetch from an open one.

               

              PL/SQL 101 : Understanding Ref Cursors

               

              As correctly pointed out above, you cannot query from a procedure, only from a function, and a materialized view is based on a query, so you cannot use a procedure in a materialized view.

              You could use a pipelined function instead of a procedure, or have the pipelined function obtain the results from the procedure and pipeline them, and then the materialized view could be based on the results of that pipelined function.

               

              example of pipelined function:

               

              SQL> CREATE OR REPLACE TYPE num_descript AS OBJECT(num number, descript varchar2(30))
                2  /

              Type created.

              SQL>
              SQL> CREATE OR REPLACE TYPE tbl_num_descript AS TABLE OF num_descript
                2  /

              Type created.

              SQL>
              SQL>
              SQL> CREATE OR REPLACE PACKAGE reftest AS
                2    FUNCTION pipedata(p_choice number) RETURN tbl_num_descript PIPELINED;
                3  END;
                4  /

              Package created.

              SQL>
              SQL> CREATE OR REPLACE PACKAGE BODY reftest AS
                2    FUNCTION pipedata(p_choice number) RETURN tbl_num_descript PIPELINED IS
                3      v_obj num_descript := num_descript(NULL,NULL);
                4      v_rc  sys_refcursor;
                5    BEGIN
                6      IF p_choice = 1 THEN
                7        OPEN v_rc FOR SELECT empno as num, ename as descript FROM emp;
                8      ELSIF p_choice = 2 THEN
                9        OPEN v_rc FOR SELECT deptno as num, dname as descript FROM dept;
              10      END IF;
              11      LOOP
              12        FETCH v_rc INTO v_obj.num, v_obj.descript;
              13        EXIT WHEN v_rc%NOTFOUND;
              14        PIPE ROW(v_obj);
              15      END LOOP;
              16      CLOSE v_rc;
              17      RETURN;
              18    END;
              19  END;
              20  /

              Package body created.

               

              SQL> select * from table(reftest.pipedata(1));

               

                     NUM DESCRIPT
              ---------- ------------------------------
                    7369 SMITH
                    7499 ALLEN
                    7521 WARD
                    7566 JONES
                    7654 MARTIN
                    7698 BLAKE
                    7782 CLARK
                    7788 SCOTT
                    7839 KING
                    7844 TURNER
                    7876 ADAMS
                    7900 JAMES
                    7902 FORD
                    7934 MILLER

              14 rows selected.

               

              SQL> select * from table(reftest.pipedata(2));

               

                     NUM DESCRIPT
              ---------- ------------------------------
                      10 ACCOUNTING
                      20 RESEARCH
                      30 SALES
                      40 OPERATIONS

              • 4. Re: Materialized View based on stored procedure data
                Ramin Hashimzadeh

                MichaelTsilikidis wrote:

                 

                Sorry I didn't express myself correctly. The procedure actually doesn't return a value, it fills one of its parameters (of ref cursor type) with data. That's what I meant.

                No you can not. As Blue pointed above , you can use PIPELINED function to create mview based on