Oracle CLOB列和延迟

Question

当我尝试在LAG列上使用CLOB函数时，我面临一个问题。

假设我们有一张桌子

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;

现在，让我们使用非阻塞列进行选择。

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

它运行得很好，但当我尝试使用clob列时，

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

我得到了

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

你能告诉我这个问题的解决办法是什么吗?因为我在这个问题上什么也找不到。

Answer 1

文档称，任何分析函数的参数都可以是任何数据类型，但似乎不支持无限制的CLOB。

然而，有一个解决办法：

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

这不是整个CLOB，但在许多情况下，4k应该足够好。

我仍然在想怎样才能解决这个问题

升级到12c是一种选择吗？这个问题与CLOB本身无关，这是Oracle对SQL中4000个字符的字符串有严格限制的事实。在12c中，我们可以选择使用扩展数据类型(前提是我们可以说服DBA打开它！)。了解更多信息。

Answer 2

在使用CLOBs时，有些特性在SQL中可能无法正常工作(比如DISTINCT、ORDER BY GROUP BY等)。看起来LAG也是其中之一，但我在文档中找不到任何地方。

如果CLOB列中的值总是小于4000个字符，则可以使用TO_CHAR

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

或

将其转换为等效的SELF JOIN (可能没有LAG那么有效)

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

演示

Answer 3

我知道这是一个老问题，但我想我找到了一个不用限制CLOB长度的答案，我想和大家分享一下。利用CTE和递归子查询，我们可以使用CLOB列复制延迟功能。

首先，让我们看看我的“原始”查询：

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;

正如预期的那样，我得到了以下错误：

ORA-00932:不一致的数据类型:预期- got CLOB

现在，让我们看一下修改过的查询：

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;

这就是它的工作原理。

1. 我创建了TEST_TABLE，这实际上只适用于示例，因为您应该已经在模式中的某个地方拥有这个表了。
2. 我创建了我想要拉的数据的CTE，加上表中的主键(或唯一列)上的一个滞后函数，按照我在原始查询中应该有的方式进行分区和排序。
3. 使用初始行作为根创建递归子查询，并在滞后列上逐行连接。从当前行返回CLOB列，从其父行返回CLOB列。