Estoy enfrentando un problema cuando intento usar la función LAG en la columna CLOB.

Entonces supongamos que tenemos una mesa

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;

Ahora hagamos una selección usando columnas que no sean clob

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

Funciona bien como se esperaba, pero cuando intento lo mismo con la columna Clob

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

Entiendo

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

¿Me puede decir cuál es la solución de este problema ya que no pude encontrar nada al respecto?

2
Alexey 16 nov. 2018 a las 13:07

3 respuestas

La mejor respuesta

La documentación dice que el argumento para cualquier función analítica puede ser cualquier tipo de datos, pero parece que CLOB sin restricciones no es compatible.

Sin embargo, hay una solución:

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

Este no es el CLOB completo, pero 4k debería ser lo suficientemente bueno en muchos casos.

Todavía me pregunto cuál es la forma correcta de superar el problema.

¿Actualizar a 12c es una opción? El problema no tiene nada que ver con CLOB como tal, es el hecho de que Oracle tiene un límite estricto para las cadenas en SQL de 4000 caracteres. En 12c tenemos la opción de usar tipos de datos extendidos (¡siempre que podamos persuadir a nuestros DBA para que lo enciendan!). Obtenga más información.

1
APC 16 nov. 2018 a las 12:23

Sé que esta es una vieja pregunta, pero creo que encontré una respuesta que elimina la necesidad de restringir la longitud de CLOB y quería compartirla. Utilizando CTE y subconsultas recursivas, podemos replicar la funcionalidad de retraso con columnas CLOB.

Primero, echemos un vistazo a mi consulta "original":

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;

Como se esperaba, me sale el siguiente error:

ORA-00932: tipos de datos inconsistentes: esperado - consiguió CLOB

Ahora, veamos la consulta modificada:

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;

Pues así es como funciona.

  1. Creo el TEST_TABLE, esto realmente es solo para el ejemplo, ya que ya debería tener esta tabla en algún lugar de su esquema.
  2. Creo un CTE de los datos que quiero extraer, más una función LAG en la clave primaria (o una columna única) en la tabla particionada y ordenada de la misma manera que lo haría en mi consulta original.
  3. Cree una subconsulta recursiva utilizando la fila inicial como raíz y uniendo fila por fila descendente en la columna rezagada. Devolver tanto la columna CLOB de la fila actual como la columna CLOB de su fila principal.
0
Del 21 may. 2020 a las 16:05

Algunas de las características pueden no funcionar correctamente en SQL cuando se utiliza CLOBs (como DISTINCT, ORDER BY GROUP BY etc. Parece que LAG también es una de ellas, pero , No pude encontrar ningún documento.

Si sus valores en las columnas CLOB siempre tienen menos de 4000 caracteres, puede usar TO_CHAR

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

O

Conviértalo en un equivalente SELF JOIN (puede no ser tan eficiente como LAG)

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

Demo

1
Kaushik Nayak 16 nov. 2018 a las 10:38