Re: Materialized View and CLOBs

Thanks for taking the time to answer

I know that if I'm inserting less than 4,000 bytes I don't need to use the DBMS_LOB routines but at some point we may. After investigation of the logs I too concluded that something doesn't look right, however, I wasn't sure if it was something wrong with my code or perhaps a bug. I looking for some sort of confirmation either way.

"Jusung Yang" <jusungyang_at_yahoo.com> wrote in message news:42ffa8fa.0209122030.704366af_at_posting.google.com...
> 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 Ora
 cle
> > 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 Fri Sep 13 2002 - 04:35:48 CDT