Cómo añadir un control en los IR de APEX para marcar / desmarcar todas las casilla de tipo «checkbox» del listado.
Select All / Unselect All Checkbox in Interactive Report Header
Deja un comentario
Archivo de la etiqueta: Oracle
12 Things Developers Will Love About Oracle Database 12c Release 2 (All Things SQL)
La nueva versión de Oracle 12cR2 (o 12.2) ya se encuentra disponible en Oracle Cloud. En el siguiente artículo se revisan las principales novedades: 12 Things Developers Will Love About Oracle Database 12c Release 2 (All Things SQL)
- Easier, Better, Faster, Stronger JSON
- JSON from SQL
- JSON in PL/SQL
- Looooooooooong Names
- Robust Code using Constants for Data Type Lengths
- Listagg Improved On Overflow
- Lightning Fast SQL with Real Time Materialized Views
- Fast Estimates with Approximate Query Enhancements
- Verify Data Type Conversions
- Handle Casting Conversion Errors
- Single Statement Table Partitioning
- Automatic List Partitioning
- Mark Old Code as «Not for Use»
- PL/SQL Code Coverage
Oracle 12.2 Sharding Option
Sharding en Oracle 12.2
Oracle 12.2 Cool New Features
En el último Oracle Open World se ha anunciado el lanzamiento de Oracle 12cR2 (o 12.2). En el siguiente artículo se repasan algunas de las novedades más importantes: Oracle 12.2 Cool New Features
- Partitioning
- Alter table to partitioned table
- Create table for exchange
- In-Memory
- Standby Database: In-Memory option
- Dynamic resizing
- Fast start
- Security
- Online encryption and re-key operations
- Internal tablespace encryption
- DB vault simulation
- Dictionary
- Long identifiers
- PDB
- Resource management
- Hot clone
- Local undo
- Flashback
- Characterset
- Others
- Index Monitoring
- Materialized Views – real time refresh
- External Tables – partitions
Select sobre colecciones PL/SQL correlacionadas
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.
Oracle Developer Gateway
Oracle ha reunido en una web todo el material relacionado con el desarrollo de software: Oracle Developer Gateway
Select random rows
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
GitHub: Oracle Database Sample Schemas
A través de GitHub – oracle/db-sample-schemas Oracle pone a disposición de la comunidad de desarrolladores y diseñadores de bases de datos los scripts para generar los siguientes schemas:
- HR: Human Resources
- OE: Order Entry
- PM: Product Media
- IX: Information Exchange
- SH: Sales History
- BI: Business Intelligence
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:
É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
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/
desc EMP 