¡Columnas LONG!… ¡TO_LOB() al rescate!

El tipo de datos LONG es un tipo que se usaba allá por las versiones 6 y 7 (¡hey! ¡Algunos aun nos acordamos de ellas!). La verdad es que fue una especie de chapuza que Oracle se sacó de la manga para trabajar con cadenas de más de 4000 bytes (la limitación de un VARCHAR2). El caso es que el tipo LONG es un tipo antipático donde los haya, porque no obedece a los operadores y funciones de los demás tipos de datos SQL. Con el advenimiento de la versión 8, aparecieron los tipos LOB que mejoraban en todos los sentidos a los viejos tipos LONG (aquí incluimos también LONG RAW). Oracle recomendó que todas las aplicaciones migraran sus tipos de datos de LONG a LOB y que los viejos LONG durmieran por fin el sueño de los justos…

Pero se olvidaron de aplicarse la máxima: las tablas del diccionario de datos están llenas de columnas tipo LONG que no son fácilmente accesibles mediante SQL ‘puro y duro’.

Veamos por ejemplo qué ocurre si intentamos buscar una vista que tenga como base la tabla (¡sí, sí: lo habéis adivinado!) PRUEBA01.

nota: este ejemplo se podría extrapolar a cualquier objeto que se almacene en el diccionario de datos en columnas LONG, como DBA_CONSTRAINTS, DBA_TRIGGERS…

Primero crearemos la vista:

carlosal@bd01.xxxxxxxx> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

carlosal@bd01.xxxxxxxx> CREATE VIEW PRUEBA01_VW
  2  AS SELECT ID_N, C_TXT
  3  FROM PRUEBA01
  4  ORDER BY ID_N
  5  /

Vista creada.

carlosal@bd01.xxxxxxxx>

Vemos que podemos consultar la vista recién creada en DBA_VIEWS:

carlosal@bd01.xxxxxxxx> SELECT TEXT FROM DBA_VIEWS
  2   WHERE VIEW_NAME = 'PRUEBA01_VW'
  3  /

TEXT
-------------------------------------------------------------
SELECT ID_N, C_TXT
FROM PRUEBA01
ORDER BY ID_N

Pero las malas noticias no tardan en llegar: como TEXT es una columna de tipo LONG:

carlosal@bd01.xxxxxxxx> SELECT TEXT FROM DBA_VIEWS
  2  WHERE TEXT LIKE '%PRUEBA01%'
  3  /
WHERE TEXT LIKE '%PRUEBA01%'
      *
ERROR en línea 2:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

El dichoso tipo está haciendo de las suyas: no le gusta nada el SQL…

Pero llega TO_LOB al rescate. TO_LOB es una ‘función herramienta’ que se le debió de ocurrir a alguien en Oracle para aliviar la transición de tipos LONG a tipos LOB.

La función en sí no tiene mucho misterio, salvo que sólo acepta argumentos LONG o LONG RAW y que sólo puede ser utilizada en el ‘SELECT LIST’ de un ‘INSERT…SELECT’ (aunque también se puede utilizar en el ‘SELECT LIST’ de un ‘CREATE TABLE…AS SELECT…’)

Así pues, utilizando la funcioncita en cuestión:

carlosal@bd01.xxxxxxxx> CREATE TABLE MY_DBA_VIEWS
  2  AS SELECT
  3  OWNER            OWNER           ,
  4  VIEW_NAME        VIEW_NAME       ,
  5  TEXT_LENGTH      TEXT_LENGTH     ,
  6  TO_LOB(TEXT)     TEXT            ,
  7  TYPE_TEXT_LENGTH TYPE_TEXT_LENGTH,
  8  TYPE_TEXT        TYPE_TEXT       ,
  9  OID_TEXT_LENGTH  OID_TEXT_LENGTH ,
 10  OID_TEXT         OID_TEXT        ,
 11  VIEW_TYPE_OWNER  VIEW_TYPE_OWNER ,
 12  VIEW_TYPE        VIEW_TYPE       ,
 13  SUPERVIEW_NAME   SUPERVIEW_NAME
 14  FROM DBA_VIEWS
 15  /

Tabla creada.

carlosal@bd01.xxxxxxxx> DESC MY_DBA_VIEWS
 Nombre            ¿Nulo?   Tipo
 ----------------- -------- -----------------

 OWNER             NOT NULL VARCHAR2(30)
 VIEW_NAME         NOT NULL VARCHAR2(30)
 TEXT_LENGTH                NUMBER
 TEXT                       CLOB
 TYPE_TEXT_LENGTH           NUMBER
 TYPE_TEXT                  VARCHAR2(4000)
 OID_TEXT_LENGTH            NUMBER
 OID_TEXT                   VARCHAR2(4000)
 VIEW_TYPE_OWNER            VARCHAR2(30)
 VIEW_TYPE                  VARCHAR2(30)
 SUPERVIEW_NAME             VARCHAR2(30)

carlosal@bd01.xxxxxxxx>

Y nuestra columna TEXT ahora es CLOB.

Entonces:

carlosal@bd01.xxxxxxxx> SELECT VIEW_NAME, TEXT FROM MY_DBA_VIEWS
  2  WHERE TEXT LIKE '%PRUEBA01%'
  3  /

VIEW_NAME                      TEXT
------------------------------ ----------------------------------------------------
PRUEBA01_VW                    SELECT ID_N, C_TXT
                               FROM PRUEBA01
                               ORDER BY ID_N

Y nuestra búsqueda funciona como es debido en simple y llano SQL.

¡Prueba superada!

Saludos.

Carlos.