Paquete DBMS_SQL para utilizar SQL dinámico (Dynamic SQL)

El grupo de paquetes DBMS se trata de un conjunto de funciones y procedimientos que el PL/SQL de Oracle incorpora de forma estándar. Estos paquetes DBMS pueden ser de mucha utilidad cuando estamos programando en PLSQL. Hoy voy a hablar sobre el paquete DBMS_SQL que permite utilizar SQL dinámico en procedimientos almacenados y bloques PL/SQL.

Las sentencias de SQL dinámico tienen la característica de que no forman parte del código fuente PL/SQL, sino que están almacenadas dentro de cadenas de caracteres que, bien forman parte de los parámetros de entrada, o bien son construidas durante la ejecución del programa PLSQL. Esto posibilita la creación de programas de propósito mucho más general. Por ejemplo, se pueden crear procedimientos que operen sobre una tabla cuyo nombre no se conoce hasta el momento de la ejecución, ya que se trata de un parámetro de entrada de dicho procedimiento.

Además, con el paquete DBMS_SQL se pueden ejecutar cualquier tipo de sentencias, tanto DML (Data Manipulation Language – Lenguaje de manipulación de datos), como DDL (Data Definition Language – Lenguaje de definición de datos). Por lo tanto, también permite directamente desde PL/SQL crear, modificar o borrar: tablas, índices, o cualquier objeto DDL (incluso procedimientos).

En el siguiente ejemplo os muestro un procedimiento almacenado en el que, dependiendo de los parámetros de entrada, se actualiza, bien el sueldo (campo salario), bien el departamento (campo dpto), de un determinado empleado.

CREATE OR REPLACE PROCEDURE upd_tabla_empleados
(   p_campo  IN VARCHAR2
  , p_emp_id IN VARCHAR2
  , p_valor  IN VARCHAR2 ) IS

  l_cursor        INTEGER;
  l_sql           VARCHAR2(500); -- Sentencia a ejecutar
  l_updated_rows  INTEGER;       -- Número de rows actualizadas
BEGIN
  l_sql := 'UPDATE empleados SET ' || p_campo || ' = ' ||
           p_valor || ' WHERE empleado_id = ' || p_emp_id;

  l_cursor := dbms_sql.open_cursor;
  dbms_sql.parse (l_cursor, l_sql, dbms_sql.native);
  l_update_rows := dbms_sql.execute (l_cursor);
  dbms_sql.close_cursor (l_cursor);
END;

Por lo tanto si ejecutamos el comando:
SQL> EXEC upd_tabla_empleados ('dpto', '1234', 'Compras');

Estaremos asignando el empleado con ID 1234 al departamento de compras.
Y si ejecutamos:

SQL> EXEC upd_tabla_empleados ('salario', '1122', '3000');

Estaremos asignando al empleado con ID 1122 un salario de 3.000 pesos.