Oracle Tablespaces Smallfile y Bigfile

Saludos,  

Como ya sabemos los tablespaces son parte de la estructura lógica de la base de datos que puede tener 1 o mas datafiles. Los datafiles son parte de la estructura física de la base de datos que puede estar asignado a 1 tablespace.

Hasta antes de la versión 10g solo existía 1 tipo de Tablespaces conocidó como SmallFile. El nuevo tipo de Tablespaces incluido desde la versión 10g es BigFile.

TABLESPACES SMALLFILE.- Tablespaces tradicional por defecto de Oracle y podrá tener uno o varios Datafile. El Datafile su tamaño será limitado por el parámetro db_block_size(2k, 4k, 8k, 16k y 32k) que se define al crear la base de datos.

TABLESPACES BIGFILE.- Este tipo de tablespace aparece de la versión de la 10g y  presentan la ventaja de tener datafiles de mayor tamaño de hasta 128Tb(Dependera sistema de archivos) pero con la restricción que el tablespace solo puede tener 1 datafile.

Habiendo dicho esto cual seria la ventaja entre SMALLFILE y BIGFILE?

SmallFile fue la primera versión para los Tablespaces y se recomienda su uso para sistemas de archivos que manejan tamaños limitados.

BigFile fue introducido desde la versión 10g y solo puede contener un solo datafile que puede tener un gran tamaño.

Por ejemplo si tenemos una base de datos db_block_size de 8k y necesitamos un tablespace de de tamaño de 1Tb  usando SmallFile vamos a tener que asignar 32 datfiles de 32Gb para poder alcanzar la capacidad de 1Tb mientras que con BigFile solo necesitamos agregar 1 solo de 1Tb el cual puede seguir aumentando de tamaño.

Tablespaces SmallFile

CREATE TABLESPACE TS_SMALLFILE DATAFILE 
  '/u01/app/dbfiles/orcl/ts_smallfile01.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile02.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile03.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile04.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile05.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile06.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile07.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile08.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile09.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile10.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile11.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile12.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile13.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile14.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile15.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile16.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile17.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile18.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile19.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile20.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile21.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile22.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile23.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile24.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile25.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile26.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile27.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile28.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile29.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile30.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile31.dbf' SIZE 32G,
  '/u01/app/dbfiles/orcl/ts_smallfile32.dbf' SIZE 32G   
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Tablespaces BigFile

CREATE BIGFILE TABLESPACE TS_BIGFILE DATAFILE 
  '/u01/app/dbfiles/orcl/ts_bigfile.dbf' SIZE 1024G AUTOEXTEND ON NEXT 100M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Si bien es cierto ambos van a tener la capacidad de crecer pero para la gestión de un DBA va hacer mas fácil gestionar tablespace con 1 solo datafile que con muchos datafile. Está bastante claro que los Tablespaces Bigfile ayudan en la transparencia de los datos, ya que cada Tablespaces tiene solo un archivo de datos, por lo que parece muy claro dónde pueden estar nuestros datos. Además, la base de datos se vuelve más fácil de administrar, ya que tiene que administrar una menor cantidad de archivos de datos. Entonces, si cree que tiene espacio de base de datos para administrar, simplemente hágalo

No existe limitantes para el uso de tablas, índices u otro objeto de base que haga uso de Tablespace.

 

Limitaciones y consideraciones Tablespaces BigFile:

• Los tablespaces Bigfile deben crearse administrados localmente y con administración automática del espacio de segmento. Éstas son las especificaciones predeterminadas. Oracle devolverá un error si se especifica DEXTENT MANAGEMENT DICTIONARY o SEGMENT SPACE MANAGEMENT MANUAL. Pero hay dos excepciones cuando los segmentos de tablespaces de bigfile se administran manualmente:
  • Undo tablespace administrado localmente
  • Espacio de tabla temporal
• Los tablespaces Bigfile deben dividirse para que las operaciones en paralelo no se vean afectadas negativamente. Oracle espera que los tablespaces de bigfile se utilice con Automatic Storage Management (ASM) u otros administradores de volúmenes lógicos que admitan la creación de bandas o RAID.
• Los tablespaces Bigfile no deben usarse en plataformas con restricciones, lo que limitaría la capacidad del tablespaces.
• Evite el uso de tablespaces de archivos grandes si es posible que no haya espacio libre disponible en un grupo de discos y la única forma de ampliar un tablespaces es agregar un nuevo archivo de datos en un grupo de discos diferente.

Referencias

https://docs.oracle.com/cd/E18283_01/server.112/e17120/tspaces002.htm

https://docs.oracle.com/database/121/VLDBG/GUID-7B764F63-C4B4-4D30-9E96-2D6D73CB4122.htm

Querying DBA_JOBS encounter ORA-01873

En la versión de Oracle 19c se me presentó error al tratar de realizar la consulta haca la vista DBA_JOBS.

select * from dba_jobs;
ORA-01873: the leading precision of the interval is too small

Revisando en la documentación de Oracle "Querying DBA_JOBS Encounter ORA-01873 (Doc ID 2710794.1)" se indica que

CAUSA:

Lista de verificación de la herramienta de actualización previa de la base de datos. (ID de documento 2380601.1)A partir de Oracle Database 19c, los trabajos creados y administrados a través del paquete DBMS_JOB en versiones anteriores de la base de datosse volverán a crear utilizando la arquitectura Oracle Scheduler. Es posible que los trabajos que no se hayan vuelto a crear correctamente no funcionen correctamente después de la actualización.

SOLUCION

Ignore la columna TOTAL_TIME y seleccione otras columnas para obtener la información de la consulta DBA_JOBS.

O

Consultando dba_scheduler_jobs y verá todos los trabajos  que fueron creados usando dbms_job. 

select *  from dba_scheduler_jobs;

Como vemos la solución esta en omitir de la consulta el campo TOTAL_TIME de la vista DBA_JOBS o usar DBA_SCHEDULER_JOBS.

Como en mi caso esta solución no me ayudaba de mucho por que no pdodia editar el programa que hacia uso de esa vista lo que realice es editar la vista DBA_JOBS. Si ya se que me diran que no debemos hacaerlo ya que es un objeto del SYS, pero les comento que no todo esta escrito en piedra asi que lo hice y me funcionó.

Deben de conectarse con el SYS para poder realizar la modificación.

CREATE OR REPLACE FORCE VIEW SYS.DBA_JOBS
(
   JOB,
   LOG_USER,
   PRIV_USER,
   SCHEMA_USER,
   LAST_DATE,
   LAST_SEC,
   THIS_DATE,
   THIS_SEC,
   NEXT_DATE,
   NEXT_SEC,
   TOTAL_TIME,
   BROKEN,
   INTERVAL,
   FAILURES,
   WHAT,
   NLS_ENV,
   MISC_ENV,
   INSTANCE
)
   BEQUEATH DEFINER AS
   SELECT m.dbms_job_number                                                                JOB,
          j.creator                                                                        LOG_USER,
          u.name                                                                           PRIV_USER,
          u.name                                                                           SCHEMA_USER,
          j.last_start_date                                                                LAST_DATE,
          SUBSTR ( TO_CHAR ( j.last_start_date, 'HH24:MI:SS' ), 1, 8 )                     LAST_SEC,
          DECODE ( BITAND ( j.job_status, 2 ), 2, j.last_start_date, NULL )                THIS_DATE,
          DECODE ( BITAND ( j.job_status, 2 ), 2, SUBSTR ( TO_CHAR ( j.last_start_date, 'HH24:MI:SS' ), 1, 8 ), NULL )
             THIS_SEC,
          j.next_run_date                                                                  NEXT_DATE,
          SUBSTR ( TO_CHAR ( j.next_run_date, 'HH24:MI:SS' ), 1, 8 )                       NEXT_SEC,
          ( CASE
              WHEN j.last_end_date > j.last_start_date THEN
                 --EXTRACT (
                 --DAY FROM (j.last_end_date - j.last_start_date) * 86400)
          (CAST(j.last_end_date AS DATE)-CAST(j.last_start_date AS DATE)) * 86400
              ELSE
                 0
           END )
             TOTAL_TIME,                                                          -- Scheduler does not track total time
          DECODE ( BITAND ( j.job_status, 1 ), 0, 'Y', 'N' )                               BROKEN,
          DECODE ( BITAND ( j.flags, 1024 + 4096 + 134217728 ), 0, j.schedule_expr, NULL ) INTERVAL,
          j.failure_count                                                                  FAILURES,
          j.program_action                                                                 WHAT,
          j.nls_env                                                                        NLS_ENV,
          j.env                                                                            MISC_ENV,
          NVL ( j.instance_id, 0 )                                                         INSTANCE
     FROM sys.scheduler$_dbmsjob_map m
          LEFT OUTER JOIN sys.obj$ o ON ( o.name = m.job_name )
          LEFT OUTER JOIN sys.user$ u ON ( u.name = m.job_owner )
          LEFT OUTER JOIN sys.scheduler$_job j ON ( j.obj# = o.obj# )
    WHERE o.owner# = u.user#;

Se marca con rojo lo que viene por defecto y genera el error y se marca con azul con lo que se remplazó y funcionó.

Espero les sirva.

DBMS_FILE_TRANSFER - Copiar archivos binarios en la base de datos y entre bases de datos por medio de DBLINK

Introducido desde la versión 10g DBMS_FILE_TRANSFER es una API que nos permite realizar copiados de archivos binario dentro de la base de datos y/o entre base de datos Oracle si necesidad de comandos del sistema operativo.

Estos nos facilita tarea de copiado de archivos en la base o entre base sin tener que ingresar a un cmd o ssh.

Consideraciones

• Hay tener en cuenta que cuando digo archivos binarios me refiero a : Datafiles, Controlfiles, Redologs, Archivelogs, Backups, Exports DMP, ect.
• Los archivos copiado deben de tener tamaño multiple de 512 bytes.
• El archivo a copiar debe de ser menor o igual a 2Tb.
• El usuario debe tener privilegios de lectura en el objeto del directorio de origen y privilegios de escritura en el objeto del directorio de destino.

Procedimientos disponible:

• COPY_FILE (Local a Local)Lee un archivo local y crea una copia del mismo en el sistema de archivos local, es decir en el mimo servidor.
• GET_FILE(De Remota a Local) Conecta con una base de datos remota por medio de un DBLINK para leer un archivo remoto y luego crea una copia del archivo en el sistema de archivos local.
• PUT_FILE(De una Local a Remota) Lee un archivo local y contacta con una base de datos remota por medio de un DBLINK para crear una copia del archivo en el sistema de archivos remoto.

Procedimiento COPY_FILE

Copiado de archivos en el mismo servidor.

Sintaxis

DBMS_FILE_TRANSFER.COPY_FILE(
                               source_directory_object       IN  VARCHAR2,
                               source_file_name              IN  VARCHAR2,
                               destination_directory_object  IN  VARCHAR2,
                               destination_file_name         IN  VARCHAR2);

Parámetro
Descripción
source_directory_object
El objeto de directorio desde el que se copia el archivo en el sistema
 de archivos local. Este objeto de directorio debe existir.
source_file_name
El nombre del archivo que se copia en el sistema de archivos local. 
Este archivo debe existir en el sistema de archivos local en el
 directorio asociado con el objeto del directorio de origen.
destination_directory_object
El objeto de directorio en el que se coloca el archivo en el sistema de
 archivos local. Este objeto de directorio debe existir en el sistema de
 archivos local.
destination_file_name
El nombre del archivo copiado en el objeto de directorio de destino en
 el sistema de archivos local. Un archivo con el mismo nombre no
 debe existir en el directorio de destino del sistema de archivos local.

Ejemplo

--Copiar EXPFULLDB.DMP de la ruta /u01/oradmp/ a /u02/oradmp/
-- Crear directorios para el copiado de un archivo DMP.
CREATE OR REPLACE DIRECTORY dmp_files_dir1 AS '/u01/oradmp/';
CREATE OR REPLACE DIRECTORY dmp_files_dir2 AS '/u02/oradmp/';

-- Copiar
BEGIN
  DBMS_FILE_TRANSFER.copy_file(
   source_directory_object      => 'DMP_FILES_DIR1',--Directorio Origen
   source_file_name             => 'EXPFULLDB.DMP',--Archivo origen a copiar
   destination_directory_object => 'DMP_FILES_DIR2',--Directorio destino
   destination_file_name        => 'EXPFULLDB.DMP'/*Nombre archivo destino*/);
END;
/

Procedimiento GET_FILE
Copiado de archivos desde una base remota hacia la local.
Sintaxis
DBMS_FILE_TRANSFER.GET_FILE(
                           source_directory_object IN VARCHAR2,    
                           source_file_name IN VARCHAR2,  
                           source_database IN VARCHAR2,  
                           destination_directory_object IN VARCHAR2,
                           destination_file_name IN VARCHAR2);
Parámetro
Descripción
source_directory_object
El objeto de directorio desde el que se copia el archivo en el sitio de
 origen. Este objeto de directorio debe existir en el sitio de origen.
source_file_name
El nombre del archivo que se copia en el sistema de archivos
 remoto. Este archivo debe existir en el sistema de archivos remoto en
 el directorio asociado con el objeto del directorio de origen.
source_database
El nombre de un enlace de base de datos a la base de datos remota
 (DBLINK) donde se encuentra el archivo.
destination_directory_object
El objeto de directorio en el que se coloca el archivo en el sitio de
 destino. Este objeto de directorio debe existir en el sistema de
 archivos local.
destination_file_name
El nombre del archivo copiado en el sistema de archivos local. Un
 archivo con el mismo nombre no debe existir en el directorio de
 destino del sistema de archivos local.

Ejemplo.
Desde la base de datos ORCL01 vamos a traer desde la base de datos Remota ORCL02 instanciada con el DBLINK DBL_ORCL02 el archivo de EXPDP_FULL_ORCL02.DMP. El archivo a traer se encuentra en el directorio DATA_PUMP_DIR y lo vamos a copiar en el mismo directorio directorio de la base de datos local.
- Vamos a copiar un DMP desde una base de datos remota hacia la base de datos Local.
- Consigue el archivo.
BEGIN
  DBMS_FILE_TRANSFER.get_file (
   source_directory_object => 'DATA_PUMP_DIR'/*Directorio origen base remota*/,
   source_file_name => 'EXPDP_FULL_ORCL02.DMP'/*Nombre del DMP remoto a descargar*/,
   source_database => 'DBL_ORCL02' /*DBlink de la base de datos remota*/,
   destination_directory_object  => 'DATA_PUMP_DIR'/*Directorio destino local de la base*/,
   destination_file_name => 'EXPDP_FULL_ORCL02.DMP'/*Nombre del DMP destino*/);
END;
/

Procedimiento PUT_FILE
Copiado des archivos desde una base local hacia una base de datos remota.
Sintaxis
DBMS_FILE_TRANSFER.PUT_FILE (
                               source_directory_object IN VARCHAR2,   
                               source_file_name IN VARCHAR2,
                               destination_directory_object IN VARCHAR2,
                               destination_file_name IN VARCHAR2,  
                               destination_database IN VARCHAR2);
Parámetro
Descripción
source_directory_object
El objeto de directorio desde el que se copia el archivo en el sitio de
 origen local. Este objeto de directorio debe existir en el sitio de
 origen.
source_file_name
El nombre del archivo que se copia del sistema de archivos local. Este
 archivo debe existir en el sistema de archivos local en el directorio
 asociado con el objeto del directorio de origen.
destination_directory_object
El objeto de directorio en el que se coloca el archivo en el sitio de
 destino. Este objeto de directorio debe existir en el sistema de
 archivos remoto.
destination_file_name
El nombre del archivo colocado en el sistema de archivos remoto. Un
 archivo con el mismo nombre no debe existir en el directorio de
 destino del sistema de archivos remoto.
destination_database
El nombre de un enlace de base de datos a la base de datos remota en
 la que se copia el archivo.

Ejemplo
Desde la base de datos ORCL01 vamos a enviar el archivo EXPDP_FULL_ORCL01.DMP a una la base de datos Remota ORCL02 instanciada con el DBLINK DBL_ORCL02 . El archivo a enviar se encuentra en el directorio DATA_PUMP_DIR y lo vamos a copiar en el mismo directorio directorio de la base de datos local.
-- Vamos a copiar desde la base de datos local ORCL01 el archivo EXPDP_FULL_ORCL02.DMP
-- y enviarlo a la base de datos remota ORCL02 por medio del DBLINK DBL_ORCL02.

BEGIN
  DBMS_FILE_TRANSFER.put_file (
   source_directory_object => 'DATA_PUMP_DIR'/*Directorio origen local*/,
   source_file_name => 'EXPDP_FULL_ORCL01.DMP'/*Archivo origen local*/,
   destination_directory_object => 'DATA_PUMP_DIR'/*Directorio destino remoto*/,
   destination_file_name => 'EXPDP_FULL_ORCL01.DMP'/*Archivo destino remoto*/,
   destination_database => 'DBL_ORCL02'/*DBLINK*/);
END;/

Listo, ya vimos las tres forma de usar DBMS_FILE_TRANSFER, solo te toca ver cual se adapta a tu necesidad.

Leer datos JSON como Tabla en Oracle - JSON_TABLE

Hola, en un post anterior hable de como extraer información de un dato con formato JSON con la función JSON_VALUE, ahora les quiero mostrar como tratar el formato JSON como si fuera una tabla. JSON_TABLE es una función que viene disponible desde la version 12.1.0.2

Para entenderlo mejor veámoslo con un ejemplo practico, vamos a tener una lista de personas Nombre, Sexo , Edad y Email. 

Para esto vamos a simular que el dato JSON esta en una tabla JSON_DATO con la clausula WITH .

WITH JSON_DATO AS
(
    SELECT '[   {Nombre:"Victor", Sexo:"M", Edad:30}, 
                {Nombre:"Manuel", Sexo:"M", Edad:25}, 
                {Nombre:"Paola" , Sexo:"F", Edad:30} ]' AS JSON_PERSONAS
    FROM DUAL
)
select Personas.*
from JSON_DATO,
JSON_TABLE (JSON_PERSONAS, '$[*]' 
            COLUMNS (   Nombre VARCHAR2 (50) PATH '$.Nombre',
                        Sexo   VARCHAR2 (1 ) PATH '$.Sexo',
                        Edad   NUMBER   (3 ) PATH '$.Edad') 
           )AS Personas;

Este ejemplo tiene el mismo resultado que el anterior, sino que se agrega en el JSON elemento de lista Persona para que vean como varia la ruta de acceso al dato

WITH JSON_DATO AS

(

    SELECT '[   {Persona:[{Nombre:"Victor", Sexo:"M", Edad:30}]}, 

                {Persona:[{Nombre:"Manuel", Sexo:"M", Edad:25}]}, 

                {Persona:[{Nombre:"Paola" , Sexo:"F", Edad:30}]} ]' AS JSON_PERSONAS

    FROM DUAL

)

select Personas.*

from JSON_DATO,

JSON_TABLE(JSON_PERSONAS, '$.Persona[*]' 

           COLUMNS ( Nombre VARCHAR2 (50) PATH '$.Nombre',

                     Sexo   VARCHAR2 (1 ) PATH '$.Sexo',

                     Edad   NUMBER   (3 ) PATH '$.Edad') 

          )AS Personas;

Incluyamos un elemento de dirección que contenga datos de la ubicación. Fíjense en las rutas de acceso como es para acceder al dato dirección.

WITH JSON_DATO AS
(
    SELECT 
    '[  {Persona:[{Nombre:"Victor", Sexo:"M", Edad:30, 

                   Dir:{Pais:"ECU", Ciu:"GYE"} }]

                  }, 
        {Persona:[{Nombre:"Manuel", Sexo:"M", Edad:25, 

                   Dir:{Pais:"ECU", Ciu:"QTO"} }]

                  }, 

        {Persona:[{Nombre:"Paola" , Sexo:"F", Edad:30, 

                   Dir:{Pais:"COL", Ciu:"BOG"} }]

                  } 

     ]' 
            AS JSON_PERSONAS
    FROM DUAL
)
select Personas.*
from JSON_DATO,

JSON_TABLE(JSON_PERSONAS, '$.Persona[*]' 

            COLUMNS (   Nombre VARCHAR2 (50) PATH '$.Nombre',

                        Sexo   VARCHAR2 (1 ) PATH '$.Sexo',

                        Edad   NUMBER   (3 ) PATH '$.Edad',

                        Pais   VARCHAR2 (10) PATH '$.Dir.Pais',

                        Ciudad VARCHAR2 (10) PATH '$.Dir.Ciu') 

         )AS Personas;

Ahora para hacerlo mas interesante vamos a incluir un elemento de Fono que puede tener ninguno o varios numero de teléfonos. Para esto vamos a usar la clausula NESTED PATH para poder acceder a la lista de telefonos que esta dentro del elemento Fono.

WITH JSON_DATO AS
(
    SELECT 
    '[  {Persona:[{Nombre:"Victor", Sexo:"M", Edad:30, 
                   Dir:{Pais:"ECU", Ciu:"GYE"}, 
                   Fono:[{Tipo:"Mobil",Numero:"555-444"},

                         {Tipo:"Casa",Numero:"555-000"}]}]
        }, 
        {Persona:[{Nombre:"Manuel", Sexo:"M", Edad:25, 
                   Dir:{Pais:"ECU", Ciu:"QTO"} }]
        }, 
        {Persona:[{Nombre:"Paola" , Sexo:"F", Edad:30, 
                   Dir:{Pais:"COL", Ciu:"BOG"},
                   Fono:[{Tipo:"Casa",Numero:"333-000"}] }]
        } 
     ]' 
            AS JSON_PERSONAS
    FROM DUAL
)
select Personas.*
from JSON_DATO,
JSON_TABLE(JSON_PERSONAS, '$.Persona[*]' 
            COLUMNS (   Nombre VARCHAR2 (50) PATH '$.Nombre',
                        Sexo   VARCHAR2 (1 ) PATH '$.Sexo',
                        Edad   NUMBER   (3 ) PATH '$.Edad',
                        Pais   VARCHAR2 (10) PATH '$.Dir.Pais',
                        Ciudad VARCHAR2 (10) PATH '$.Dir.Ciu',
                        NESTED PATH '$.Fono[*]'
                            COLUMNS (FonoTipo VARCHAR2(32) PATH '$.Tipo',
                                     FonoNumero VARCHAR2(32) PATH '$.Numero')
                    ) 
         )AS Personas;

Listo, ya tenemos un dato JSON como tabla, espero les sirva!!

Leer datos con formato JSON desde Oracle - JSON_VALUE

Si necesitamos realizar alguna operación con datos que tengan formato JSON en versiones anteriores  a la 12c  hemos tenido que crear nuestras rutinas para leer los datos de este formato, pero ahora desde la version Oracle 12.1.0.2 ya se incluyó utilidades para extrae los datos como JSON_VALUE el cual es una gran utilidad.

JSON_VALUE como primer parámetro recibe la cadena de JSON y segundo la ruta o dirección dé extracción del dato.

https://docs.oracle.com/database/121/SQLRF/functions093.htm#SQLRF56668

Ejemplos

Para un entendimiento practico vamos a realizar un ejemplo, Empecemos con algo básico extraigamos dato Color

SELECT JSON_VALUE('{Color:"Amarillo"}', '$.Color') AS value 

FROM DUAL;

Ahora con varios colores, fijemos del lado de la ruta indicamos el elemento "Color2" para que nos retorne el color.

SELECT JSON_VALUE('{Color1:"Amarillo", Color2:"Azul", Color2:"Rojo"}', '$.Color2') AS value 
FROM DUAL;

Extrae elemento de la lista basado en el índice del elemento, las posiciones de los elementos van desde 0 hasta 2

SELECT JSON_VALUE('["Amarillo", "Azul", "Rojo"]', '$[2]') AS value 
FROM DUAL;

Extraer una propiedad de uno de los elementos de la lista

SELECT JSON_VALUE('[{Color:"Amarillo"}, {Color:"Azul"}, {Color:"Rojo"}]', '$[1].Color') AS value 
FROM DUAL;

Ahora veamos algo mas practico, tendremos una lista de personas Nombre, Sexo y Edad. Para hacer esto el dato JSON  lo voy a poner en una subconsulta y el elemento que extraeré es el de la tercera posición (para el arreglo va a ser la 2).

SELECT  JSON_VALUE(A.DATO_JSON,'$[2].Nombre')   Nombre, 
        JSON_VALUE(A.DATO_JSON,'$[2].Sexo')     Sexo, 
        JSON_VALUE(A.DATO_JSON,'$[2].Edad')     Edad
FROM(
    SELECT '[   {Nombre:"Victor", Sexo:"M", Edad:30}, 
                {Nombre:"Manuel", Sexo:"M", Edad:25}, 
                {Nombre:"Paola" , Sexo:"F", Edad:30} ]' DATO_JSON
    FROM DUAL
)A;

Ahora agreguemos un campo mas "Email" en el dato JSON, pero que "Paola" no lo tenga para ver el resultado . 

SELECT  JSON_VALUE(A.DATO_JSON,'$[2].Nombre')   Nombre, 
        JSON_VALUE(A.DATO_JSON,'$[2].Sexo')     Sexo, 
        JSON_VALUE(A.DATO_JSON,'$[2].Edad')     Edad,
        JSON_VALUE(A.DATO_JSON,'$[2].Email')    Email
FROM(
    SELECT '[   {Nombre:"Victor", Sexo:"M", Edad:30, Email:"victor@hola.com"}, 
                {Nombre:"Manuel", Sexo:"M", Edad:25, Email:"manuel@hola.com"}, 
                {Nombre:"Paola" , Sexo:"F", Edad:30} ]' DATO_JSON
    FROM DUAL
)A;

Lo que ocurrió es que aunque el el campo no exista lo va a presentar NULO. Tambien podemos manejo eventos de datos nulos o errores para que en caso de que ocurra presente un default.

SELECT  JSON_VALUE(A.DATO_JSON,'$[2].Nombre')   Nombre, 
        JSON_VALUE(A.DATO_JSON,'$[2].Sexo')     Sexo, 
        JSON_VALUE(A.DATO_JSON,'$[2].Edad')     Edad,
        JSON_VALUE(A.DATO_JSON,'$[2].Email' DEFAULT 'No definido' ON ERROR)    Email
FROM(
    SELECT '[   {Nombre:"Victor", Sexo:"M", Edad:30, Email:"victor@hola.com"}, 
                {Nombre:"Manuel", Sexo:"M", Edad:25, Email:"manuel@hola.com"}, 
                {Nombre:"Paola" , Sexo:"F", Edad:30} ]' DATO_JSON
    FROM DUAL
)A;

Espero les sirva de ayuda

Lectura de datos de un XML Oracle.

Como podemos dar lectura a los datos de un XML, bueno para esto vamos hacer uso de funciones propias de Oacle para lograr este objetivo.

Veamos el caso con un ejemplo, tenemos un XML con datos de paises, vemos que como primer elemento esta <datos> y como elementos hijos están los países y con los países lo datos relacionados(id, cod, nombre). Dentro del dato hay dos pisases.

<datos>

    <pais>

        <id>1</id>

        <cod>ECU</cod>

        <nombre>Ecuador</nombre>

    </pais>

    <pais>

        <id>2</id>

        <cod>COL</cod>

        <nombre>Colombia</nombre>

    </pais>

</datos>

 

El formato XML lo vamos a tranformar en una estructura que permita tratarlo como una tabla y con ello podamos realizar consultas.

SELECT * 

FROM TABLE (XMLSEQUENCE (EXTRACT (XMLTYPE.CREATEXML (

'<datos>

    <pais>

        <id>1</id>

        <cod>ECU</cod>

        <nombre>Ecuador</nombre>

    </pais>

    <pais>

        <id>2</id>

        <cod>COL</cod>

        <nombre>Colombia</nombre>

    </pais>

</datos>'                                   ), '/'))) PAIS

Si ejecutamos el sql solo nos devolverá un solo registro con el campo COLUMN_VALUE con el formato XML.

Antes de avanzar para ver como recorrer los registro que conforman el XML primero les explicaré como fue que se armo la consulta con las funciones utilizadas del lado del from.

• XMLTYPE.CREATEXML Convierte texto con formato XML en un tipo de dato XML. Utilizamos el XML de países de ejemplo.
• EXTRACT Extraemos los elementos del XML en el nivel /, todo el XML.
• XMLSEQUENCE Extrae los nodos dentro del XML.
• TABLE Transforma en tabla los resultados del XMLSEQUENCE.

Ahora  si, ya explicado las funciones del lado del from vamos a obtener  cada uno de los registro de país. Para ellos vamos a indicar en el EXTRACT el parametro '/datos/pais' para que así recorramos todos los elementos de país.

SELECT * 

FROM TABLE (XMLSEQUENCE (EXTRACT (XMLTYPE.CREATEXML (

'<datos>

    <pais>

        <id>1</id>

        <cod>ECU</cod>

        <nombre>Ecuador</nombre>

    </pais>

    <pais>

        <id>2</id>

        <cod>COL</cod>

        <nombre>Colombia</nombre>

    </pais>

</datos>'                                   ), '/datos/pais'))) PAIS

El resultado sera todos los elementos de pais, pero todavía nos falta obtener el dato de cada campo de país, para ello vamos a usar la función ExtractValue para obtener cada uno de los campos del registro.

SELECT  ExtractValue(Value(PAIS),'/pais/id/text()')id

       ,ExtractValue(Value(PAIS),'/pais/cod/text()')cod 

       ,ExtractValue(Value(PAIS),'/pais/nombre/text()')nombre 

FROM TABLE (XMLSEQUENCE (EXTRACT (XMLTYPE.CREATEXML (

'<datos>

    <pais>

        <id>1</id>

        <cod>ECU</cod>

        <nombre>Ecuador</nombre>

    </pais>

    <pais>

        <id>2</id>

        <cod>COL</cod>

        <nombre>Colombia</nombre>

    </pais>

</datos>'                                   ), '/datos/pais'))) PAIS

Nota:

XMLSEQUENCE  es una función que se uso desde la versión 10g  y sigue funcionando en versiones superiores pero en la versión 11g se incluyó la XMLTable  como una mejora.