Archivo de la etiqueta: query

Select sobre colecciones PL/SQL correlacionadas

Deja un comentario

Oracle permite acceder a los valores de colecciones PL/SQL desde sentencias SQL. Esta propiedad del comando SELECT se llama table_collection_expression y muestra el contenido de una colección como si de una tabla se tratara.

Para que el proceso funcione correctamente es necesario que la colección esté declarada en el esquema, no dentro de un paquete:

-- Create PL/SQL collection
create or replace type ty_number is table of number;

-- Select from one PL/SQL collection
select * from table(ty_number(10,20,30,50,40));

La sentencia daría como resultado:

COLUMN_VALUE
------------
10
20
30
50
40

Esta propiedad puede resultarnos muy útil cuando estamos programando en PL/SQL y, por ejemplo, necesitamos extraer datos de una consulta a través de un proceso bulk collection y compararlos con registros de otras tablas.

Pero, ¿qué ocurre si necesitamos cruzar datos de dos colecciones PL/SQL y datos de tablas? ¿Cómo vamos a correlacionar los elementos de varias colecciones entre sí? En estos casos podemos recurrir a una sentencia join en combinación con la cláusula connect by level para generar los índices que nos permitirán recorrer las colecciones:

-- Select from two correlated PL/SQL collections
select coll_index, coll_1_value, coll_2_value
from (select level coll_index from dual connect by level <= 5) gen_idx -- Generate index for iterate collections
     join (select rownum rn, column_value coll_1_value from table(ty_number(10,20,30,50,40))) coll_1 on gen_idx.coll_index = coll_1.rn -- Collection 1
     join (select rownum rn, column_value coll_2_value from table(ty_number(11,21,31,51,41))) coll_2 on gen_idx.coll_index = coll_2.rn; -- Collection 2

La ejecución de la sentencia produce el siguiente resultado:

COLL_INDEX	COLL_1_VALUE	COLL_2_VALUE
----------	------------	------------
1		10		11
2		20		21
3		30		31
4		50		51
5		40		41

Como puede apreciarse al fijarnos en las filas 4 y 5, los elementos de las colecciones mantienen el orden original y la correlación entre sus elementos.

Anuncio publicitario

Select random rows

Deja un comentario

Para seleccionar una o varias filas de una tabla de manera aleatoria existen distintos enfoques. En este ejemplo se intenta obtener una fila de la tabla EMP tomando una muestra del 25% de los registros totales sobre el que posteriormente se aplica una ordenación utilizando la función DBMS_RANDOM.random. De esta forma podemos aplicar una ordenación aleatoria sobre los registros de la tabla evitando el alto coste de ejecutar la función random en tablas que sean muy grandes.

-- Select 1 random row from EMP table
select *
from ( select *
    from emp sample(25) -- 25% total rows
    order by dbms_random.random)
where rownum = 1;

El resultado:

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7698	BLAKE	MANAGER	7839	01-MAY-81	2850	-	30

Extraer datos del XML Facturae

Deja un comentario

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/

Búsquedas en columnas de tipo LONG

Deja un comentario

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
...
...
...

Volcado de una consulta SQL a fichero (.csv)

Deja un comentario

Mediante SQL*Plus se pueden realizar volcados de datos a fichero dando el formato que nos sea más conveniente. Todo depende de la parametrización que se realice de la sesión SQL y elaborar un poco las sentencias que utilicemos como fuente de datos.

En este ejemplo vamos a generar un fichero con estructura .csv utilizando la coma como separador y las dobles comillas para las columnas de tipo texto:

SET LINESIZE 80
SET NEWPAGE NONE
SET HEADING OFF
SET COLSEP ''
SET RECSEP OFF
SET PAGESIZE 0
SET FEEDBACK OFF
SET TRIMOUT OFF
SET TRIMSPOOL OFF
SET VERIFY OFF

ACCEPT what_owner PROMPT 'what owner? '

spool all_tables_list.csv;

-- Headers
select '"OWNER","TABLE NAME","NUM ROWS"' from dual;
-- Data
select '"'||owner||'","'||table_name||'",'||nvl(num_rows,0)||''
from all_tables
where owner = '&what_owner';

spool off;

Si guardamos el script del cuadro superior en un fichero con nombre «all_tables_list.sql» y lo ejecutamos el resultado mostrará algo similar a las siguientes líneas (el resultado puede variar dependiendo de la versión de Oracle y las opciones que se tengan instaladas):

SQL> @all_tables_list.sql
 what owner? SYS
 "OWNER","TABLE NAME","NUM ROWS"
 "SYS","AUDIT_ACTIONS",0
 "SYS","AW$AWCREATE",0
 "SYS","AW$AWMD",0
 "SYS","AW$EXPRESS",0
 "SYS","DUAL",0
 "SYS","ODCI_SECOBJ$",0
 "SYS","ODCI_WARNINGS$",0
 "SYS","OLAPTABLEVELS",0
 "SYS","OLAPTABLEVELTUPLES",0
 "SYS","PSTUBTBL",0
 "SYS","STMT_AUDIT_OPTION_MAP",0
 "SYS","SYSTEM_PRIVILEGE_MAP",0
 "SYS","TABLE_PRIVILEGE_MAP",0

Mediante el comando «spool», el script guarda el resultado de la consulta en un fichero con nombre «all_tables_list.csv». El fichero se escribirá en la ruta desde la que hayamos ejecutado SQL*Plus.

Si abrimos este fichero con una aplicación para el tratamiento de hojas de cálculo podremos trabajar con los datos y realizar ordenaciones, agrupaciones y todas las operaciones propias de estas aplicaciones.