Archivo de la categoría: SQL

SQL vs R

Introducción

Vamos a realizar una breve comparativa entre SQL y R. Para ello nos centraremos en los aspectos básicos del acceso a datos, mostrando ejemplos de las mismas operaciones en ambos lenguajes.

En junio de 2015 Oracle publicó el documento SQL – the natural language for analysis en el que se describen los fundamentos del lenguaje SQL y las ventajas que ofrece en el campo del acceso y análisis de datos:

http://www.oracle.com/technetwork/database/bi-datawarehousing/wp-sqlnaturallanguageanalysis-2431343.pdf

Éste documento será nuestra guía en la pequeña comparativa que sigue a continucaión.

Preparación del entorno

El juego de datos que se utiliza en los ejemplos del documento son las conocidas tablas EMP y DEPT, que tradicionalmente han acompañado a Oracle. Desde R se puede acceder a una gran variedad de fuentes de datos, entre las que se incluyen bases de datos, ficheros, recursos web, etc.

En este caso, para acceder a los datos de las tablas Oracle desde R, primero los volcaremos a un fichero con estructura CSV y los cargaremos utilizando la librería de R “DPLYR”.

Las siguientes sentencias SQL nos pueden servir para generar la estructura CVS:

select '"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"' from dual
union all
select EMPNO||',"'||ENAME||'","'||JOB||'",'||MGR||','||HIREDATE||','||SAL||','||COMM||','||DEPTNO
from emp;
select '"DEPTNO","DNAME","LOC"' from dual
union all
select DEPTNO||',"'||DNAME||'","'||LOC||'"'
from dept;

Después de guardar los datos de EMP y DEPT en disco con formato CSV será necesario copiar ambos ficheros en una ruta a la que se tenga acceso desde el entorno de ejecución de R (por ejemplo, un subdirectorio “./data” dentro del espacionde trabajo) y ejecutar los siguientes comandos:

library(dplyr)
emp <- tbl_dt(read.csv("./data/emp.csv",stringsAsFactors = FALSE))
dept <- tbl_dt(read.csv("./data/dept.csv",stringsAsFactors = FALSE))

Principios de SQL

El lenguaje SQL se compone de cuatro operadores básicos:

  • Projection
  • Filter
  • Join
  • Aggregate

Projection

El operador projection permite indicar las columnas que queremos recuperar de una tabla. En su forma básica, se indican las columnas existentes por su nombre o posición en la tabla.

SELECT ename, job, hiredate
FROM emp;
ENAME	JOB		HIREDATE
-----	---------	--------------------
KING	PRESIDENT	17-NOV-1981 00:00:00
BLAKE	MANAGER		01-MAY-1981 00:00:00
CLARK	MANAGER		09-JUN-1981 00:00:00
JONES	MANAGER		02-APR-1981 00:00:00
-- Next lines were deleted
emp %>%
select(ENAME, JOB, HIREDATE)
Source: local data table [14 x 3]

    ENAME       JOB             HIREDATE
    (chr)     (chr)                (chr)
1    KING PRESIDENT 17-NOV-1981 00:00:00
2   BLAKE   MANAGER 01-MAY-1981 00:00:00
3   CLARK   MANAGER 09-JUN-1981 00:00:00
4   JONES   MANAGER 02-APR-1981 00:00:00
# Next lines were deleted

La proyección, en su forma extendida, permite generar columnas dinámicas aplicando operaciones que son calculadas en tiempo de ejecución.

SELECT ename, sal, comm, comm/sal*100 as COMM_RATE
FROM emp;
ENAME	SAL	COMM	COMM_RATE
-----	----	----	---------
KING	5000	- 	-
BLAKE	2850	- 	-
CLARK	2450	- 	-
JONES	2975	- 	-
SCOTT	3000	- 	-
FORD	3000	- 	-
SMITH	800	- 	-
ALLEN	1600	300	18.75
WARD	1250	500	40
MARTIN	1250	1400	112
TURNER	1500	0	0
ADAMS	1100	- 	-
JAMES	950	- 	-
MILLER	1300	- 	- 
emp %>%
mutate(COMM_RATE = COMM/SAL*100) %>%
select(ENAME,SAL,COMM,COMM_RATE)
Source: local data table [14 x 4]

    ENAME   SAL  COMM COMM_RATE
    (chr) (int) (int)     (dbl)
1    KING  5000    NA        NA
2   BLAKE  2850    NA        NA
3   CLARK  2450    NA        NA
4   JONES  2975    NA        NA
5   SCOTT  3000    NA        NA
6    FORD  3000    NA        NA
7   SMITH   800    NA        NA
8   ALLEN  1600   300     18.75
9    WARD  1250   500     40.00
10 MARTIN  1250  1400    112.00
11 TURNER  1500     0      0.00
12  ADAMS  1100    NA        NA
13  JAMES   950    NA        NA
14 MILLER  1300    NA        NA

Filter

El operador filter permite indicar una serie de reglas que deben cumplir los datos almacenados para ser recuperados en la consulta. En su forma más sencilla comparan los valores de la tabla con una expresión.

SELECT ename, job, hiredate
FROM emp
WHERE job = 'CLERK';
ENAME	JOB	HIREDATE
------	-----	--------------------
SMITH	CLERK	17-DEC-1980 00:00:00
ADAMS	CLERK	12-JAN-1983 00:00:00
JAMES	CLERK	03-DEC-1981 00:00:00
MILLER	CLERK	23-JAN-1982 00:00:00
emp %>%
filter(JOB == "CLERK") %>%
select(ENAME, JOB, HIREDATE)
Source: local data table [4 x 3]

   ENAME   JOB             HIREDATE
   (chr) (chr)                (chr)
1  SMITH CLERK 17-DEC-1980 00:00:00
2  ADAMS CLERK 12-JAN-1983 00:00:00
3  JAMES CLERK 03-DEC-1981 00:00:00
4 MILLER CLERK 23-JAN-1982 00:00:00

Join

El operador join relaciona distintas tablas a través de la comparación de uno o varios de sus campos. Existen varios tipos de join dependiendo de la forma en la que va a realizar el cruzado de los datos: inner, full outer, outer left, outer right y cross.

SELECT d.deptno, d.dname, e.empno, e.ename
FROM dept d INNER JOIN emp e
 ON (e.deptno = d.deptno);
DEPTNO	DNAME		EMPNO	ENAME
------	----------	-----	------
10	ACCOUNTING	7782	CLARK
10	ACCOUNTING	7934	MILLER
10	ACCOUNTING	7839	KING
20	RESEARCH	7902	FORD
20	RESEARCH	7788	SCOTT
-- Next lines were deleted
emp %>%
inner_join(dept, by = c("DEPTNO"="DEPTNO")) %>%
select(DEPTNO,DNAME,EMPNO,ENAME)
Source: local data table [14 x 4]

   DEPTNO      DNAME EMPNO  ENAME
    (int)      (chr) (int)  (chr)
1      10 ACCOUNTING  7839   KING
2      10 ACCOUNTING  7782  CLARK
3      10 ACCOUNTING  7934 MILLER
4      20   RESEARCH  7566  JONES
5      20   RESEARCH  7788  SCOTT
# Next lines were deleted

Aggregate

El operador aggregate ejecuta operaciones sobre grupos, o sets, de datos.

SELECT SUM(sal) AS total_salary
FROM emp;
TOTAL_SALARY
------------
29025
emp %>%
summarise(total_salary = sum(SAL))
Source: local data table [1 x 1]

  total_salary
         (int)
1        29025

Este operador es especialmente importante en el análisis de datos porque permite acotar las operaciones a rangos o agrupaciones de los datos sobre los que estamos trabajando.

SELECT deptno,
       COUNT(empno) AS no_of_employees,
       SUM(sal) AS total_salary,
       AVG(sal) AS average_salary
FROM emp
GROUP BY deptno;
DEPTNO	NO_OF_EMPLOYEES	TOTAL_SALARY	AVERAGE_SALARY
------	---------------	------------	----------------------------------------
30	6		9400		1566.66666666666666666666666666666666667
20	5		10875		2175
10	3		8750		2916.66666666666666666666666666666666667
emp %>%
group_by(DEPTNO) %>%
summarise(no_of_employees = length(EMPNO),total_salary = sum(SAL), average_salary = mean(SAL))
Source: local data table [3 x 4]

  DEPTNO no_of_employees total_salary average_salary
   (int)           (int)        (int)          (dbl)
1     10               3         8750       2916.667
2     30               6         9400       1566.667
3     20               5        10875       2175.000
Anuncios

Extraer datos del XML Facturae

Introducción

Oracle permite acceder a datos de un XML desde una sentencia SELECT. Para ello utiliza el estandar XPath descrito por la W3C. El acceso a los datos del XML se puede realizar mediante el uso de la función EXTRACT, que ha sido deprecada a partir de Oracle 11g Release 2, y la función XMLQuery que viene a reemplazarla y a ampliar sus posibilidades.

Un ejemplo muy sencillo podría ser el siguiente:

<?xml version="1.0" encoding="ISO-8859-1"?>
<root>
    <node01 Attr01="Atribute 01">Node 01 text</node01>
    <node02>Node 02 text</node02>
</root>

Con la función EXTRACT de Oracle:

select dbms_xmlgen.convert(
         xmltype('<?xml version="1.0" encoding="ISO-8859-1"?>
                  <root>
                    <node01 Attr01="Atribute 01">Node 01 text</node01>
                    <node02>Node 02 text</node02>
                  </root>').extract( '/root/node01/text()' ).getstringval(), 1
       ) as "Extract node01"
from dual;
Extract node01
--------------
Node 01 text

Con la función XMLQuery de Oracle:

select xmlcast(
         xmlquery( '/root/node01/text()'
                   PASSING xmltype('<?xml version="1.0" encoding="ISO-8859-1"?>
                                    <root>
                                      <node01 Attr01="Atribute 01">Node 01 text</node01>
                                      <node02>Node 02 text</node02>
                                    </root>')
                   RETURNING CONTENT
                 ) as VARCHAR2(4000)
       ) as "XMLQuery node01"
from dual;
XMLQuery node01
---------------
Node 01 text

El modelo de factura XML en España: Facturae y FACe

Facturae es un esquema XML de factura electrónica diseñado en primer lugar por la Asociación CCI (Centro de Cooperación Interbancaria) y posteriormente asumido por la Agencia Tributaria española, con el objetivo de fijar un estándar nacional para el intercambio de facturas electrónicas. A día de hoy nos encontramos en la versión 3 del estándar, del que se han publicado varias revisiones. La más reciente es la revisión 3.2.1.

El pasado 15 de enero de 2015 se produjo un importante paso en la implantación de Facturae como modelo de referencia en España. Este día entró en vigor la obligatoriedad de enviar factura electrónica a la administración pública. Para ello, los distintos niveles de administración se han dotado de puntos generales de entrada de facturas como FACe, el punto de entrada de la administración central, y otros sistemas desarrollados por administraciones autonómicas o locales. Estos puntos de entrada admiten generalmente el modelo Facturae versión 3, con distintos grados de aceptación de las revisiones que se han liberado del mismo.

Otro factor importante que se ha introducido, junto a los puntos de entrada de facturas, es la designación del receptor de la factura mediante el sistema DIR3. Este sistema utiliza tres códigos para designar a todos los organismos que forman parte de la administración pública. Para informar los códigos DIR3 en la factura electrónica se han utilizado los nodos de centros administrativos del comprador disponibles en el modelo Facturae.

Oracle, SQL y el modelo XML Facturae

Como ya se ha comentado en la introducción, Oracle permite consultar datos de una estructura XML desde SQL mediante el estándar XPath. Los ejemplos que se muestran a continuación extraen los datos más relevantes del formato Facturae v3.x de cara a las integraciones con FACe y el uso de DIR3:

  • CIF del comprador
  • Nombre del comprador
  • Datos DIR3: Oficina Contable (OC), Órgano Gestor (OG) y Unidad Tramitadora (UT)
  • Número de la factura
  • Serie de la factura
  • Fecha de la factura
  • FileReference o Referencia de registro

Para estos ejemplos se ha utilizado el XML que propone la web oficial del estandar Facturae, incluyendo algunas modificaciones para poder mostrar el tratamiento de los centros administrativos que marca DIR3.

SELECT + EXTRACT

Ejemplo de select directa sobre una tabla con el XML en un campo Clob y la función EXTRACT:

select dbms_xmlgen.convert(xmltype(data_clob).extract( '//Parties/BuyerParty/TaxIdentification/TaxIdentificationNumber/text()').getstringval(), 1) as "CIF Comprador",
       decode( xmltype(data_clob).extract( '//Parties/BuyerParty/TaxIdentification/PersonTypeCode/text()').getstringval(),
               'J', dbms_xmlgen.convert(xmltype(data_clob).extract( '//Parties/BuyerParty/LegalEntity/CorporateName/text()').getstringval(), 1),
               dbms_xmlgen.convert(xmltype(data_clob).extract( '//Parties/BuyerParty/Individual/Name/text()').getstringval(), 1) || ' ' ||
               dbms_xmlgen.convert(xmltype(data_clob).extract( '//Parties/BuyerParty/Individual/FirstSurname/text()').getstringval(), 1)
             ) as "Nombre Comprador",
       dbms_xmlgen.convert(xmltype(data_clob).extract( '//Parties/BuyerParty/AdministrativeCentres/AdministrativeCentre[RoleTypeCode = "01"]/CentreCode/text()').getstringval(), 1) as "Oficina Cont.",
       dbms_xmlgen.convert(xmltype(data_clob).extract( '//Parties/BuyerParty/AdministrativeCentres/AdministrativeCentre[RoleTypeCode = "02"]/CentreCode/text()').getstringval(), 1) as "Órgano Ges.",
       dbms_xmlgen.convert(xmltype(data_clob).extract( '//Parties/BuyerParty/AdministrativeCentres/AdministrativeCentre[RoleTypeCode = "03"]/CentreCode/text()').getstringval(), 1) as "Unidad Tr.",
       dbms_xmlgen.convert(xmltype(data_clob).extract( '//Invoices/Invoice[1]/InvoiceHeader/InvoiceNumber/text()').getstringval(), 1) as "Número",
       dbms_xmlgen.convert(xmltype(data_clob).extract( '//Invoices/Invoice[1]/InvoiceHeader/InvoiceSeriesCode/text()').getstringval(), 1) as "Serie",
       dbms_xmlgen.convert(xmltype(data_clob).extract( '//Invoices/Invoice[1]/InvoiceIssueData/IssueDate/text()').getstringval(), 1) as "Fecha",
       dbms_xmlgen.convert(xmltype(data_clob).extract( '//Invoices/Invoice[1]/Items/InvoiceLine[1]/FileReference/text()').getstringval(), 1) as "Referencia"
from test_xml;
CIF Comprador   Nombre Comprador   Oficina Cont.   Órgano Ges.   Unidad Tr.   Número   Serie   Fecha        Referencia
0000000000B     Juana Mauriño      E00000012       E00000034     E00000033    18       -       2010-03-10   000298172

La función DBMS_XMLGEN.CONVERT permite codificar y decodificar las entidades XML (p. ej: la secuencia “& amp;” para el carácter &). Los valores que admite el parámetro flag son:

  -- DBMS_XMLGEN.CONVERT conversion types
  ENTITY_ENCODE CONSTANT conversionType := 0;
  ENTITY_DECODE CONSTANT conversionType := 1;

SELECT + XMLQUERY

Ejemplo de select directa sobre tabla con el XML en un campo Clob y la función XMLQuery:

select xmlcast(xmlquery( '//Parties/BuyerParty/TaxIdentification/TaxIdentificationNumber/text()' PASSING xmltype(data_clob) RETURNING CONTENT) as VARCHAR2(4000)) as "CIF Comprador",
       xmlcast(xmlquery( '//Parties/BuyerParty/Individual/Name/text()' PASSING xmltype(data_clob) RETURNING CONTENT) as VARCHAR2(4000)) ||' '||
       xmlcast(xmlquery( '//Parties/BuyerParty/Individual/FirstSurname/text()' PASSING xmltype(data_clob) RETURNING CONTENT) as VARCHAR2(4000)) as "Nombre Comprador",
       xmlcast(xmlquery( '//Parties/BuyerParty/AdministrativeCentres/AdministrativeCentre[RoleTypeCode = "01"]/CentreCode/text()' PASSING xmltype(data_clob) RETURNING CONTENT) as VARCHAR2(4000)) as "Oficina Cont.",
       xmlcast(xmlquery( '//Parties/BuyerParty/AdministrativeCentres/AdministrativeCentre[RoleTypeCode = "02"]/CentreCode/text()' PASSING xmltype(data_clob) RETURNING CONTENT) as VARCHAR2(4000)) as "Órgano Ges.",
       xmlcast(xmlquery( '//Parties/BuyerParty/AdministrativeCentres/AdministrativeCentre[RoleTypeCode = "03"]/CentreCode/text()' PASSING xmltype(data_clob) RETURNING CONTENT) as VARCHAR2(4000)) as "Unidad Tr.",
       xmlcast(xmlquery( '//Invoices/Invoice[1]/InvoiceHeader/InvoiceNumber/text()' PASSING xmltype(data_clob) RETURNING CONTENT) as VARCHAR2(4000)) as "Número",
       xmlcast(xmlquery( '//Invoices/Invoice[1]/InvoiceHeader/InvoiceSeriesCode/text()' PASSING xmltype(data_clob) RETURNING CONTENT) as VARCHAR2(4000)) as "Serie",
       xmlcast(xmlquery( '//Invoices/Invoice[1]/InvoiceIssueData/IssueDate/text()' PASSING xmltype(data_clob) RETURNING CONTENT) as VARCHAR2(4000)) as "Fecha",
       xmlcast(xmlquery( '//Invoices/Invoice[1]/Items/InvoiceLine[1]/FileReference/text()' PASSING xmltype(data_clob) RETURNING CONTENT) as VARCHAR2(4000)) as "Referencia"
from test_xml;
CIF Comprador   Nombre Comprador   Oficina Cont.   Órgano Ges.   Unidad Tr.   Número   Serie   Fecha        Referencia
0000000000B     Juana Mauriño      E00000012       E00000034     E00000033    18       -       2010-03-10   000298172

Si el XML se encuentra en una columna de tipo Blob, debemos indicar el ID del juego de caracteres al utilizar XMLType:

select dbms_xmlgen.convert(xmltype(data_blob, NLS_CHARSET_ID('AL32UTF8')).extract('//FileHeader/SchemaVersion/text()').getstringval(), 1) as "Versión"
from test_xml;

Recursos

Ejemplo de XML Facturae: http://descemp.pistigu.com/recursos/ejemplos/facturae-v3-2-ejemplo-dir3/

Buscar intervalos – Lead analytic function

¿Cómo encontrar intervalos libres en una secuencia temporal?

Ésta fue la pregunta que surgió en el transcurso de un proyecto en el que los usuarios tenían que dar de alta periodos temporales con una duración variable y que no tenían por qué seguir un orden. Estos periodos temporales se registraron en una tabla general de periodos, en la que se asociaba a cada usuario los periodos que iba dando de alta con las fechas de inicio y final de periodo. Cada vez que un usuario iba a registrar un nuevo periodo el sistema debía comprobar si este periodo existía ya o era necesario darlo de alta. Pero, ¿cómo buscar si el nuevo periodo de tiempo estaba ya comprendido entre los registrados anteriormente por el usuario?

La respuesta que finalmente encontramos fue utilizar las funciones analíticas MAX y LEAD. Gracias a estas dos funciones, una simple SELECT nos podía devolver los intervalos libres entre los periodos ya registrados en la tabla.

Para demostrar su funcionamiento, primero vamos a crear un pequeño escenario de pruebas. En la tabla “lead_test” vamos a insertar una serie de registros con fechas de inicio y final que nos permitan probar la solución:

-- Clean environment
drop table lead_test;

-- Create test environment
create table lead_test (id number, date_start date, date_end date);

insert into lead_test values(1, to_date('01/01/2014 12:00:01', 'dd/mm/yyyy hh24:mi:ss'), to_date('01/02/2014 12:00:00', 'dd/mm/yyyy hh24:mi:ss'));
insert into lead_test values(3, to_date('01/03/2014 12:00:01', 'dd/mm/yyyy hh24:mi:ss'), to_date('01/04/2014 12:00:00', 'dd/mm/yyyy hh24:mi:ss'));
insert into lead_test values(4, to_date('15/03/2014 22:00:00', 'dd/mm/yyyy hh24:mi:ss'), to_date('01/05/2014 12:00:00', 'dd/mm/yyyy hh24:mi:ss'));
insert into lead_test values(5, to_date('01/05/2014 12:00:01', 'dd/mm/yyyy hh24:mi:ss'), to_date('01/06/2014 12:00:00', 'dd/mm/yyyy hh24:mi:ss'));

commit;

-- Alter default date format
alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';

Una vez ejecutadas las sentencias que preparan el entorno de pruebas vamos a comprobar cómo queda nuestra tabla “lead_test:

-- Show test data
select * from lead_test;

        ID DATE_START          DATE_END
---------- ------------------- -------------------
         1 01/01/2014 12:00:01 01/02/2014 12:00:00
         3 01/03/2014 12:00:01 01/04/2014 12:00:00
         4 15/03/2014 22:00:00 01/05/2014 12:00:00
         5 01/05/2014 12:00:01 01/06/2014 12:00:00

Estos datos nos proporcionan una casuística variada:

  • Hemos omitido el registro con ID = 2 para dejar el intervalo libre.
  • Los registros con ID 3 y 4 se solapan en el tiempo.
  • Los registros con ID 4 y 5 empiezan y acaban en el mismo momento, justo uno a continuación del otro pero sin solapamiento.

En una primera aproximación, se elaboró la siguiente consulta que nos permitía obtener los intervalos que no se encontraban cubiertos por ninguno de los periodos registrados en la tabla:

-- Search All Gaps
select *
from (
      select max(l.date_end) over (order by l.date_start) gap_start,
             lead(l.date_start) over (order by l.date_start) gap_end
      from lead_test l
     )
where gap_start < gap_end;

Como resultado obtendremos el intervalo que existe entre los registros con ID 1 y 3. Pero también el intervalo entre los registros con ID 4 y 5, puesto que no hay solapamiento:

GAP_START           GAP_END
------------------- -------------------
01/02/2014 12:00:00 01/03/2014 12:00:01
01/05/2014 12:00:00 01/05/2014 12:00:01

Con una pequeña modificación en la consulta podemos regular la duración mínima de los intervalos que queremos encontrar. Y, de este modo, podemos descartar aquellos intervalos que resulten demasiado pequeños para nuestros requerimientos:

-- Search Gaps > 1 sec.
select *
from (
      select max(l.date_end) over (order by l.date_start) gap_start,
             lead(l.date_start) over (order by l.date_start) gap_end
      from lead_test l
     )
where gap_start < gap_end
      and (gap_end - gap_start) > (1/24/60/60);

Al incluir como condición que el tiempo entre final de un intervalo y el inicio del intervalo que le sigue sea mayor a 1 segundo (la fórmula 1/24/60/60 representa un segundo en operaciones con fechas en las consultas SQL de Oracle) obtendremos solo el primer intervalo, entre los periodos con ID 1 y 3:

GAP_START           GAP_END
------------------- -------------------
01/02/2014 12:00:00 01/03/2014 12:00:01

Generar SH1 de una cadena

El paquete DBMS_CRYPTO nos permite, entre otras cosas, generar SHA1 de cualquier cadena de texto. Admite los siguientes parámetros:

  • src – [RAW|BLOB|CLOB]: datos a los que aplicar el hash
  • typ – PLS_INTEGER: algoritmo hash a aplicar

Los algoritmos disponibles son los siguientes:

  • HASH_MD4 := 1
  • HASH_MD5 := 2
  • HASH_SH1 := 3

Nota: Es necesario conceder permisos de ejecución sobre este paquete a los usuarios que vayan a utilizarlo.

select DBMS_CRYPTO.hash(utl_raw.cast_to_raw ('Hello'),
                        3) "hash_Hello",
       DBMS_CRYPTO.hash(utl_raw.cast_to_raw ('HELLO'),
                        3) "hash_HELLO"
from dual;
hash_Hello
----------------------------------------
F7FF9E8B7BB2E09B70935A5D785E0CC5D9D0ABF0

hash_HELLO
----------------------------------------
C65F99F8C5376ADADDDC46D5CBCF5762F9E55EB7

Ver también Generar el HASH de una cadena

Generar el HASH de una cadena

El paquete DBMS_UTILITY nos permite, entre otras cosas, generar resúmenes HASH de cualquier cadena de texto. Admite los siguientes parámetros:

  • name – VARCHAR2: cadena a la que se aplica la función hash
  • base – NUMBER: valor mínimo que devolverá la función hash
  • hash_size – NUMBER: valor máximo que devolverá la función hash
select DBMS_UTILITY.get_hash_value('Hello',
                                   1,
                                   power(2,16)-1
                                  ) "hash_Hello",
       DBMS_UTILITY.get_hash_value('HELLO',
                                   1,
                                   power(2,16)-1
                                  ) "hash_HELLO"
from dual;
hash_Hello hash_HELLO
---------- ----------
     11511      16086

Ver también Generar SH1 de una cadena

Generar valores RANDOM

El paquete DBMS_RANDOM nos permite, entre otras cosas, generar números aleatorios indicando un rango. Admite los siguientes parámetros, que pueden ser omitidos:

  • low – NUMBER: valor mínimo que devolverá la función random
  • high – NUMBER: valor máximo que devolverá la función random
select DBMS_RANDOM.value nums_decim,
       DBMS_RANDOM.value(1000,
                         9999) nums_range_decim,
       trunc(DBMS_RANDOM.value(1000,
                               9999)) nums_range_int
from dual;
NUMS_DECIM NUMS_RANGE_DECIM NUMS_RANGE_INT
---------- ---------------- --------------
0,76259616 8188,26474538092           3750

Búsquedas en columnas de tipo LONG

Cuando intentamos hacer una búsqueda sobre una columna de tipo LONG del mismo modo que operamos con columnas VARCHAR nos encontramos con el problema de que no se comportan del mismo modo. Y el problema es más común de lo que a priori se puede esperar dado que varias vistas del diccionario de datos utilizan este tipo de datos para almacenar información bastante interesante.

En el caso que nos ocupa, estábamos intentando localizar el uso de una columna concreta en cualquier vista accesible por el usuario. Cuando intentamos consultar la vista ALL_VIEWS para localizar en ALL_VIEWS.TEXT las menciones a la columna que buscábamos nos encontramos con el siguiente error:

select *
from all_views
where upper(text) like '%TABLE_NAME%';
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

La función UPPER() es una más de las muchas que no se pueden aplicar sobre tipos de datos LONG.

Después de darle varias vueltas al problema, e intentar distintas alternativas, logramos escribir un pequeño bloque anónimo que nos permitía realizar la búsqueda:

-- Search in LONG datatype columns
set serveroutput on;
begin
    for i in (select * from all_views) loop
        if instr(upper(i.text), 'TABLE_NAME') > 0 then
            dbms_output.put_line('View: '||i.view_name);
        end if;
    end loop;
end;

Y por fin obtuvimos el resultado esperado:

View: ALL_ALL_TABLES
View: ALL_TAB_COLUMNS
View: ALL_TAB_COL_STATISTICS
...
...
...