Materialized View and CLOBs
Bradley Brown
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
Jusung Yang
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" <pick...@picksley.com> wrote in message news:<alqp03$rq4$1...@paris.btinternet.com>...
Bradley Brown
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" <jusun...@yahoo.com> wrote in message
news:42ffa8fa.02091...@posting.google.com...
Vladimir M. Zakharychev
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 (b...@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" <pick...@picksley.com> wrote in message news:alsbhj$6gb$1...@paris.btinternet.com...
Jonathan Lewis
Somewhere in the manuals (possibly the
Pl/SQL supplied packages) I recall seeing
an explicit note to the effect that when you
use the dbms_lob package, triggers on the
table do not fire. Since MV logs are maintained
through triggers (albeit system internal ones)
this would explain one part of the problem if
my memory isn't playing tricks on me.
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )
____England______September 24/26, November 12/14
____USA__________November 7/9 (MI), 19/21 (TX)
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Bradley Brown wrote in message ...