Re: Materialized View and CLOBs

I only have a partial answer to this question. Tom is a busy guy, hopefully he (or someone else) will be able to eventually provide a complete answer.

First off, it would work if you don't use the DBMS_LOB routine, instead just use a simple insert to populate clob field.

Now to the question. Normally, this is how it works: 1. When you do an insert into the master table, a record is created in the MV log indicating this is a new record with "insert" DML type. 2. When you do an update on a record in the master table, TWO records are created in the MV log - both indicating "update" DML type, one records the old value the other new.

The way you insert data into clob field, with DBMS_LOB routine, mv log does not appear to be capturing all the necessary info needed for refreshing a MV.
1. The insert SQL using empty_clob() creates a "insert" record in the mv log.
2. The dbms_lob.write SQL is essentially an update to the record that you just inserted with the empty_clob().

Something in the mv log does not look right after step 2. You don't see 2 records in the MV log as you normally would. You see just 1. My guess is that this is why the MV is not refreshed correctly. Is this a bug? I don't know.

"Bradley Brown" <picksley_at_picksley.com> wrote in message news:<alqp03$rq4$1_at_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 - 23:30:28 CDT