Is it possible to use CLOB with Materialized Views

Question

In my project we have used a table with CLOB column in Materialized Views by converting it using TO_CHAR so the resulting column is a VARCHAR2 with size of 4000 characters.

MAX(CASE WHEN BONG_NARR_TY = 'ADTX' AND TO_CHAR(BONG_NARR_TX) IS NOT NULL THEN BONG_NARR_TX END ) AS ADTX_OP_NARR,

Is it possible to have more than 4000 character in the view because when I skip

TO_CHAR
MAX(CASE WHEN BONG_NARR_TY = 'ADTX' AND BONG_NARR_TX IS NOT NULL THEN BONG_NARR_TX END ) AS ADTX_OP_NARR,

I get the following error.

SQL Error: ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 -  "inconsistent datatypes: expected %s got %s"

The size of CLOB is 8Gb but because of View I'm restricted to 4Kb :)

I don't think you can TO_CHAR() a clob if the CLOB is too big for a VARCHAR2. — WW., Jul 03 '15 at 07:07
try some typecasting here. Also, you're checking if CLOB is null, without a coonversion, but you also have a CASE where you compare it to 'ADTX' Give the whole statement and I can explain better. — g00dy, Jul 03 '15 at 07:10
What are you trying to accomplish with this query? Why are you trying to use `MAX` with a `CLOB` column (which - as Jon-Tofte Hansen pointed out in their answer) is not possible) ? — Frank Schmitt, Jul 03 '15 at 07:42

score 2 · Answer 1 · answered Jul 03 '15 at 07:37

2

You cannot use CLOB in MAX(). You can use CLOB in a MATERIALIZED VIEW.

answered Jul 03 '15 at 07:37

Jon Tofte-Hansen

794
6
16

score 1 · Answer 2 · answered Jul 03 '15 at 07:51

As was already pointed out, you cannot use MAX() with a CLOB column.

If your CLOB values differ in the first 4000 characters, you can use DBMS_LOB.SUBSTR() to extract this as a string, sort by this value, and return the CLOB for this.

Example (using the windowing function ROW_NUMBER() to get a ranking for the rows):

create table test(
 pk number not null primary key,
 bong_narr_ty varchar2(10) not null,
 bong_narr_tx CLOB
);

insert into test(pk, bong_narr_ty, bong_narr_tx)
  values(1, 'ADTX', 'A');
insert into test(pk, bong_narr_ty, bong_narr_tx)
  values(2, 'ADTX', 'B');

create materialized view mv_test 
build immediate
as 
select bong_narr_tx as adtx_op_narr
from
  (select t.*, row_number() over (
      partition by 1 order by dbms_lob.substr(bong_narr_tx, 4000, 1) desc) as rn
   from test t
  )
where rn=1;

I found out: According to http://stackoverflow.com/questions/10331912/performance-of-substr-on-clob `DBMS_LOB.SUBSTR()` performs better. — Jon Tofte-Hansen, Jul 03 '15 at 08:10

Is it possible to use CLOB with Materialized Views

2 Answers2