Oracle SQL Developer 17.4 disponible

Release del cuarto trimestre del 2017 (17.4), se encuentra disponible para descargar aquí.

Oracle 12c R2 - Column Level COLLATION

Para quienes estamos acostumbrados a trabajar con Oracle, nos resulta normal que la base de datos considere a los caracteres "a", "á", "A" y "Á" como distintos, ya que es conocido que Oracle por defecto es una base de datos donde los valores de tipo carácter son almacenados, comparados y ordenados en forma binaria, teniendo en cuenta el valor ASCII de cada uno de ellos.

Pero este comportamiento por defecto puede resultar molesto en situaciones donde debemos realizar búsquedas sobre información ingresada en forma manual, donde la misma puede tener diferencias en Mayúsculas/Minúsculas o en la acentuación. Por ejemplo, si buscamos clientes utilizando esta sintaxis

WHERE Apellido LIKE '%García%'

la sentencia no devolverá aquellos clientes que fueron ingresados en nuestra base de datos como "GARCIA", "Garcia" o "garcía". Este comportamiento puede observarse en operaciones lógicas (ya sea por igualdad, rango o similitud), en agregaciones (GROUP BY) y en el ordenamiento de los datos devueltos por la consulta (ORDER BY).

Es por ello que en muchos sistemas se optaba por alguno de estos dos enfoques al momento de manejar este tipo de información:

1) Se "normalizaba" la información ingresada por los usuarios, convirtiendo todo a mayúsculas o minúsculas, y reemplazando los acentos. De esta forma, los siguientes datos se almacenaban así:

Garcia --> GARCIA
García --> GARCIA
garcia --> GARCIA

2) Se mantenía la información en la base de datos como la ingresaba el usuario, pero se creaba un indice basado en funciones que indexaba por los valores convertidos a mayúsculas/minúsculas y sin acentos.

En ambos casos, al momento de realizar una búsqueda se requiere que el texto a buscar se encuentre en el mismo "formato" (mayúsculas o minúsculas, acentos eliminados) que como se ha almacenado en la tabla o en el índice basado en funciones.

¿Qué es "COLLATION" y para que sirve?

Collation es un nuevo atributo de las columnas de tipo caracter, el cual define como se interpretan las distintas variaciones (por mayúscula/minúscula, acentuación) de las letras del abecedario.

Existen dos tipos básicos de COLLATION:

• Binario: Las comparaciones y el ordenamiento de los datos se basan en el valor numérico de cada caracter de una cadena.
• Lingüístico: El ordenamiento y las comparaciones se basan en secuencias alfabéticas de los caracteres, independiente del valor numérico de los mismos.

Los distintos tipos de COLLATION linguisticos pueden consultarse en el sitio de Oracle. Las distintas COLLATION poseen tres sufijos que definen el comportamiento de las mismas al momento de realizar comparaciones y ordenamiento:

• "_CS": Sensible a acentos y mayúscula/minúscula. Es la opcion por defecto, y si no se especifica el sufijo se asume el mismo.
• "_CI": Case Insensitive, significa que no se tiene en cuenta diferencias entre mayúsculas y minúsculas, pero si a los acentos.
• "_AI": No tiene en cuenta mayúsculas y minúsculas ni acentos.

Si no se especifica una COLLATION en particular, Oracle utiliza un pseudo-valor "USING_NLS_COMP", el cual significa que se tienen en cuenta los valores de los parámetros "NLS_SORT" y "NLS_COMP" para determinar la collation a utilizar.

¿Como se define la COLLATION de una columna?

Al momento de crear una tabla, cada columna puede incluir la definición del COLLATION que se va a utilizar. Asimismo, se puede definir una COLLATION por defecto a nivel de tabla, esquema, sesión y base de datos. A continuación veremos algunos ejemplos:

A nivel Columna

CREATE TABLE t_Tabla1 (
  ID_Tabla1          NUMBER,
  Texto              VARCHAR2(20 CHAR),
  TextoBinarioCI     VARCHAR2(20 CHAR) COLLATE BINARY_CI,
  CONSTRAINT pk_Tabla1 PRIMARY KEY (ID_Tabla1)
);

Para probar como se comporta la columna "TextoBinarioCI", vamos a insertar algunos datos y ejecutar algunas consultas:

INSERT INTO t_Tabla1
    SELECT 1, 'Opción', 'Opción' FROM DUAL UNION ALL
    SELECT 2, 'Opcion', 'Opcion' FROM DUAL UNION ALL
    SELECT 3, 'opción', 'opción' FROM DUAL UNION ALL
    SELECT 4, 'opcion', 'opcion' FROM DUAL;

4 rows inserted.

SELECT * FROM t_Tabla1 t
WHERE t.Texto = 'opcion';

 ID_TABLA1 TEXTO                TEXTOBINARIOCI
---------- -------------------- --------------------
         4 opcion               opcion

SELECT * FROM t_Tabla1 t
WHERE t.TextoBinarioCI = 'opcion';

 ID_TABLA1 TEXTO                TEXTOBINARIOCI
---------- -------------------- --------------------
         2 Opcion               Opcion
         4 opcion               opcion

A nivel Tabla

Podemos crear una tabla con una COLLATION por defecto, o modificar una tabla existente. Hay que tener en cuenta que modificar la COLLATION por defecto de una tabla existente, no modifica las columnas existentes, sino que el nuevo valor se aplica a las columnas que se creen en la misma posteriormente.

ALTER TABLE t_Tabla1 DEFAULT COLLATION BINARY_AI;

Table T_TABLA1 altered.

ALTER TABLE t_Tabla1 ADD (
  TextoBinarioAI  VARCHAR2(20 CHAR) );

Table T_TABLA1 altered.

A continuación, actualizamos los datos existente en la tabla, repitiendo los valores de las dos columnas ya existentes en la nueva columna. Y luego realizamos una búsqueda similar a la anterior, pero sobre la nueva columna.

UPDATE t_Tabla1
SET TextoBinarioAI = CASE ID_Tabla1 WHEN 1 THEN 'Opción'
                                    WHEN 2 THEN 'Opcion'
                                    WHEN 3 THEN 'opción'
                                    WHEN 4 THEN 'opcion'
                     END;

4 rows updated.

SELECT * FROM t_Tabla1 t
WHERE t.TextoBinarioAI = 'opcion';

 ID_TABLA1 TEXTO                TEXTOBINARIO         TEXTOBINARIOAI
---------- -------------------- -------------------- --------------------
         1 Opción               Opción               Opción
         2 Opcion               Opcion               Opcion
         3 opción               opción               opción
         4 opcion               opcion               opcion

Podemos ver que al crear la nueva columna, como la tabla había sido configurada con la opción DEFAULT COLLATION BINARY_AI, la misma no distingue entre mayúsculas y minúsculas ni acentos al momento de realizar comparaciones.

A nivel Esquema

Para definir una COLLATION por defecto a nivel de esquema, se utiliza la clausula "DEFAULT COLLATION {COLLATION}" al momento de crear un usuario, o podemos modificarlo posteriormente:

CREATE USER usr1 IDENTIFIED BY pwd1
  DEFAULT TABLESPACE users
  DEFAULT COLLATION BINARY_CI;

ALTER USER usr2 DEFAULT COLLATION BINARY_AI;

Es importante recordar que estos cambios afectan a las columnas creadas con posterioridad, no modificando las columnas ya existentes.

A nivel Sesión

La forma mas sencilla es utilizando una sentencia "ALTER SESSION SET DEFAULT_COLLATION", como se muestra a continuación:

ALTER SESSION SET DEFAULT_COLLATION=SPANISH_CI;

También es posible modificar la COLLATION con la que se crearan las nuevas columnas en forma implícita, al configurar los valores de "NLS_SORT" y "NLS_COMP" y luego crear columnas sin explicitar ninguna COLLATION (ni haber configurado una COLLLATION por defecto para la tabla, esquema o sesión).

ALTER SESSION SET NLS_SORT=SPANISH_CI;
ALTER SESSION SET NLS_COMP=LINGUISTIC;

CREATE TABLE t_Tabla2 (
  Texto     VARCHAR2(10)
);

Para ver la COLLATION con la que fue creada una columna, podemos utilizar la siguiente consulta:

SELECT column_id, column_name, collation
FROM   user_tab_columns
WHERE  table_name = 'T_TABLA2'

 COLUMN_ID COLUMN_NAME                 COLLATION
---------- --------------------------- --------------------------------
         1 TEXTO                       USING_NLS_COMP

A nivel Base de Datos

Para cambiar la COLLATION por defecto de toda una base de datos, se puede hacer el cambio a los parámetros "NLS_SORT" y "NLS_COMP" a nivel de la CDB, (y el mismo impacta esta base de datos y a todas las PDB conectadas a la misma) o en cada PDB.

ALTER SYSTEM SET NLS_SORT=BINARY_CI SCOPE=SPFILE;
ALTER SYSTEM SET NLS_COMP=LINGUISTIC SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

A nivel de Sentencia

Podemos utilizar la clausula "COLLATE {COLLATION}" al momento de hacer agrupaciones, ordenamientos o comparaciones, como podemos ver en los siguientes ejemplos:

SELECT ID_Tabla1, Texto 
FROM t_Tabla1
WHERE Texto COLLATE SPANISH_CI LIKE 'O%o%';

 ID_TABLA1 TEXTO                                             
---------- --------------------------------------------------
         2 Opcion                                            
         4 opcion                                            

SELECT ID_Tabla1, Texto 
FROM t_Tabla1
WHERE Texto COLLATE SPANISH_AI LIKE 'O%o%';

 ID_TABLA1 TEXTO                                             
---------- --------------------------------------------------
         1 Opción                                            
         2 Opcion                                            
         3 opción                                            
         4 opcion       

SELECT TextoBinarioAI COLLATE SPANISH_CI, COUNT(*)
FROM t_Tabla1
GROUP BY TextoBinarioAI COLLATE SPANISH_CI;

TEXTOBINARIOAICOLLAT   COUNT(*)
-------------------- ----------
Opcion                        2
Opción                        2

SELECT TextoBinarioAI COLLATE SPANISH_AI, COUNT(*)
FROM t_Tabla1
GROUP BY TextoBinarioAI COLLATE SPANISH_AI;

TEXTOBINARIOAICOLLAT   COUNT(*)
-------------------- ----------
Opción                        4

SELECT ID_Tabla1, TextoBinarioAI
FROM t_Tabla1
ORDER BY TextoBinarioAI;

 ID_TABLA1 TEXTOBINARIOAI      
---------- --------------------
         1 Opción              
         4 opcion              
         3 opción              
         2 Opcion              


SELECT ID_Tabla1, TextoBinarioAI
FROM t_Tabla1
ORDER BY TextoBinarioAI COLLATE BINARY;


 ID_TABLA1 TEXTOBINARIOAI      
---------- --------------------
         2 Opcion              
         1 Opción              
         4 opcion              
         3 opción              

Oracle Code Online en Diciembre

Nuevo evento gratuito Oracle Code Online en diciembre, cubriendo cinco tracks:

• Database
• Java
• Emerging Technologies
• DevOps/Containers
• JavaScript

Como bonus, con la registración se obtienen U$S 300 para utilizar en Oracle Cloud

Enseguida volvemos

Después de un par de semanas de inactividad debido a compromisos laborales y a unas merecidas vacaciones, vamos a retomar con la actividad del blog

Oracle VirtualBox 5.2 disponible para descargar

La nueva release de Virtual Box puede ser descargada desde el sitio oficial de Oracle y desde la página de descargas de Virtual Box.


La lista de los cambios introducidos en esta versión se encuentra disponible aqui.

Oracle 12c R2 - Conversiones de Tipo de Datos y Manejo de Error

¿Como validar si un dato puede ser convertido? 

Cuando se deben realizar conversiones de datos, es frecuente encontrar valores que no pueden ser convertidos al tipo de datos deseado.

Tomemos el siguiente ejemplo (el texto en azul lo utilizaremos en todos los ejemplos del presente artículo):

WITH vDatos (ID, Fecha) AS
( SELECT 1, '20170101' FROM DUAL
  UNION ALL
  SELECT 2, '20171501' FROM DUAL
  UNION ALL
  SELECT 3, '20170115' FROM DUAL
  UNION ALL
  SELECT 4, '01-apr-2017' FROM DUAL
  UNION ALL
  SELECT 5, '01-abr-2017' FROM DUAL
  UNION ALL
  SELECT 6, '01/08/17' FROM DUAL
  UNION ALL
  SELECT 7, '30-Feb-17' FROM DUAL
)
SELECT ID, Fecha, TO_DATE(Fecha)
FROM vDatos v;

Según cómo tengamos configurados los parámetros NLS_DATE_FORMAT y NLS_DATE_LANGUAGE, al ejecutar la consulta anterior nos encontraremos con alguno de estos mensajes de error, los cuales impiden la ejecución de la consulta:

ORA-01861: literal does not match format string
ORA-01843: not a valid month
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-01839: date not valid for month specified

Es por ello que es normal que quienes se han encontrado repetidamente con este problema, hayan creado funciones del estilo "es_una_fecha()" o "es_un_numero()" para poder identificar los datos que pueden ser convertidos, evitando los errores que cancelan la ejecución de la consulta.

En Oracle 12c Release 2, se introduce una nueva función "VALIDATE_CONVERSION" que recibe como entrada un dato y un tipo de datos al que deseamos convertir el dato original, y la función nos devuelve 1 cuando es posible realizar la conversión y 0 cuando no es posible.

Ejemplo (utilizando la clausula WITH del ejemplo anterior)

SELECT v.ID, v.Fecha, TO_DATE(v.Fecha)
FROM vDatos v
WHERE VALIDATE_CONVERSION(v.Fecha AS DATE) = 1;

También es posible (y recomendado) utilizar un formato específico para probar si el dato puede ser convertido, como vemos en este ejemplo (utilizando la clausula WITH del ejemplo anterior)

SELECT v.ID, v.Fecha, TO_DATE(v.Fecha,'YYYYMMDD')
FROM vDatos v
WHERE VALIDATE_CONVERSION(v.Fecha AS DATE, 'YYYYMMDD') = 1;

De esa forma, podemos identificar aquellos registros que pueden ser convertidos y aquellos que no pueden serlo. Esto resuelve muchos problemas de conversión de datos, permitiendo ignorar los registros que no poseen un dato que puede ser convertido.

¿Qué pasa cuando necesitamos procesar TODOS los registros, y utilizar algún valor especial cuando no es posible realizar la conversión?

En muchos escenarios, identificar que dato puede ser convertido y cual no puede no ser suficiente. Por eso Oracle 12C Release 2 introduce una nueva opción dentro de las funciones de conversión de datos que permite especificar un valor por defecto cuando no es posible realizar la conversión, en vez de generar un error.

Esta opcion es "DEFAULT {VALOR|NULL} ON CONVERSION ERROR" y puede ser utilizada tanto en la función CAST como en las funciones de conversión tradicionales ("TO_DATE", "TO_NUMBER", etc.).

A continuación podemos ver dos ejemplos de como utilizarlos (utilizando la clausula WITH del primer ejemplo para generar los datos)

SELECT v.ID, v.Fecha, TO_DATE(v.Fecha DEFAULT '19000101' ON CONVERSION ERROR,'YYYYMMDD')
FROM vDatos v

SELECT v.ID, v.Fecha, TO_DATE(v.Fecha DEFAULT NULL ON CONVERSION ERROR,'DD/MM/YY')
FROM vDatos v

Oracle SQL Developer 17.3.1 disponible

Actualización #1 de la Release del tercer trimestre (17.3), contiene mas de 25 correcciones a bugs reportados por usuarios.

Se encuentra disponible para descargar aquí.

Oracle 12c R2 - Mejoras a APROXIMATE QUERY

APPROX_COUNT_DISTINCT en Oracle 12.1

En muchos escenarios de negocio, se requiere conocer información acumulada (total de ventas, cantidad de clientes nuevos, etc. etc.) con un grado de certeza importante, pero sin llegar a la necesidad de que el resultado sea 100% exacto.

Por ejemplo, la gerencia de una cadena de supermercados puede decidir dejar de abrir los días domingos, basándose en la cantidad de operaciones realizadas en dichos días y comparándolo con los otros días de la semana. En dicha situación, conocer el  número exacto de operaciones de venta por cada día de la semana no es una necesidad, sino que con un resultado aproximado es posible tomar una decisión.

En Oracle 12c, se introdujo la función de agregación aproximada APPROX_COUNT_DISTINCT que permite obtener resultados aproximados en forma mas rápida y consumiendo menos recursos.

El principal inconveniente de esta nueva funcionalidad radica en la necesidad de modificar el código existente para utilizar esta nueva función, como podemos ver en el siguiente ejemplo:

SELECT APPROX_COUNT_DISTINCT(manager_id) AS "Active Managers"
  FROM employees;

Nuevos parámetros de Oracle 12.2

Oracle 12c Release 2 introduce tres nuevos parámetros que permiten que las funciones de agregación "normales" (aquellas que venimos utilizando en nuestro código) realicen los cálculos por aproximación, sin necesidad de modificar las consultas existentes.

Los mismos son:

approx_for_aggregation
approx_for_count_distinct
approx_for_percentile

Ejemplo

Si ejecutamos una sentencia con COUNT(DISTINCT) en nuestra base de datos, sin habilitar el uso de funciones de aproximación, y consultamos el resultado y el plan de ejecución, obtenemos lo siguiente:

SQL> SELECT name, value
FROM   v$ses_optimizer_env
WHERE  sid = SYS_CONTEXT('USERENV','SID')
AND    name LIKE '%approx%';

NAME                                     VALUE
---------------------------------------- -------------------------
approx_for_aggregation                   false
approx_for_count_distinct                false
approx_for_percentile                    none

SQL>  SELECT COUNT(DISTINCT id) AS data_count
  2  FROM   t2;

DATA_COUNT
----------
   1010000

SQL> SET AUTOTRACE TRACE EXPLAIN
SQL> SELECT COUNT(DISTINCT id) AS data_count
FROM   t2;

Execution Plan
----------------------------------------------------------
Plan hash value: 4170058314

-----------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     1 |    13 |       |  4472   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE      |          |     1 |    13 |       |            |          |
|   2 |   VIEW               | VW_DAG_0 |  1010K|    12M|       |  4472   (1)| 00:00:01 |
|   3 |    HASH GROUP BY     |          |  1010K|  4931K|    11M|  4472   (1)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T2       |  1010K|  4931K|       |  1390   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------

Analizando el plan de ejecución, podemos ver que que la consulta generó una vista agrupando por un HASH, para poder calcular el resultado acumulado, la cual consumió 11 Mb. de espacio temporal. El costo total de la consulta es de 4.472, correspondiendo 3.082 de ellos a dicha vista temporal.

Al habilitar el uso de funciones de agregación aproximadas por defecto (sin necesidad de cambiar la funcion COUNT() por la nueva función APROX_COUNT_DISTINCT(), la misma consulta devuelve otro resultado y nos genera el siguiente plan de ejecución:

SQL> ALTER SESSION SET approx_for_aggregation = TRUE;
Session altered.

SQL> SELECT name, value
FROM   v$ses_optimizer_env
WHERE  sid = SYS_CONTEXT('USERENV','SID')
AND    name LIKE '%approx%';

NAME                                     VALUE
---------------------------------------- -------------------------
approx_for_aggregation                   true
approx_for_count_distinct                true
approx_for_percentile                    all

SQL>  SELECT COUNT(DISTINCT id) AS data_count
  2  FROM   t2;

DATA_COUNT
----------
    979478

SQL> SET AUTOTRACE TRACE EXPLAIN
SQL> SELECT COUNT(DISTINCT id) AS data_count
FROM   t2;

Execution Plan
----------------------------------------------------------
Plan hash value: 3321871023

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |     5 |  1390   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE APPROX|      |     1 |     5 |            |          |
|   2 |   TABLE ACCESS FULL   | T2   |  1010K|  4931K|  1390   (1)| 00:00:01 |
------------------------------------------------------------------------------

Podemos ver que la segunda consulta devuelve un resultado (979.478) que difiere con el resultado exacto (1.010.000), con una diferencia del 3.02% respecto al resultado exacto.

Pero en este caso, el plan de ejecución nos muestra que Oracle no necesitó agrupar los datos en una vista, reduciendo el costo total en de 4.472 a 1.390 y sin consumir los 11 Mb. de espacio temporal.

En este ejemplo, se logra una reducción del 69% del costo de la consulta sacrificando un 3% de exactitud, lo cual puede ser un valor aceptable para muchos casos.

Oracle SQL Developer 17.3 disponible

La nueva release trimestral de SQL Developer fue liberada el viernes pasado, y se encuentra disponible para descargar aquí.

Conjuntamente con SQL Developer, se encuentra disponible como descarga adicional SQLcl, la herramienta de linea de comando que reemplaza a SQL*Plus.

Oracle 12c R2 - LISTAGG y OVERFLOW

En Oracle 11gR2 se introdujo una nueva función analítica, LISTAGG, la cual ordena los datos dentro de cada grupo especificado en la cláusula ORDER BY y luego concatena los valores de la columna o expresión seleccionados.

Por ejemplo, si queremos obtener una lista de las tablas a las que tenemos acceso, junto con una lista de todas las columnas, ordenadas por nombre de columna, de cada una de tablas, podemos ejecutar la siguiente consulta:

SELECT table_name,
       LISTAGG(column_name, ',') WITHIN GROUP 
                  (ORDER BY column_name) Columns
FROM all_tab_cols
GROUP BY table_name;

Lo cual produce el siguiente resultado:

Pero el problema surge cuando la lista concatenada de valores supera el tamaño máximo que el lenguaje SQL soporta en Oracle, el cual es 4000 bytes. En el ejemplo, siguiente, repetimos 50 veces los datos para aumentar el tamaño del resultado de LISTAGG, obteniendo el siguiente error:

SELECT table_name,
       LISTAGG(column_name, ',') WITHIN GROUP 
                  (ORDER BY column_name) Columns
FROM all_tab_cols
    CROSS JOIN (SELECT level FROM dual CONNECT BY level <=50)
GROUP BY table_name;

ORA-01489: result of string concatenation is too long
01489. 00000 -  "result of string concatenation is too long"
*Cause:    String concatenation result is more than the maximum size.
*Action:   Make sure that the result is less than the maximum size

Oracle 12cR2 introduce una sintaxis ampliada para la función LISTAGG, la cual nos permite obviar el error, mostrando sólo los primeros 4000 bytes que la función obtiene, sin producir un error, como podemos ver a continuación (aquí "cortamos' la cadena concatenada en dos pedazos para poder ver el principio y fin de la misma):

SELECT x.table_name,
       SUBSTR(LISTAGG(x.column_name, ',' ON OVERFLOW TRUNCATE) 
            WITHIN GROUP (ORDER BY x.column_name),1,30) AS "Comienza con...",
       SUBSTR(LISTAGG(x.column_name, ',' ON OVERFLOW TRUNCATE)
            WITHIN GROUP (ORDER BY x.column_name),-30) AS "Termina con..."
FROM all_tab_cols x
    CROSS JOIN (SELECT level FROM dual CONNECT BY level <=50)
GROUP BY x.table_name;

Como vimos en el ejemplo anterior, la opción "ON OVERFLOW TRUNCATE" por defecto corta el resultado hasta el valor anterior cuyo tamaño no exceda los 4000 bytes, agrega "..." a continuación del último valor que puede ser mostrado, y luego muestra entre paréntesis la cantidad de caracteres que fueron truncados.

Podemos reemplazar los puntos suspensivos con cualquier cadena que deseemos utilizar como indicador para informar que el texto ha sido truncado, de la siguiente forma:

ON OVERFLOW TRUNCATE '///'

Tambien podemos indicarle que no deseamos saber el numero de caracteres truncados, de la siguiente forma:

ON OVERFLOW TRUNCATE WITHOUT COUNT

La sintaxis completa de la función LISTAGG en oracle 12c Release 2 puede encontrarse aqui.

Oracle Application Express 5.1.3.00.05 disponible para descargar

Ya se encuentra disponible la última versión de APEX (5.1.3.00.05) en el sitio de descargas de Oracle.


La misma fue liberada en el día de ayer.

Mas información y toda la documentación sobre APEX en el sitio oficial de Oracle.

Oracle 12c R2 - Utilizar Constantes para definir el tamaño de Variables

Es común encontrar en nuestras aplicaciones, lugares donde accedemos a tablas o vistas diccionario de datos de Oracle para obtener cierta información, por ejemplo:

SELECT t.table_name
INTO vTabla
FROM user_tables t
WHERE t.table_name like ‘T_%’

El tamaño máximo de los indicadores era, hasta Oracle 12c R2, de 30 caracteres, por lo que es frecuente encontrar que se definía a la variable “vTabla” de la siguiente forma:

DECLARE
    vTabla    VARCHAR2(30);

Pero, como ya vimos en el artículo anterior del blog, "Oracle 12c R2 - Identificadores de 128 Bytes", la Release 2 de Oracle 12c nos permite definir indicadores de hasta 128 caracteres de largo, por lo que es probable que nuestras aplicaciones que definían variables para almacenar indicadores con un largo fijo de 30 caracteres muy pronto comiencen a fallar con el siguiente error:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Afortunadamente, el cambio  en el tamaño de los indicadores viene acompañado en Oracle 12c R2 de la posibilidad de declarar el largo de variables usando constantes. Por ejemplo, podemos definir un paquete de constantes y especificar en el mismo los valores que deseamos utilizar:

CREATE OR REPLACE PACKAGE pkg_constantes
AS
  --
  cn_largo_identificadores  constant pls_integer := 128;
  cn_largo_codigos          constant pls_integer := 20;
  cn_largo_cuit             constant pls_integer := 11;
  --
END pkg_constantes;
/

Y luego utilizar los mismos al momento de definir variables:

declare
  vNombreTabla     VARCHAR2(pkg_constantes.cn_largo_identificadores );
  vCUIT           NUMBER(pkg_constantes.cn_largo_cuit);
  vCodigoCliente  VARCHAR2(pkg_constantes.cn_largo_codigos);

Para simplificar nuestra tarea, la versión 12c R2 ya provee una constante declarada en el paquete “DBMS_STANDARD” llamada “ORA_MAX_NAME_LEN” la cual representa un valor de 128, por lo que nuestro primer ejemplo puede ser reformulado de la siguiente forma:

DECLARE
    vTabla   VARCHAR2(ORA_MAX_NAME_LEN);

Ejemplo y documentación adicional 

• Ejemplo en Oracle Live SQL.
• Artículo en Blog de Steven Feuerstein

Oracle 12c R2 - Identificadores de 128 Bytes

Seguramente, mas de una vez cada desarrollador o administrador de Oracle sufrió un dolor de cabeza al tener que crear un objeto y encontrarse con la limitación de treinta caracteres para los nombres de objetos (identificadores).

Por ejemplo, si queríamos correr el siguiente script:

CREATE TABLE t_EstadoComprobante
( ID_EstadoComprobante      NUMBER(4)       NOT NULL,
  EstadoComprobante         VARCHAR(50)     NOT NULL
);
--
ALTER TABLE t_EstadoComprobante
    ADD CONSTRAINT PK_EstadoComprobante
        PRIMARY KEY (ID_EstadoComprobante);
--
CREATE TABLE t_Comprobante
( ID_Comprobante            NUMBER(4)       NOT NULL,
  Numero_Comprobante        NUMBER(8)       NOT NULL,
  Fecha_Comprobante         DATE            NOT NULL,
  Monto_Comprobante         NUMBER(12,2)    NOT NULL,
  ID_EstadoComprobante      NUMBER(4)       NOT NULL
);
--
ALTER TABLE t_Comprobante
    ADD CONSTRAINT PK_Comprobante
        PRIMARY KEY (ID_Comprobante);
--
ALTER TABLE t_Comprobante 
    ADD CONSTRAINT FK_Comprobante_EstadoComprobante 
        FOREIGN KEY (ID_EstadoComprobante)
        REFERENCES t_EstadoComprobante (ID_EstadoComprobante);
--

Nos encontrábamos con el siguiente mensaje de error.

ORA-00972: identifier is too long
00972. 00000 -  "identifier is too long"
*Cause:    An identifier with more than 30 characters was specified.
*Action:   Specify at most 30 characters.

La limitación de 30 caracteres obligaba a utilizar abreviaturas para nombrar a identificadores, haciendo poco legibles (y hasta inentendibles) ciertos nombres.

Identificadores Largos (hasta 128 bytes) en Oracle 12c R2

La nueva versión de Oracle soporta identificadores de hasta 128 bytes, por lo que de ejecutar el script anterior en una base de datos 12c R2, obtendremos el siguiente resultado:

Table created.
Table altered.
Table created.
Table altered.
Table altered.

Si consultamos la estructura de las tablas del diccionario de datos, podemos ver que la mayoria de las columnas de las vistas del diccionario de datos que contienen identificadores ahora tienen un tamaño de 128 bytes:

SQL> desc all_tables
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(128)
 TABLE_NAME                                NOT NULL VARCHAR2(128)
 TABLESPACE_NAME                                    VARCHAR2(30)
 CLUSTER_NAME                                       VARCHAR2(128)
 ...
 ...

Restricciones y Consideraciones

Los siguientes objetos mantienen sus identificadores con el largo como en versiones anteriores.

• 8 Bytes
• Nombre de Base de Datos
• 30 Bytes
• Tablespace
• Disk Group
• PDBs
• Rollback Segment

Otro aspecto a tener en cuenta es que el nuevo tamaño se mide en Bytes, por lo que si nuestra base de datos tiene un set de caracteres UTF8, cada caracter puede ocupar entre 1 y 3 bytes, por lo que el tamaño máximo en caracteres del identificador puede ser menor, como vemos a continuación:

create table tablááááááááááááááááááááááááááááááááááááááááááááááááááááááááááááááá 
(
  Son_67_Caracteres_pero_ocupan_130_bytes int
);

ORA-00972: identifier is too long

Inscripción Abierta para Oracle Developer Tour 2017 en Argentina

El Grupo de Usuarios Oracle de Argentina (ArOUG) anunció que ya se encuentra abierta la inscripción para las jornadas del Oracle Developer Tour Latam 2017.

El evento transcurrirá los días 13 y 14 de Noviembre en la ciudad de Córdoba, siendo la sede la Universidad Tecnológica Nacional - Facultad Regional Córdoba. El primer día será destinado a conferencias, mientras que el segundo se destinará a Laboratorios prácticos a desarrollarse en Aulas de la UTN donde, de la mano de reconocidos Instructores podrás aprender y experimentar con herramientas Oracle.

Descubriremos como Oracle puede ayudarnos a maximizar la productividad de nuestros Proyectos de Software, minimizando costos y tiempos de ejecución a partir del uso de sus últimas y más modernas tecnologías.

Para mas información sobre el evento, tanto en Argentina como en los otros nueve países que en donde se desarrollará este año, recomendamos visitar el sitio oficial del evento.

Registración Online para Argentina AQUI.

Oracle 12c R2 - PRAGMA DEPRECATE

¿Qué es "PRAGMA DEPRECATE"?

La opción de compilador "PRAGMA DEPRECATE" permite marcar a un elemento PL/SQL como obsoleto. El compilador emite advertencias para usos del pragma DEPRECATE o el de elementos obsoletos.

Las advertencias le indican a los usuarios que utilizan un elemento obsoleto que es necesario cambiar el código para tener en cuenta la depreciación.

¿Qué objetos se pueden marcar como obsoletos con "PRAGMA DEPRECATE"?

Se puede marcar como obsoleto objetos de los siguientes tipos:

• Subprograms
• Packages
• Variables
• Constants
• Types
• Subtypes
• Exceptions
• Cursors

¿Cómo se utiliza?

Habilitar las advertencias de compilador

Como primer medida, tenemos que habilitar las advertencias de compilador para poder utilizar este nuevo Pragma. Se puede hacer para las advertencias relacionadas con "PRAGMA DEPRECATE" o bien se puede habilitar para todas las advertencias de compilación.

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:(6019,6020,6021,6022)';
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL'; 

Compilar una unidad de programación utilizando ""PRAGMA DEPRECATE"

En el siguiente ejemplo, compilamos un paquete e indicamos que el mismo se encuentra obsoleto al incluir la línea "PRAGMA DEPRECATE" seguida del nombre del paquete.

CREATE OR REPLACE PACKAGE pkg_Deprecado AUTHID DEFINER 
AS
   PRAGMA DEPRECATE (pkg_Deprecado);
   --
   PROCEDURE Procedimiento1;
   --
   FUNCTION Funcion1 RETURN NUMBER;
END pkg_Deprecado;

SP2-0808: Package created with compilation warnings

Dependiendo de la IDE que utilicemos para desarrollar, la advertencia puede mostrarse en forma automática o tendremos que consultar que tipo de advertencia se ha generado:

SQL> SHOW ERRORS
Errors for PACKAGE PKG_DEPRECADO:

LINE/COL ERROR
-------- -------------------------------------------------------
3/4      PLW-06019: entity PKG_DEPRECADO is deprecated

Lo mismo si probamos de marcar como obsoleto sólo a un procedimiento de un paquete:

CREATE OR REPLACE PACKAGE pkg_Codigo AUTHID DEFINER
AS
  PROCEDURE Procedimiento1;
  PRAGMA DEPRECATE (Procedimiento1, 
      'pkg_Codigo.Procedimiento1 deprecado. Utilice pkg_Codigo.Procedimiento2');
  --
  PROCEDURE Procedimiento2;
END;

SP2-0808: Package created with compilation warnings

El mensaje de error ahora nos indica que la entidad que se encuentra marcada como obsoleta es el procedimiento "PROCEDIMIENTO1" y no el paquete completo.

SQL> SHOW ERRORS
Errors for PACKAGE PKG_DEPRECADO:

LINE/COL ERROR
-------- -------------------------------------------------------
4/3      PLW-06019: entity PROCEDIMIENTO1 is deprecated

¿Qué significa cada mensaje de Advertencia?

A continuación tenemos la definición oficial de cada uno de los cuatro mensajes de advertencia o error relacionados con "PRAGMA DEPRECATE"

Nro. de Advertencia	Significado de la Advertencia
6019	La entidad ha sido marcada como obsoleta y puede ser removida en una versión futura. No utilice esta entidad.
6020	La entidad referenciada ha sido marcada como obsoleta y puede ser removida en una versión futura. No utilice esa entidad. Siga las instrucciones especificadas en la advertencia si están disponibles
6021	Pragma mal ubicado. El pragma DEPRECATE debe seguir en forma inmediata a la declaración de la entidad que esta siendo marcada como obsoleta.Ubique al pragma inmediatamente debajo de la declaración de la entidad que esta siendo marcada como obsoleta
6022	Esta entidad no puede ser marcada como obsoleta. Esta funcionalidad sólo aplica a entidades que se declaran en un Paquete, a especificaciones de tipos o a Procedimientos y Funciones. Elimine el pragma.

Advertencia 6019
La advertencia (o error) 6019, como vimos al compilar los dos paquetes anteriores, ocurrirá cada vez que compilemos un objeto que se ecuentra "marcado" como obsoleto con la instrucción "PRAGMA DEPRECATE".

Advertencia 6020
La advertencia (o error) 6020 ocurrirá cada vez que compilemos un objeto que referencia a otro que se encuentra "marcado" como obsoleto con la instrucción "PRAGMA DEPRECATE".

Siguiendo con el ejemplo anterior, si intentamos compilar un procedimiento que referencia al procedimiento "Procedimiento1" del paquete "pkg_Codigo", obtendremos la advertencia 6020:

CREATE OR REPLACE PROCEDURE Usar_Procedimiento_Deprecado
IS
BEGIN
   pkg_codigo.procedimiento1;
END;
/

SP2-0804: Procedure created with compilation warnings

SQL> SHOW ERRORS
Errors for PROCEDURE USAR_PROCEDIMIENTO_DEPRECADO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/4      PLW-06020: reference to a deprecated entity: PROCEDIMIENTO1
         declared in unit PKG_CODIGO[3,13].  pkg_Codigo.Procedimiento1
         deprecado. Utilice pkg_Codigo.Procedimiento2

Este escenario es el mas útil, ya que una vez que un objeto sea marcado como obsoleto, cualquier usuario que compile cualquier unidad de programación que haga referencia al mismo recibirá la advertencia, pudiendo en ese caso modificar su código para utilizar el código vigente en vez del objeto obsoleto.

Advertencia 6021

La advertencia (o error) 6021 ocurrirá si tratamos de compilar un objeto con una sentencia "PRAGMA DEPRECATE" ubicada en un lugar incorrecto, como podemos ver en el siguiente ejemplo:

CREATE OR REPLACE PACKAGE MiPaquete AUTHID DEFINER AS
  --
  PRAGMA DEPRECATE(CalculaTotal);
  --
  CURSOR c_cursor IS
    SELECT dummy FROM dual;
  --
  PROCEDURE CalculaTotal;
  --
END MiPaquete;
/

SP2-0808: Package created with compilation warnings

SQL> SHOW ERRORS
Errors for PACKAGE MIPAQUETE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/10     PLW-06021: PRAGMA DEPRECATE on CALCULATOTAL is misplaced

Advertencia 6022

La advertencia (o error) 6022 indica que estamos tratando de marcar como obsoleto un objeto que no puede ser marcado como obsoleta. Esto puede ocurrir si marcamos como obsoleto un procedimiento o función interna (declarada dentro de otro procedimiento o función), como podemos ver en el siguiente ejemplo:

CREATE OR REPLACE PROCEDURE Error6022
IS
   PROCEDURE Error6022_Interno
   IS
   PRAGMA DEPRECATE (Error6022_Interno, 'Error6022_Interno es Obsoleto');
   BEGIN
     DBMS_OUTPUT.PUT_LINE('Probando Error6022_Interno');
   END;
BEGIN
  --
  DBMS_OUTPUT.PUT_LINE('Probando Error6022');
  --
  Error6022_Interno;
  --
END;

Warning: PROCEDURE ERROR6022
Line: 5 PLW-06022: cannot use PRAGMA DEPRECATE on this entity

Documentación y ejemplos en Oracle Live SQL

• Documentación Oficial.
• Marcar un paquete como Obsoleto.
• Marcar un paquete como Obsoleto con un mensaje de Advertencia.
• Marcar un procedimiento como Obsoleto.
• Marcar una constante y una excepción como Obsoletos.
• Ejemplo de Pragma mal ubicado.

Oracle 12c R2 - Mejoras a PL/SCOPE

¿Que es PL/Scope?

PL/Scope es una herramienta del compilador introducida en Oracle 11g que recopila datos sobre identificadores en el código fuente PL/SQL en tiempo de compilación de unidades de programa. La información recopilada queda disponible en vistas estáticas del diccionario de datos. Los datos recogidos incluyen información sobre tipos de identificadores, sus usos (declaración, definición, referencia, llamada, asignación) y la ubicación de cada uso en el código fuente de nuestros programas.

¿Como se lo habilita?

El parámetro "PLSCOPE_SETTINGS" define el comportamiento de esta herramienta al momento de compilar código PL/SQL. Por defecto, su valor es "IDENTIFIERS:NONE".

Una de las nuevas funcionalidades en Oracle 12.2 es la posibilidad de aceptar otras opciones aparte para indicar el tipo de identificador.

Sintaxis (en negrita las nuevas opciones de 12.2)

IDENTIFIERS : { ALL | NONE | PUBLIC | SQL | PLSQL }
STATEMENTS  : { ALL | NONE } 

Ejemplo

Para habilitar el uso de PL/Scope al momento de compilar, sólo es necesario modificar el parámetro "PLSCOPE_SETTINGS" y crear o modificar una unidad de programación almacenada en la base de datos (procedimiento, función o paquete)

CREATE TABLE t_datos (ID NUMBER(10), Dato VARCHAR(100), Nada VARCHAR(100));
--
ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL';
--
CREATE OR REPLACE PROCEDURE PRUEBA_PLSCOPE(p_numerico IN NUMBER)
AS
  --
  v_numero NUMBER;
  v_texto VARCHAR2(30000);
  --
BEGIN
  --
  SELECT dummy
  INTO v_texto
  FROM dual;
  --
  SELECT COUNT(*) + p_numerico
  INTO v_numero
  FROM dual;
  --
  INSERT INTO t_datos (ID, Dato)
  VALUES (v_numero, v_texto);
  --
END;
/
DROP TABLE t_datos;

¿Cómo se utiliza la información generada por PL/Scope?

Una vez que compilamos una unidad de programación teniendo habilitado PL/Scope, es posible consultar la información generada utilizando las vista estáticas "ALL_IDENTIFIERS" y "ALL_STATEMENTS" ( o sus versiones user_* o dba_*).

SELECT ai.usage_id, ai.usage, ai.type, ai.name, ai.line, ai.col

FROM ALL_IDENTIFIERS ai

WHERE ai.object_name = 'PRUEBA_PLSCOPE'

ORDER BY ai.usage_id;

SELECT a_s.usage_id, a_s.type, a_s.line, a_s.col, a_s.text

FROM ALL_STATEMENTS a_s

WHERE a_s.object_name = 'PRUEBA_PLSCOPE'

ORDER BY a_s.usage_id;

¿Que nuevos tipos de identificadores y sentencias soporta PL/Scope en Oracle 12.2?

Oracle 12c Release 2 incluye los siguientes nuevos tipo de identificadores, antes no tenidos en cuenta por PL/Scope

Identificadores

• ALIAS
• COLUMN
• MATERIALIZED VIEW
• OPERATOR
• TABLE

Sentencias

• SELECT
• UPDATE
• INSERT
• DELETE
• MERGE
• EXECUTE IMMEDIATE
• SET TRANSACTION
• LOCK TABLE
• COMMIT
• SAVEPOINT
• ROLLBACK
• OPEN
• CLOSE
• FETCH

¿Dónde se almacena la información generada por PL/Scope?

La información que PL/Scope recopila al momento de compilar una unidad de programación, es almacenada en el tablespace SYSAUX.

SELECT SPACE_USAGE_KBYTES
FROM V$SYSAUX_OCCUPANTS
WHERE OCCUPANT_NAME='PL/SCOPE';

SPACE_USAGE_KBYTES
------------------
              1088

Referencias e Información Adicional

• Oracle Database Development Guide - Using PL/SCOPE
• Plugin para SQL Developer

Serie de Artículos sobre Oracle 12c Release 2

Siguiendo con la temática que presenté en el Oracle OTN Latin American Tour a principios de agosto (ver post anterior), entre septiembre  y diciembre voy a estar subiendo artículos técnicos sobre nuevas características de Oracle 12c Release 2.

Los primeros temas a tratar son:

• Mejoras a PL/SCOPE.
• PRAGMA DEPRECATE.
• Identificadores de 128 Bytes.
• Utilizar Constantes para definir el tamaño de Variables.
• LISTAGG y OVERFLOW.
• Mejoras a APROXIMATE QUERY.
• Conversiones y Manejo de Error.
• COLLATION.

A medida que publique un articulo, el link estará disponible en esta misma nota.

Oracle Developer Tour 2017 llega a Argentina

Ya están confirmadas las fechas del Oracle Developer Tour (ODT) 2017 en Latinoamérica:

Pais	Ciudad	Fechas
México	Ciudad de México	30/10/2017
Costa Rica	San José	31/10/2017
Panamá	Chitre	01/11/2017
Colombia	Medellín	02/11/17 y 03/11/2017
Ecuador	Quito	07/11/2017
Paraguay	Asunción	08/11/2017
Brasil	Brasilia	09/11/2017 y 10/11/2017
Argentina	Córdoba	13/11/2017 y 14/11/2017
Uruguay	Montevideo	15/11/2017
Chile	Santiago	17/11/2017

En nuestro país, el evento comprenderá dos jornadas en la ciudad de Córdoba, en la sede de la Universidad Tecnológica Nacional – Facultad Regional Córdoba (Ciudad Universitaria, Maestro M. Lopez esq. Cruz Roja s/n).

Para todos los interesados en exponer en alguna de las jornadas, se encuentra abierto el Call for Papers hasta el próximo 2 de Septiembre. Esperamos poder estar presentando una charla sobre nuevas características de Oracle 12c Release 2 en el evento en Argentina.

Próximamente se encontrará abierta la inscripción en el sitio de ArOUG para aquellos que quieran asistir a las jornadas en Argentina.

Para estar al tanto de más novedades, podes seguir la cuenta de Twitter @OracleDevTour del evento.