Oracle CLOB column and LAG

user_2765624 0
Viewed 971

I'm facing a problem when I try to use LAG function on CLOB column.

So let's assume we have a table

create table test (
    id number primary key, 
    not_clob varchar2(255),
    this_is_clob clob
);

insert into test values (1, 'test1', to_clob('clob1'));
insert into test values (2, 'test2', to_clob('clob2'));

DECLARE
x CLOB := 'C';
BEGIN

 FOR i in 1..32767
 LOOP
  x := x||'C';
 END LOOP;

 INSERT INTO test(id,not_clob,this_is_clob) values(3,'test3',x);

END;
/

commit;

Now let's do a select using non-clob columns

select id, lag(not_clob) over (order by id) from test;

It works fine as expected, but when I try the same with clob column

select id, lag(this_is_clob) over (order by id) from test;

I get 

ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:
Error at Line: 1 Column: 16

Can you tell me what's the solution of this problem as I couldn't find anything on that.

oracle11g oracle
3 Answers

Some of the features may not work properly in SQL when using CLOBs(like DISTINCT , ORDER BY GROUP BY etc. Looks like LAG is also one of them but, I couldn't find anywhere in docs.

If your values in the CLOB columns are always less than 4000 characters, you may use TO_CHAR

select id, lag( TO_CHAR(this_is_clob)) over (order by id) from test;

OR

convert it into an equivalent SELF JOIN ( may not be as efficient as LAG )

SELECT a.id,
       b.this_is_clob AS lagging
FROM test a
LEFT JOIN test b ON b.id < a.id;

Demo

user_79985910

The documentation says the argument for any analytic function can be any datatype but it seems unrestricted CLOB is not supported.

However, there is a workaround:

select id, lag(dbms_lob.substr(this_is_clob, 4000, 1)) over (order by id) 
from test;

This is not the whole CLOB but 4k should be good enough in many cases.

I'm still wondering what is the proper way to overcome the problem

Is upgrading to 12c an option? The problem is nothing to do with CLOB as such, it's the fact that Oracle has a hard limit for strings in SQL of 4000 characters. In 12c we have the option to use extended data types (providing we can persuade our DBAs to turn it on!). Find out more.

user_1463250

I know this is an old question, but I think I found an answer which eliminates the need to restrict the CLOB length and wanted to share it. Utilizing CTE and recursive subqueries, we can replicate the lag functionality with CLOB columns.

First, let's take a look at my "original" query:

WITH TEST_TABLE AS
(
 SELECT LEVEL ORDER_BY_COL,
        TO_CLOB(LEVEL) AS CLOB_COL
 FROM DUAL
 CONNECT BY LEVEL <= 10
)
SELECT tt.order_by_col,
       tt.clob_col,
       LAG(tt.clob_col) OVER (ORDER BY tt.order_by_col)
FROM test_table tt;

As expected, I get the following error:

ORA-00932: inconsistent datatypes: expected - got CLOB

Now, lets look at the modified query:

WITH TEST_TABLE AS
(
 SELECT LEVEL ORDER_BY_COL,
        TO_CLOB(LEVEL) AS CLOB_COL
 FROM DUAL
 CONNECT BY LEVEL <= 10
),
initial_pull AS
(
  SELECT tt.order_by_col,
         LAG(tt.order_by_col) OVER (ORDER BY tt.order_by_col) AS PREV_ROW,
         tt.clob_col
  FROM test_table tt
),
recursive_subquery (order_by_col, prev_row, clob_col, prev_clob_col) AS
(
  SELECT ip.order_by_col, ip.prev_row, ip.clob_col, NULL
  FROM initial_pull ip
  WHERE ip.prev_row IS NULL
  UNION ALL
  SELECT ip.order_by_col, ip.prev_row, ip.clob_col, rs.clob_col
  FROM initial_pull ip
  INNER JOIN recursive_subquery rs ON ip.prev_row = rs.order_by_col
)
SELECT rs.order_by_col, rs.clob_col, rs.prev_clob_col
FROM recursive_subquery rs;

So here is how it works.

  1. I create the TEST_TABLE, this really is only for the example as you should already have this table somewhere in your schema.
  2. I create a CTE of the data I want to pull, plus a LAG function on the primary key (or a unique column) in the table partitioned and ordered in the same way I would have in my original query.
  3. Create a recursive subquery using the initial row as the root and descending row by row joining on the lagged column. Returning both the CLOB column from the current row and the CLOB column from its parent row.
user_22997600
Related