Nuevos tipos de datos PL/SQL soportados desde la versión 12c al asociar datos vía SQL (SQL binding)

Anteriormente a la versión 12c de la base de datos Oracle, siempre que era necesario asociar una variable a una expresión PL/SQL mediante el uso de la sentencia EXECUTE IMMEDIATE o del paquete DBMS_SQL, el tipo de datos PLSQL de dicha expresión debía ser un tipo de dato SQL permitido. Concretamente, no era posible asociar datos de tipo BOOLEAN, ni tampoco tipos de datos definidos por el usuarios que hubiesen sido declarados en la especificación de un paquete PL/SQL, incluyendo registros y colecciones.

En la versión 12c se han eliminado prácticamente todas las restricciones de este tipo. Ahora, por ejemplo, se pueden asociar datos de tipo BOOLEAN para ejecutar un bloque de PLSQL dinámico con el comando EXECUTE INMEDIATE. Además, también se pueden asociar matrices asociativas y utilizarlas dentro de una llamada al operador TABLE. Ambas cosas no estaban soportadas por versiones anteriores a la 12c.

Como siempre, lo más recomendable es comprobar el funcionamiento de las nuevas funcionalidades mediante algunos ejemplos. En el primero de ellos se puede ver como ejecutar un bloque de PLSQL dinámico que utiliza un tipo de dato BOOLEAN.

CREATE OR REPLACE PACKAGE empleados_pkg
AS
  TYPE lista_empleados_t 
    IS TABLE OF VARCHAR2 (30);

  PROCEDURE es_jefe (
      empleados_in IN lista_empleados_t, 
      es_jefe_in IN BOOLEAN);
END;
/

CREATE OR REPLACE PACKAGE BODY empleados_pkg
AS
  PROCEDURE es_jefe (
    empleados_in IN lista_empleados_t, 
    es_jefe_in IN BOOLEAN)
  IS
  BEGIN
    FOR indx IN 1 .. empleados_in.COUNT
    LOOP
      DBMS_OUTPUT.put_line (
        CASE WHEN es_jefe_in THEN 'Jefecillo ' END 
        || empleados_in (indx));
    END LOOP;
  END;
END;
/

DECLARE
  l_empleados empleados_pkg.lista_empleados_t
    := empleados_pkg.lista_empleados_t (
       'Juan', 
       'Pepe', 
       'Roberto');
BEGIN
  /* Solo para Oracle Database 12c o posterior */
  EXECUTE IMMEDIATE 
   'BEGIN empleados_pkg.es_extranjero(:l, :s); END;' 
   USING l_empleados, TRUE;
END;
/

La salida sería:

Jefecillo Juan
Jefecillo Pepe
Jefecillo Roberto

En el segundo ejemplo podemos ver como se puede utilizar una sentencia SELECT para devolver los datos desde una matriz asociativa.

CREATE OR REPLACE PACKAGE apellidos_pkg
  AUTHID CURRENT_USER
AS
  TYPE apellidos_t IS TABLE OF VARCHAR2 (100)
    INDEX BY PLS_INTEGER;

  PROCEDURE listar_apellidos (
    apellidos_in IN apellidos_t);
END apellidos_pkg;
/

CREATE OR REPLACE PACKAGE BODY apellidos_pkg
AS
  PROCEDURE listar_apellidos (
    apellidos_in IN apellidos_t)
  IS
  BEGIN
    FOR indx IN 1 .. apellidos_in.COUNT
    LOOP
      DBMS_OUTPUT.put_line (apellidos_in (indx));
    END LOOP;
  END;
END apellidos_pkg;
/

DECLARE
  l_apellidos apellidos_pkg.apellidos_t;
BEGIN
  l_apellidos (1) := 'Pérez';
  l_apellidos (2) := 'González';
  l_apellidos (3) := 'López';

  EXECUTE IMMEDIATE 
    'BEGIN apellidos_pkg.listar_apellidos ' ||
    '(:apellidos); END;' 
    USING l_apellidos;

  FOR reg IN (SELECT * FROM table (l_apellidos))
  LOOP
    DBMS_OUTPUT.put_line (reg.COLUMN_VALUE);
  END LOOP;
END;
/

La salida sería:

Pérez
González
López
Pérez
González
López

Más sobre tipos de datos SQL en la versión 12c

Anteriormente a la versión 12c de la base de datos Oracle, la longitud máxima del tipo de dato VARCHAR2 en SQL era de solo 4.000 bytes, mientras que el PLSQL dicho valor era 32.767 bytes. En la versión 12c ambos tamaños se han homogeneizado y la longitud máxima para los tipos de dato VARCHAR2 y NCARCHAR2 es 32.767 bytes tanto en SQL como PLSQL.

Nota: eso sí, para poder utilizar estas longitudes máximas en SQL es necesario cambiar el valor del parámetro de la base de datos MAX_STRING_SIZE a EXTENDED (su valor por defecto es STANDARD).

Para más información sobre los tipos de datos en SQL y PL/SQL podéis dirigiros a este enlace.