miércoles, 28 de agosto de 2019

Oracle 19c - Otras Mejoras en el manejo de JSON

Soporte GeoJSON

Oracle 19c permite usar “JSON_VALUE” para devolver objetos de tipo “SDO_GOMETRY” a partir de documentos JSON que poseen información en formato GeoJSON. La sintaxis es la siguiente:

SELECT JSON_VALUE(data, '$.features[0].geometry'
                  RETURNING SDO_GEOMETRY
                  ERROR ON ERROR)
FROM json_documents;

Y da como resultado:

JSON_VALUE(DATA,'$.FEATURES[0].GEOMETRY'RETURNINGSDO_GEOMETRYERRORONERROR)(SDO_GTYPE, SDO_SRID, SDO_
----------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-.15380859, 51.5053234, NULL), NULL, NULL)

Y se pueden crear índices en los mismos

CREATE INDEX json_documents_geo_idx
  ON json_documents (JSON_VALUE(data, '$.features[0].geometry'
                     RETURNING SDO_GEOMETRY))
  INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Mapeo JSON / SQL Object Type

Oracle 19c permite usar “JSON_VALUE” para devolver objetos de tipo SQL definido por el usuario, a partir de documentos JSON:

Ejemplo:

CREATE TABLE DocumentoJSON ( 
      ID_DocumentoJSON NUMBER(10) NOT NULL, 
      Datos CLOB, 
      CONSTRAINT PK_DocumentoJSON PRIMARY KEY (ID_DocumentoJSON),
      CONSTRAINT CK_DocumentoJSON_DatosJSON CHECK(Datos IS JSON)
);
INSERT INTO DocumentoJSON 
 VALUES (1, '{"marca":"Ford", "modelo":"Mustang", "cantidad":3}');
INSERT INTO DocumentoJSON 
 VALUES (2, '{"marca":"Chevrolet", "modelo":"Camaro", "cantidad":5}');
INSERT INTO DocumentoJSON 
 VALUES (3, '{"marca":"Dodge", "modelo":"Charger", "cantidad":1}');
--
COMMIT;
--
CREATE OR REPLACE TYPE ut_Auto AS OBJECT (
  MARCA VARCHAR2(100),
  MODELO VARCHAR2(100),
  CANTIDAD NUMBER(5));
/
--
SELECT JSON_VALUE(Datos, '$' RETURNING ut_Auto) AS Auto
FROM DocumentoJSON
WHERE ID_DocumentoJSON = 1;

El resultado que obtendremos es:

AUTO(MARCA, MODELO, CANTIDAD)
-----------------------------------------------------------------------
UT_AUTO(FORD, MUSTANG,3)


La cláusula "ON MISMATCH" permite generar un error, devolver el mismo o devolver nulo cuando alguno de los elementos del documento JSON no coincide con la estructura del objeto. La sintaxis de la misma es:

JSON_value_on_mismatch ( ( IGNORE | ERROR | NULL ) 
    ON MISMATCH  [  ( (MISSING DATA) | (EXTRA DATA) | (TYPE ERROR) )  ]
  )...

Se puede realizar este mismo tipo de operación con tablas (NESTED TABLES). Y se puede realizar la operación inversa, convertir un objeto SQL en JSON.

Cláusula SQL NESTED

Cuando usamos la función JSON_TABLE, Oracle internamente realiza un INNER JOIN entre la tabla origen y el resultado de la función. Por lo tanto, si una fila de la tabla no posee datos JSON, la misma no es visualizada.

Una forma de solucionar el problema hasta Oracle 19c era realizar un LEFT JOIN, como vemos en el siguiente ejemplo:

SELECT j.id, jt.marca, jt.modelo
FROM   DocumentoJSON j LEFT OUTER JOIN JSON_TABLE(j.Datos, '$'
         COLUMNS (marca    VARCHAR2(50 CHAR)  PATH marca,
                  modelo   VARCHAR2(50 CHAR)  PATH modelo)) jt ON 1=1;

Esto puede reemplazarse en Oracle 19c por la más legible cláusula “NESTED”:

SELECT j.id, jt.marca, jt.modelo
FROM   DocumentoJSON j NESTED datos
         COLUMNS (marca    VARCHAR2(50 CHAR)  PATH marca,
                  modelo   VARCHAR2(50 CHAR)  PATH modelo) jt;

lunes, 26 de agosto de 2019

Oracle 19c - Mejoras en el uso de JSON_TABLE en Vistas Materializadas



Oracle 18c permite usar “JSON_TABLE” en vistas materializadas con la opción de refresco “ON STATEMENT”.

Oracle 19c permite que estas vistas sean utilizadas por consultas que ejecuten funciones “JSON_VALUE” o “JSON_EXISTS” utilizando la funcionalidad de Query Rewrite, lo cual no era posible previamente.



Para esto, la vista debe cumplir con algunas condiciones en particular:

  • La vista materializada debe crearse con “REFRESH FAST ON STATEMENT” e incluir el ROWID o la clave primaria.
  • La vista materializada solo puede ser un JOIN entre la tabla maestra y "JSON_TABLE".
  • Solo las columnas de "JSON_TABLE" definidas como “ERROR ON ERROR NULL ON EMPTY” se consideran para reescribir.
  • La funcionalidad admite notación de puntos, llamadas a "JSON_VALUE" y "JSON_EXISTS", que pueden reescribirse con llamadas a JSON_TABLE y, por lo tanto, son aplicables para una re-escritura para usar la vista materializada.

viernes, 23 de agosto de 2019

Oracle 19c - Mejoras a JSON_OBJECT


Oracle 19c introduce algunas mejoras significativas en el manejo de datos en formato JSON. En este artículo en particular voy a explicar las mejoras introducidas a la función JSON_OBJECT.

En los siguientes ejemplos, vamos a utilizar una tabla llamada Producto que podemos crear con el script incluido a continuación:


CREATE TABLE Producto ( 
      ID NUMBER(10) NOT NULL, 
      Marca VARCHAR(100) NOT NULL, 
      Nombre VARCHAR(100) NOT NULL, 
      Precio NUMBER(10,4) NOT NULL, 
      CONSTRAINT PK_Producto PRIMARY KEY (ID)
);
--
INSERT INTO Producto VALUES (1, 'Sony', 'TV LED 40"', 20000);
INSERT INTO Producto VALUES (2, 'Philips', 'TV LED 32"', 15000 );
INSERT INTO Producto VALUES (3, 'Motorola', 'Moto Z4', 25000);
--
COMMIT;


 Uso de Comodín

Es posible utilizar el comodín “*” para referenciar todas las columnas en un solo paso. Se puede usar el mismo para toda la consulta o para una tabla en particular usando un alias:


SELECT JSON_OBJECT(t.*) AS json_data
FROM <Tabla> t;

Ejemplo

SELECT JSON_OBJECT(p.*) AS json_data
FROM Producto p;


Lista de Columnas

Es posible utilizar una lista de columnas separadas por coma. Los nombres de los elementos mantienen las mayúsculas / minúsculas definidas en la lista:

SELECT JSON_OBJECT(columnaA, ColumnaB) AS json_data
FROM <Tabla> t;

Ejemplo

SELECT JSON_OBJECT(Nombre, Precio) AS json_data
FROM Producto p;


Sintaxis KEY ... VALUE simplificada

No es necesario utilizar la sintaxis ‘KEY … VALUE’, pudiéndose usar directamente “:” para definir los pares de datos:

SELECT JSON_OBJECT('ID' : id,
                   'Nombre' : name) AS json_data
FROM persons;

Ejemplo


SELECT JSON_OBJECT('ID':id, 
                   'Producto':nombre) AS json_data_new
FROM Producto p;




miércoles, 21 de agosto de 2019

Oracle 19c - Nueva Función JSON_SERIALIZE


Oracle 19c introduce algunas mejoras significativas en el manejo de datos en formato JSON. En este artículo en particular voy a explicar el funcionamiento de la función JSON_SERIALIZE.

La misma nos permite convertir un documento JSON, cualquiera sea su formato (BLOB, RAW, CLOB) en texto

La sintaxis de la función es la siguiente:
JSON_SERIALIZE (target_expr [ json_query_returning_clause ] [ PRETTY ]
                [ ASCII ] [ TRUNCATE ] [ json_query_on_error_clause ])
  • La cláusula "RETURNING" así como las opciones “PRETTY”, “ASCII” y “TRUNCATE” se comportan igual a lo visto en la función JSON_MERGEPATCH.
  • La función JSON_SERIALIZE permite el uso de la clausula “ON ERROR” para definir que comportamiento tomar en caso de que encuentre un error al ejecutarse.
  • Al igual que la función “TREAT(… AS JSON)” y "JSON_ MERGEPATCH", es una función SQL que no puede emplearse en forma directa en PL/SQL.

Ejemplos de Uso:

Primero, vamos a crear una tabla conteniendo algunos documentos JSON sencillos en una columna BLOB:
CREATE TABLE BLOBJSON ( 
      ID NUMBER(10) NOT NULL, 
      Datos BLOB, 
      CONSTRAINT PK_BLOBJSON PRIMARY KEY (ID),
      CONSTRAINT CK_BLOBJSON_DatosJSON CHECK(Datos IS JSON)
);
INSERT INTO BLOBJSON VALUES (1, '{"marca":"Ford", "modelo":"Mustang", "cantidad":3}');
INSERT INTO BLOBJSON VALUES (2, '{"marca":"Chevrolet", "modelo":"Camaro", "cantidad":5}');
INSERT INTO BLOBJSON VALUES (3, '{"marca":"Dodge", "modelo":"Charger", "cantidad":1}');
--
COMMIT;

Si queremos consultar los datos, no podemos hacerlo ya que se encuentran en formato binario:


Si los datos no son muy grandes, se puede usar la función "UTL_RAW.cast_to_varchar2"para realizar la conversion:
SELECT UTL_RAW.cast_to_varchar2(Datos) AS data2 FROM BLOBJSON;
Y el resultado que obtenemos es el siguiente:


Otra opción es utilizar la función JSON_QUERY para ver el contenido:
SELECT JSON_QUERY(Datos, '$') AS data FROM BLOBJSON;
Y el resultado que obtenemos es el siguiente:

O directamente utilizar la nueva función (en este caso, adicionalmente estamos filtrando la consulta para ver solo un registro):
SELECT JSON_SERIALIZE(d.Datos PRETTY) AS data
FROM   BLOBJSON d
WHERE  d.datos.marca = 'Dodge';
Y el resultado que obtenemos es el siguiente:












lunes, 19 de agosto de 2019

Oracle 19c - Nueva Función JSON_MERGEPATCH


Oracle 19c introduce algunas mejoras significativas en el manejo de datos en formato JSON. En este artículo en particular voy a explicar el funcionamiento de la función JSON_MERGEPATCH.

En Oracle 12c y 18c para modificar un documento JSON era necesario obtenerlo, procesar y aplicar los cambios y luego reemplazar el documento original con la versión modificada. Con “JSON_MERGEPATCH” es posible simplificar el proceso notablemente en algunos casos, ya que esta función permite modificar el contenido de un objeto JSON, ya sea en una sentencia SELECT como en un UPDATE.

La sintaxis de la función es la siguiente:

JSON_MERGEPATCH
   ( target_expr, patch_expr[returning_clause] [PRETTY] [ASCII]
     [TRUNCATE] [on_error_clause] )

  • Si “patch_expr” se refiere a un elemento existente (o a un grupo de elementos), el o los mismos son actualizados con el valor provisto en la expresión, al cual puede ser por ejemplo el valor en blanco.
  • Si “patch_expr” no se refiere a un elemento existente, el mismo es agregado a cada documento.
  • Si “patch_expr” le asigna el valor nulo a un elemento, el mismo es removido de los documentos.
  • La clausula "RETURNING" funciona como en todas las otras funciones de manejo de JSON, y permite definir un tipo de dato.
  • La opción "PRETTY" muestra el resultado con un formato mas legible, aunque mas extenso.
  • La opción "TRUNCATE" indica que el resultado debe ser truncado si es mayor que el tipo de datos definido.
  • La opción "ASCII" convierte cualquier carácter no-ASCII a una secuencia de escape JSON.
  • La función JSON_MERGEPATCH permite el uso de la clausula “ON ERROR” para definir que comportamiento tomar en caso de que encuentre un error al ejecutarse.

Al igual que la función “TREAT(… AS JSON)”, es una función SQL que no puede emplearse en forma directa en PL/SQL. Para hacerlo es necesario embeber la misma en una llamada a SQL, por ejemplo "SELECT JSON_MERGEPATCH(...) INTO xxx FROM DUAL".

La función JSON_MERGEPATCH se puede utilizar tanto en un SELECT como en sentencias UPDATE para actualizar los datos contenidos en un documento JSON.



Ejemplos de Uso:

Primero, vamos a crear una tabla conteniendo algunos documentos JSON sencillos:


    CREATE TABLE DocumentoJSON (
          ID_DocumentoJSON NUMBER(10) NOT NULL,
          Datos CLOB,
          CONSTRAINT PK_DocumentoJSON PRIMARY KEY (ID_DocumentoJSON),
          CONSTRAINT CK_DocumentoJSON_DatosJSON CHECK(Datos IS JSON)
    );
    INSERT INTO DocumentoJSON
        VALUES (1, '{"marca":"Ford", "modelo":"Mustang", "cantidad":3}');
    INSERT INTO DocumentoJSON
        VALUES (2, '{"marca":"Chevrolet", "modelo":"Camaro", "cantidad":5}');
    INSERT INTO DocumentoJSON
        VALUES (3, '{"marca":"Dodge", "modelo":"Charger", "cantidad":1}');
    --
    COMMIT;

    A continuación vamos a consultar los datos, pero reemplazando el elemento "cantidad" con el valor 500:

    SELECT JSON_MERGEPATCH(Datos, '{"cantidad":500}') AS DatosModificados
    FROM   DocumentoJSON;
    Y el resultado que obtenemos es el siguiente:


    A continuación vamos a consultar nuevamente los datos, pero agregando el elemento "Marca" (recuerden que "marca" ya existe, pero  JSON es case sensitive en cuanto a la definición de los nombres de elementos por lo que lo considera un nuevo elemento):

    SELECT JSON_MERGEPATCH(Datos, '{"Marca":"Skoda"}') AS DatosModificados
    FROM   DocumentoJSON;
    Y el resultado que obtenemos es el siguiente (noten que la cantidad se mantiene tal cual la insertamos originalmente, ya que la función aplicada en el ejemplo anterior fue en una sentencia SELECT y por lo tanto no modificó los datos):




    En este ejemplo, vamos a remover el elemento "marca", asignándole el valor NULL:

    SELECT JSON_MERGEPATCH(Datos, '{"marca":NULL}' PRETTY) AS DatosModificados
    FROM   DocumentoJSON;
    Y el resultado que obtenemos es el siguiente:


    Como ultimo ejemplo, vamos a actualizar los datos en la tabla, agregando el elemento "importado", y luego consultamos los datos tal cual están guardados en la base de datos:

    UPDATE DocumentoJSON dj
    SET Datos = JSON_MERGEPATCH(Datos, '{"importado":"USA"}') ;
    --
    SELECT * FROM DocumentoJSON;
    Y el resultado que obtenemos es el siguiente:



    miércoles, 7 de agosto de 2019

    Resumen de Oracle Groundbreakers Tour 2019 en Argentina

    El evento 

    El 6 de Agosto de realizó, en la sede Recoleta de la Fundación UADE en Buenos Aires, la edición Argentina de Oracle Groundbreakers Tour 2019. Este tour es organizado en conjunto entre Oracle y los grupos de usuarios de latinoamérica, siendo en el caso de Argentina el responsable AROUG.

    El evento contó con la presencia 25 speakers nacionales e internacionales y una asistencia superior a las 120 personas que disfrutaron de charlas técnicas y un Workshop.




    La Apertura 

    En el acto de apertura participaron Rita Nuñez en representación de AROUG, autoridades de UADE y del sponsor principal de evento, Vault Consulting.



    El programa Oracle ACE y la Comunidad Oracle

    Durante la apertura Pablo Cicarello presentó los programas Oracle ACE y Oracle Groundbreakers Ambassador, ambos parte de las iniciativas de Oracle para potenciar la comunidad de usuarios y especialistas de tecnologías de Oracle. Luego de la presentación aprovechó para presentar en público a los nuevos miembros del programa que estaban presentes, en este caso yo mismo y Cristian Peque.



    Durante el transcurso de la mañana, también realicé una entrevista en vivo por Twitter para comentar mi participación en Oracle Community y en el sitio OTN en Español, la cual pueden ver aquí.


    Mi Sesión

    Este año preparé una charla titulada "Working with JSON in Oracle 18c and 19c – Simpler and Quicker!" donde expliqué las mejoras incluidas en las dos últimas versiones de Oracle referentes al almacenamiento y procesamiento de documentos JSON, algo cada más frecuente y requerido.




    Se puede descargar la presentación haciendo click en el enlace de la imagen siguiente:




    El Panel de Expertos y evento de Cierre

    El evento concluyó con un panel donde varios referentes mundiales de Oracle (Mike Dietrich, Markus Michalewicz, Douglas Hood, Nirmala Sundarappa, Anil Nair y Ricardo Gonzalez) respondienon preguntas sobre el estado actual y la dirección futura de las tecnologías Oracle.



    Luego de eso, Oracle University presentó su gama de opciones para capacitaciones (presenciales, online, etc) y certificaciones, y sorteamos dos Certificaciones Oracles entre los presentes.


    La cena de Speakers

    A la noche, como es costumbre, el Grupo de Usuario Oracle de Argentina (AROUG) agasajó a los Speakers con una cena, donde pudimos compartir experiencias y anécdotas mientras los invitados extranjeros disfrutaban (y algunos descubrían) las bondades de la parrilla argentina.




    Imágenes del Evento

    A continuación incluyo algunas otras imágenes del evento









    jueves, 1 de agosto de 2019

    SQL Developer 19.2 ya está disponible (y también Data Modeler, SQLcl, and REST Data Services)



    Jeff Smith, Oracle Product Manager de las herramientas de desarrollo de Oracle Database, anunció en su blog que Oracle SQL Developer 19.2 ya está disponible para ser descargado aquí.

    Algunas de las nuevas características de esta versión:

    • Compatible con Java 11 . También puede ejecutar con Java 12, pero la version probada y certificada es 11. 8. esta disponible una version integrada que incluye Java 8 para Windows 64.
    • ORDS ya no se incluye con SQL Developer. Todavía puede usar los asistentes de Instalador / Ejecutar para ORDS, pero primero deberá descargar el ZIP de OTN.
    • ORDS y la API de DB se han mejorado, especialmente hay endpoints para ejecutar Data Pump Imports y crear nuevas bases de datos con el Asistente de creación de bases de datos.
    • Data Modeler ahora admite versionar sus diseños con Git.
    • SQLcl ahora permite habilitar advertencias de inyección SQL al crear / compilar PL / SQL.
    • SQLcl ahora incluye soporte para Liquibase, incluyendo la creación de registros de cambios.

    Algunos links útiles:

    La documentación oficial todavía no fue actualizada, pero esta disponible para cada version aqui.

    Para consultas y dudas, recomendamos usar el foro de SQL Developer en Oracle Community.