Try using a temporary clob, populate it with data and insert right away
without having to insert empty_clob(), select it for update, update and
put back. DBMS_LOB.createTemporary() will create you a new temporary
clob which you can then populate using DBMS_LOB.write or .writeAppend
the same way you do for permanent LOBs. When you insert it, you'll
get permanent copy and hopefully will have mv refreshed at the same time.
Don't forget to discard temp clob when it's no longer needed using
DBMS_LOB.freeTemporary() to save resources (or use dbms_lob.call
duration when creating it so that it is automatically discarded when call is
complete.)
--
Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"Bradley Brown" <picksley_at_picksley.com> wrote in message news:alsbhj$6gb$1_at_paris.btinternet.com...
> 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 - 05:24:26 CDT
