Área de Tecnologías de la Información y las Comunicaciones Aplicadas
Área de Tecnologías de la Información y las Comunicaciones Aplicadas Universidad de Murcia
Área de Tecnologías de la Información y las Comunicaciones Aplicadas
ATICA arrow DBCONNECTOR arrow PLSQL - Long To Clob para poder usar LIKE, INSTR, SUBSTR
22.11.2017
 
 
PLSQL - Long To Clob para poder usar LIKE, INSTR, SUBSTR Imprimir E-mail

Desde Oracle 8i, se recomienda el uso de los tipos LOB (clob y blob) en lugar de los LONG (long y long raw).

En este artículo vamos a centrarnos en los tipos LONG y CLOB, y más concretamente en las restricciones que tienen los tipos LONG para hacer búsquedas, y como superarlas haciendo una conversión a CLOB.

En una columna LONG no se puede usar el operador LIKE, y tampoco SUBSTR y/o INSTR, pero con un CLOB sí podemos hacer ésto.

Por otro lado, aunque existe una función TO_LOB, no se puede usar en PL/SQL.

Ejemplos

Supongamos que queremos buscar aquellas vistas que hayamos hecho que contengan la cadena "SALARIO" en el texto de la consulta correspondiente a dichas vistas.

En primer lugar, veamos el error que obtenemos si hacemos una consulta sobre USER_VIEWS con un LIKE sobre la columa TEXT (que es de tipo LONG), que sería lo más directo:

select view_name, text from user_views where upper(text) like '%SALARIO%';
                                                   *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

Como se observa, no puedo usar LIKE sobre una columna LONG. La solución sería convertirla a CLOB, pero si lo hacemos directamente, usando la función TO_LOB, obtenemos el mismo error:

select view_name, to_lob(text) from user_views where upper(text) like '%SALARIO%';
                  *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got LONG

Se me ocurren 2 alternativas para evitar dicho error. La primera sería accediendo a los datos desde PL/SQL mediante un cursor, siempre y cuando el texto de la consulta de la vista sea menor de 32K:

SET SERVEROUTPUT ON

BEGIN
   FOR rec in
      (SELECT view_Name, text, text_length
          FROM USER_VIEWS
         WHERE text_length < 32000) LOOP
      IF UPPER(rec.text) LIKE '%SALARIO%' THEN
        DBMS_OUTPUT.PUT_LINE(rec.view_Name||' -> '||rec.text);
      END IF;
   END LOOP;
END;
/

V3 -> select empno, deptno, NVL(sal,0) + NVL(comm,0) salario from emp

PL/SQL procedure successfully completed.

Y la segunda opción consiste en crear una GTT (Global Temporary Table), convirtiendo la columna LONG a CLOB con la función TO_LOB:

create global temporary table clob_views as select VIEW_NAME, to_lob(TEXT) text_lob from user_views;

Table created.


insert into clob_views select VIEW_NAME, to_lob(TEXT) from user_views;

3 rows created.


select * from clob_views where instr(upper(text_lob),'SALARIO',1,1)>0;

VIEW_NAME
------------------------------
TEXT_LOB
--------------------------------------------------------------------------------
V3
select empno, deptno, NVL(sal,0) + NVL(comm,0) salario from emp


select * from clob_views where upper(text_lob) like '%SALARIO%';

VIEW_NAME
------------------------------
TEXT_LOB
--------------------------------------------------------------------------------
V3
select empno, deptno, NVL(sal,0) + NVL(comm,0) salario from emp


FAQs

¿Se puede crear una vista materializada sobre una tabla con columnas tipo LONG?

No, porque se obtiene un error "ORA-00997: illegal use of LONG datatype". Pero se puede crear la vista materializada aplicando la función TO_LOB a las columnas LONG, de modo que dichas columnas pasarán a ser de tipo CLOB en la vista materializada:

create materialized view mv_user_views as select * from user_views;
                                                        *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype


create materialized view mv_user_views as select view_name, to_lob(TEXT) text_lob from user_views;

Materialized view created.

¿Se puede convertir un CLOB a VARCHAR2 y crear una vista donde una columna que era tipo CLOB aparezca como VARCHAR2?

Sí, pero sólo puedo coger los 4000 primeros caracteres. Se puede hacer usando "DBMS_LOB.SUBSTR(columna, 4000, 1)":

create view v_user_views as select view_name, dbms_lob.substr(text_lob,4000,1) text_lob from mv_user_views;

View created.

desc v_user_views
 Name                       Null?    Type
 -------------------------- -------- ----------------------------
 VIEW_NAME                  NOT NULL VARCHAR2(30)
 TEXT_LOB                            VARCHAR2(4000)

desc mv_user_views
 Name                       Null?    Type
 -------------------------- -------- ----------------------------
 VIEW_NAME                  NOT NULL VARCHAR2(30)
 TEXT_LOB                            CLOB


Enlaces

Última modificación ( 25.09.2012 )
 
Área de Tecnologías de la Información y las Comunicaciones Aplicadas
Volver al incio del documento Volver al inicio del documento
Área de Tecnologías de la Información y las Comunicaciones Aplicadas