Generando JSON desde PL/SQL con APEX_JSON: objetos, arrays y cursores

Introducción

En el artículo anterior vimos qué es APEX_JSON y por qué conviene usarlo en lugar de concatenar strings. Ahora vamos a la parte práctica: cómo generar JSON de verdad, con estructura, con arrays, con objetos anidados y con datos que vienen de la base de datos.

El patrón de generación de APEX_JSON funciona como un escritor que va construyendo el documento de afuera hacia adentro: abrís un objeto, escribís pares clave-valor, abrís un array dentro, escribís sus elementos, cerrás el array y cerrás el objeto. El package mantiene un contexto interno que sabe en qué nivel de la estructura estás, así que no podés olvidarte de cerrar un nivel sin que el resultado sea inválido.

Esta entrega cubre todas las funciones de generación que vas a usar en el día a día: desde el par open_object/close_object más básico hasta el caso estrella de convertir un REF CURSOR entero en JSON con una sola línea de código.

`open_object` / `close_object` — la estructura básica

Cualquier JSON que no sea un simple array comienza y termina con un objeto. En APEX_JSON, eso se expresa con open_object y close_object:

declare
  l_output clob;
begin
  APEX_JSON.initialize_clob_output; -- inicializar buffer explícito

  APEX_JSON.open_object;
  APEX_JSON.write('pais', 'Paraguay');
  APEX_JSON.write('capital', 'Asunción');
  APEX_JSON.close_object;

  l_output := APEX_JSON.get_clob_output;
  APEX_JSON.free_output;

  DBMS_OUTPUT.put_line(l_output);
end;
-- Resultado: {"pais":"Paraguay","capital":"Asunción"}

La indentación dentro del bloque es puramente cosmética — PL/SQL no le presta atención — pero ayuda mucho a visualizar la estructura del JSON resultante mientras escribís el código. Adoptá ese hábito desde el principio y vas a ahorrarte horas de debugging.

open_object acepta opcionalmente un nombre de clave cuando el objeto es el valor de una propiedad dentro de otro objeto. Por ejemplo, APEX_JSON.open_object('direccion') abre un objeto anidado bajo la clave "direccion". Lo vamos a usar extensivamente en la sección de objetos anidados.

`open_array` / `close_array` — listas de valores

Cuando necesitás representar una lista de elementos, usás open_array y close_array. Funciona igual que los objetos, pero los elementos no tienen clave — solo valores:

declare
  l_output clob;
begin
  APEX_JSON.initialize_clob_output; -- inicializar buffer explícito
  APEX_JSON.open_object;
  APEX_JSON.write('ciudad', 'Encarnación');
  APEX_JSON.open_array('barrios');
  APEX_JSON.write('Centro');
  APEX_JSON.write('San Isidro');
  APEX_JSON.write('Pacú Cuá');
  APEX_JSON.close_array;
  APEX_JSON.close_object;

  l_output := APEX_JSON.get_clob_output;
  APEX_JSON.free_output;

  DBMS_OUTPUT.put_line(l_output);
end;
-- Resultado: {"ciudad":"Encarnación","barrios":["Centro","San Isidro","Pacú Cuá"]}

Al igual que open_object, open_array puede recibir un nombre de clave cuando el array es una propiedad de un objeto padre. Y puede recibir también ningún argumento cuando es el elemento raíz del JSON — por ejemplo, para devolver una lista directamente sin objeto envolvente.

`write` — tipos soportados

La función write está sobrecargada para los tipos de datos más comunes de PL/SQL. Podés pasar sin conversión explícita:

VARCHAR2 — se escapa automáticamente (comillas, barras, caracteres de control)
NUMBER — se serializa sin comillas
DATE y TIMESTAMP — se convierten a string ISO 8601 ("2024-03-15T00:00:00Z")
BOOLEAN — se convierte a true o false (sin comillas)
NULL — se escribe como null JSON, pero solo si pasás p_write_null => TRUE; por defecto los nulls se omiten

DECLARE
    l_output  CLOB;
    l_nulo    VARCHAR2(1) := NULL;
BEGIN
    APEX_JSON.initialize_clob_output;

    APEX_JSON.open_object;
        APEX_JSON.write('activo',  TRUE);
        APEX_JSON.write('puntaje', 9.75);
        APEX_JSON.write('fecha',   DATE '2024-06-15');
        APEX_JSON.write('codigo',  l_nulo);  -- NULL tipado como VARCHAR2
    APEX_JSON.close_object;

    l_output := APEX_JSON.get_clob_output;
    APEX_JSON.free_output;

    DBMS_OUTPUT.put_line(l_output);
END;
-- Resultado: {"activo":true,"puntaje":9.75,"fecha":"2024-06-15T00:00:00Z","codigo":null}

Tip: Por defecto `write` omite las propiedades con valor `NULL`. Esto es cómodo la mayoría de las veces, pero cuidado cuando tu consumidor espera que el campo esté presente aunque sea como `null`. En ese caso, pasá el tercer parámetro `p_write_null => TRUE`.

Objetos anidados

Los objetos y arrays se pueden anidar libremente. La regla es simple: cada open_* debe tener su close_* correspondiente, y el orden de cierre debe ser el inverso al de apertura (como una pila). Si cerrás en el orden equivocado, el JSON resultante queda inválido — y APEX_JSON no va a lanzar un error en ese caso, simplemente vas a obtener JSON malformado.

DECLARE
    l_output  clob;
BEGIN
    APEX_JSON.initialize_clob_output;

    APEX_JSON.open_object;
      APEX_JSON.write('empresa', 'Distribuidora SA');
      APEX_JSON.open_object('contacto');
        APEX_JSON.write('nombre',   'Rodrigo Vera');
        APEX_JSON.write('telefono', '0971-555-123');
        APEX_JSON.open_array('emails');
          APEX_JSON.write('rodrigo@distribuidora.com.py');
          APEX_JSON.write('rodo.vera@gmail.com');
        APEX_JSON.close_array;
      APEX_JSON.close_object;
    APEX_JSON.close_object;

    l_output := APEX_JSON.get_clob_output;
    APEX_JSON.free_output;

    DBMS_OUTPUT.put_line(l_output);
END;

El resultado es:

{"empresa":"Distribuidora SA","contacto":{"nombre":"Rodrigo Vera","telefono":"0971-555-123","emails":["rodrigo@distribuidora.com.py","rodo.vera@gmail.com"]}}

`write_raw` — incrustar JSON ya generado

A veces tenés un fragmento de JSON ya armado (por ejemplo, almacenado en una columna CLOB, o devuelto por otra función) y querés incrustarlo dentro de un objeto más grande sin que APEX_JSON lo re-escape como string.

Para eso existe write_raw:

DECLARE
    l_output  clob;
    l_detalle VARCHAR2(200) := '{"lineas":3,"importe":750000}';
BEGIN
    APEX_JSON.initialize_clob_output;
    APEX_JSON.open_object;
      APEX_JSON.write('pedido_id', 1042);
      APEX_JSON.write_raw('resumen', l_detalle);
    APEX_JSON.close_object;

    l_output := APEX_JSON.get_clob_output;
    APEX_JSON.free_output;

    DBMS_OUTPUT.put_line(l_output);
END;
-- Resultado: {"pedido_id":1042,"resumen":{"lineas":3,"importe":750000}}

Si hubieras usado write en lugar de write_raw, el JSON interno se habría escapado como string: "resumen":"{\"lineas\":3,\"importe\":750000}" — que no es lo que querés.

El caso estrella: convertir un REF CURSOR a JSON en una línea

Esta es probablemente la función más poderosa del lado de generación: APEX_JSON.write acepta un REF CURSOR como argumento y lo serializa completamente como array de objetos JSON. Una línea de código hace todo el trabajo.

DECLARE
    l_cur SYS_REFCURSOR;
BEGIN
    OPEN l_cur FOR
        SELECT id, nombre, precio_venta AS precio
          FROM productos
         WHERE activo = 'S'
         ORDER BY nombre;

    APEX_JSON.open_object;
      APEX_JSON.write('productos', l_cur);
    APEX_JSON.close_object;
    -- l_cur queda cerrado automáticamente
END;

El cursor se convierte en un array donde cada fila es un objeto y cada columna es una propiedad. Los alias de columna (precio_venta AS precio) se convierten en las claves del JSON, así que podés controlar los nombres fácilmente desde el SELECT.

`initialize_clob_output` / `get_clob_output` / `free_output`

Por defecto, APEX_JSON escribe a DBMS_OUTPUT. Cuando necesitás capturar el resultado en una variable — para guardarlo en una tabla, pasarlo a otra función, o devolverlo desde un proceso APEX — tenés que redirigir la salida a un CLOB interno.

El trío de funciones para esto es:

DECLARE
    l_output CLOB;
BEGIN
    APEX_JSON.initialize_clob_output;

    APEX_JSON.open_object;
      APEX_JSON.write('sucursal', 'Casa Central');
      APEX_JSON.open_array('productos');
        APEX_JSON.open_object;
          APEX_JSON.write('codigo', 'P001');
          APEX_JSON.write('descripcion', 'Arroz 1kg');
          APEX_JSON.write('precio', 15000);
        APEX_JSON.close_object;
        APEX_JSON.open_object;
          APEX_JSON.write('codigo', 'P002');
          APEX_JSON.write('descripcion', 'Azúcar 1kg');
          APEX_JSON.write('precio', 12500);
        APEX_JSON.close_object;
      APEX_JSON.close_array;
    APEX_JSON.close_object;

    l_output := APEX_JSON.get_clob_output;
    APEX_JSON.free_output;

    DBMS_OUTPUT.put_line(l_output);
END;

El flujo siempre es el mismo: initialize_clob_output → generar JSON → get_clob_output → free_output. El orden importa, y nunca hay que saltarse free_output.

Tip: El error más común es olvidar `free_output` al usar CLOB — causa memory leaks silenciosos. El CLOB interno queda en la memoria de la sesión sin liberarse, y si el procedimiento se ejecuta muchas veces (un job nocturno, un proceso masivo), la sesión puede crecer indefinidamente hasta que la base de datos la mate. Siempre poné `free_output` en un bloque `EXCEPTION` también, para garantizar la liberación incluso si algo falla.

-- Patrón defensivo con free_output garantizado
BEGIN
    APEX_JSON.initialize_clob_output;
    -- ... generación ...
    l_output := APEX_JSON.get_clob_output;
EXCEPTION
    WHEN OTHERS THEN
        APEX_JSON.free_output;
        RAISE;
END;
APEX_JSON.free_output; -- también en el flujo normal

Conclusión

Con estas funciones tenés todo lo necesario para generar cualquier estructura JSON desde PL/SQL: desde el objeto más simple hasta respuestas complejas con arrays anidados y datos de múltiples tablas. La clave está en mantener el modelo mental de apertura/cierre anidado — y en no olvidar free_output cuando usás CLOB.

open_object / close_object — la estructura básica

open_array / close_array — listas de valores

write — tipos soportados