Materialized View and CLOBs

From: Bradley Brown <picksley_at_picksley.com>
Date: Thu, 12 Sep 2002 19:13:07 +0000 (UTC)
Message-ID: <alqp03$rq4$1@paris.btinternet.com>

Here's a question I've been waiting to post on Tom Kyte's most excellent website. Can anybody else assist? This test has been conducted on an Oracle 8.1.7.2 Sun Solaris platform.

Thanks in advance.

Tom,

I have code in our application very similar to that listed below. The problem I'm having is the last row inserted with CLOB data shows up in the table MV_TEST but is not aggregated in the materialized view MV_TEST_SUMMARY. Is there something special I need to do when inserting CLOB data or any other type of LOB data so that materialized view refreshs correctly? I check the Oracle manuals are there are no restrictions listed with regards to LOBs.

Thanks in advance for your help.

Bradley

/*

drop sequence mv_test_seq
/

DROP TABLE mv_test
/

DROP PACKAGE TEST_MV
/

drop materialized view mv_test_summary
/

*/

CREATE SEQUENCE mv_test_seq
  INCREMENT BY 1
  START WITH 1
  MINVALUE 1

  MAXVALUE 999999999999999999999999999

  NOCYCLE
  NOORDER
  CACHE 20
/

CREATE TABLE mv_test

    (id          NUMBER PRIMARY KEY,
     clob_col    CLOB,
     timestamp   DATE DEFAULT SYSDATE,
     ref_col     NUMBER(3),
     username    VARCHAR2(50))

/

CREATE OR REPLACE PACKAGE TEST_MV AS

PROCEDURE INSERT_INTO_MV_TEST(p_clob_col IN VARCHAR2,
                              p_ref_col IN mv_test.ref_col%TYPE,
                              p_username IN mv_test.username%TYPE);

END TEST_MV;
/

CREATE OR REPLACE PACKAGE BODY TEST_MV AS

PROCEDURE INSERT_INTO_MV_TEST(p_clob_col IN VARCHAR2,
                              p_ref_col IN mv_test.ref_col%TYPE,
                              p_username IN mv_test.username%TYPE) IS

l_clob clob;
BEGIN   IF p_clob_col IS NULL THEN

        INSERT INTO mv_test (id,
                             clob_col,
                             timestamp,
                             ref_col,
                             username)
                     VALUES (mv_test_seq.NEXTVAL,
                             p_clob_col,
                             SYSDATE,
                             p_ref_col,
                             p_username);
  ELSE
        INSERT INTO mv_test (id,
                             clob_col,
                             timestamp,
                             ref_col,
                             username)
                     VALUES (mv_test_seq.NEXTVAL,
                             empty_clob(),
                             SYSDATE,
                             p_ref_col,
                             p_username)
        RETURN clob_col into l_clob;

        dbms_lob.open(l_clob, dbms_lob.lob_readwrite);
        dbms_lob.write(l_clob,
                       length(p_clob_col),
                       1,
                       p_clob_col);
        dbms_lob.close(l_clob);

 END IF; END INSERT_INTO_MV_TEST; END TEST_MV;
/

CREATE MATERIALIZED VIEW log
  ON mv_test
  WITH ROWID (id, timestamp, ref_col, username)   INCLUDING NEW VALUES
/

CREATE MATERIALIZED VIEW mv_test_summary   build IMMEDIATE
  REFRESH FAST ON COMMIT AS
  SELECT ref_col,

         username,
         count(*) row_count

    FROM mv_test
GROUP BY ref_col,

         username
/

BEGIN

TEST_MV.INSERT_INTO_MV_TEST('',1,'FRED');
TEST_MV.INSERT_INTO_MV_TEST('',1,'FRED');
TEST_MV.INSERT_INTO_MV_TEST('',1,'FRED');
TEST_MV.INSERT_INTO_MV_TEST('Inserted by Fred',1,'FRED');

END;
/

COMMIT
/

SQL> SELECT * FROM MV_TEST
/

  2

        ID CLOB_COL                       TIMESTAMP    REF_COL USERNAME
---------- ------------------------------ --------- ---------- -------------
-------
         1                                06-SEP-02          1 FRED
         2                                06-SEP-02          1 FRED
         3                                06-SEP-02          1 FRED
         4 Inserted by Fred      06-SEP-02          1 FRED

SQL> SELECT * FROM MV_TEST_SUMMARY
  2 /

   REF_COL USERNAME ROW_COUNT

---------- -------------------- ----------
         1 FRED                          3

Received on Thu Sep 12 2002 - 14:13:07 CDT