PL/SQL – Página 2 – Optimización SQL en Oracle

«La query que tumba la máquina».

Hace unos años publiqué un artículo llamado «PL/SQL y ejecuciones en host» en el que describía el paso a paso para poder, desde PL/SQL, ejecutar código en el sistema operativo.

Oracle no permite que los procedimientos y funciones puedan acceder al host, pero sí permite llamadas a funciones externas implementadas con C o PASCAL, y redireccionadas como librerías mediante un objeto library.

Mi intención inicial fue la de crear un procedimiento PL/SQL que realizara un backup en caliente del servidor, realizase un export, import, o cualquier otra invocación a un ejecutable residente en el sistema operativo.

Hoy he visto una configuración similar en una base de datos en un entorno de producción, que realizan la misma implementación pero mediante una función.

create or replace
FUNCTION sysrun (syscomm IN VARCHAR2)
  RETURN BINARY_INTEGER
  AS LANGUAGE C
  NAME «sysrun»
  LIBRARY shell_lib
  PARAMETERS(syscomm string);

De este modo, la función retorna un 0 si la llamada al SHELL_LIB se ha ejecutado correctamente, o un número si ha habido un error.

Hice esta prueba, y pude constatar un mito: La consulta que tumba el servidor existe!!.

SQL> select sysrun('sudo reboot') from dual; Broadcast message from root (Thu Jan 27 13:16:34 2011): The system is going down for reboot NOW!

SYSTEM.SYSRUN('SUDOREBOOT') --------------------------- 0

Encriptación de datos desde aplicación.

En todos los ejemplos que he encontrado sobre encriptación y desencriptación de datos en Oracle, siempre se usan procedimientos PL/SQL para establecer la seguridad en la base de datos. No he encontrado un sólo ejemplo que permita hacer un insert «encriptado» y una consulta «desencriptada».

Imaginando el siguiente escenario: Cada usuario tiene una «palabra secreta» para desencriptar su propia información. En la base de datos todo se registra encriptado.

Para ello, Oracle ofrece dos paquetes:

– DBMS_OBFUSCATION_TOOLKIT. A partir de Oracle8i, que soporta encriptación DES y triple DES (Data Encription Standard), y con ciertas limitaciones (por ejemplo, los datos a encriptar han de ser un múltiplo de 8 bytes).

– DBMS_CRYPTO. A partir de Oracle10g. Soporta más formas de encriptación, como la AES (Advanced Encription Standard), que sustituye el anterior DES y no hay limitación con el número de carácteres.

Para mas información, la documentación de Oracle ofrece esta comparativa de funcionalidades.

Uso de DBMS_OBFUSCATION_TOOLKIT

El siguiente ejemplo muestra la encriptación de la palabra «SECRETO» (8 bytes) y genera un error al intentar encriptar «SECRETITOS!» (11 bytes)

SQL> select DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>‘SECRETO!’,key_string=>’clavedesencript’) from dual;

DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(INPUT_STRING=>’SECRETO!’,KEY_STRING=>’CLAVED
——————————————————————————–
lr??

SQL> select DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>‘SECRETITOS!’,key_string=>’clavedesencript’) from dual;
select DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>’SECRETITOS!’,key_string=>’clavedesencript’) from dual
*
ERROR at line 1:
ORA-28232: invalid input length for obfuscation toolkit
ORA-06512: at «SYS.DBMS_OBFUSCATION_TOOLKIT_FFI», line 21
ORA-06512: at «SYS.DBMS_OBFUSCATION_TOOLKIT», line 126

El error ORA-28232 corresponde a la longitud inadecuada de la cadena a encriptar. ‘SECRETITOS!» tiene 11 carácteres y el paquete está limitado a múltiplos de 8 bytes. Por ejemplo, el número de una tarjeta de crédito.

SQL> select DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>’1234567812345678′,
2 key_string=>’clavedesencript’)
3 from dual;

DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(INPUT_STRING=>’1234567812345678′,KEY_STRING=
——————————————————————————–
}??X??

De modo que la desencriptación funciona de igual modo, usando la función DESDecrypt

SQL> select DBMS_OBFUSCATION_TOOLKIT.DESDecrypt(
2 input_string=>DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>’SECRETO!’,key_string=>’CLAVE_BUENA’),
3 key_string=>’CLAVE_BUENA’) from dual;

DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(INPUT_STRING=>DBMS_OBFUSCATION_TOOLKIT.DESEN
——————————————————————————–
SECRETO!

SQL> select DBMS_OBFUSCATION_TOOLKIT.DESDecrypt(
2 input_string=>DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>’1111222233334444′,key_string=>’CLAVE_BUENA’),
3 key_string=>’CLAVE_BUENA’) from dual;

DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(INPUT_STRING=>DBMS_OBFUSCATION_TOOLKIT.DESEN
——————————————————————————–
1111222233334444

y si se utiliza una clave distinta, la información no se desencriptará adecuadamente.

SQL> select DBMS_OBFUSCATION_TOOLKIT.DESDecrypt(
2 input_string=>DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>’SECRETO!’,key_string=>’CLAVE_BUENA’),
3 key_string=>’CLAVE_ERRONEA’) from dual;

DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(INPUT_STRING=>DBMS_OBFUSCATION_TOOLKIT.DESEN
——————————————————————————–
???! k

…ó producirá un error.

SQL> select DBMS_OBFUSCATION_TOOLKIT.DESDecrypt(
2 input_string=>DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>’1111222233334444′,key_string=>’CLAVE_BUENA’),
3 key_string=>’CLAVE_MALA’) from dual;
ERROR:
ORA-29275: partial multibyte character

no rows selected

Uso de DBMS_CRYPTO

El siguiente ejemplo muestra la encriptación de la palabra «SECRETITOS!» (11 bytes) usando una suite de encriptación que ya viene implementada. En concreto es la DES_CBC_PCKS5, que contiene encriptación DES, encadenamiento de cifrado de bloques y modificadores de relleno PCKS5.

Es preciso, para invocar correctamente a este paquete, realizar una conversión a RAW de las cadenas a encriptar. He utilizado para ello el paquete UTL_RAW y la función UTL_I18N.STRING_TO_RAW.

SQL> select DBMS_CRYPTO.ENCRYPT(src => UTL_I18N.STRING_TO_RAW (‘SECRETITOS!’, ‘AL32UTF8’),
2 typ => 4353,
3 key => UTL_I18N.STRING_TO_RAW (‘clavedesencript’, ‘AL32UTF8’)
4 )
5 from dual;

DBMS_CRYPTO.ENCRYPT(SRC=>UTL_I18N.STRING_TO_RAW(‘SECRETITOS!’,’AL32UTF8′),TYP=>4
——————————————————————————–
1BA7F933C2CAD0C7F4FDA685775BE0E7

Y la desencriptación de la información, con la función DECRYPT.

SQL> select UTL_RAW.cast_to_varchar2(
2 DBMS_CRYPTO.DECRYPT(
3 DBMS_CRYPTO.ENCRYPT(src => UTL_I18N.STRING_TO_RAW (‘SECRETITOS!’, ‘AL32UTF8’),
4 typ => 4353,
5 key => UTL_I18N.STRING_TO_RAW (‘clavedesencript’, ‘AL32UTF8’)
6 ),
7 typ => 4353,
8 key => UTL_I18N.STRING_TO_RAW (‘clavedesdecript’, ‘AL32UTF8’)
9 )
10 )
11 from dual;

UTL_RAW.CAST_TO_VARCHAR2(DBMS_CRYPTO.DECRYPT(DBMS_CRYPTO.ENCRYPT(SRC=>UTL_I18N.S
——————————————————————————–
SECRETITOS!

Para más información sobre las múltiples formas de encriptación y uso de claves, lo mejor es consultar la documentación del paquete DBMS_CRYPTO.

Generación de XML mediante query.

En algunos proyectos en los que he participado, el equipo de front-end, que desarrollaba la interfaz web sobre plataforma .NET, tenía serios problemas para recuperar un conjunto de filas con un objeto tipo array incrustado.

La consulta debía devolver un vector de registros, algunos de ellos formados por una lista de valores. En aquel entonces, el driver de .NET no podía gestionar objetos UDT (tipos de datos definidos por el usuario) y hubo que comprar un componente aparte que tampoco dio buen resultado,pues no soportaba consultas a NESTED TABLES.

Una de las soluciones propuestas fue devolver el resultado (se trataba de fichas de clientes) en formato XML. La idea era buena, pero había alguno que pretendía «decodificar» columnas y filas para elaborar el resultado… ¡escribiendo un procedure con substrs!

No hace falta. La funcionalidad de crear UDT tiene una importancia relevante en las conversiones a XML. Definiendo los tipos como OBJECT y TABLE OF OBJECT podemos crear una estructura de datos facilmente exportable a XML tal y como muestra el ejemplo.

Las tablas tomadas como referencia corresponden a EMP y DEPT del esquema de ejemplo SCOTT.

SQL> create or replace type r_empleado is object(
  2     NUM_EMP          NUMBER(4),
  3     NOMBRE_EMP       VARCHAR2(10),
  4     OFICIO_EMP       VARCHAR2(9),
  5     JEFE_EMP         NUMBER(4),
  6     FCONTRATO_EMP    DATE,
  7     SALARIO_EMP      NUMBER(7,2),
  8     COMISION_EMP     NUMBER(7,2),
  9     DEPARTAMENTO_EMP NUMBER(2));
10  /

Tipo creado.

SQL>
SQL> create or replace type t_empleado is table of r_empleado;
2 /

Tipo creado.

SQL>
SQL> create or replace type r_dept is object(
  2     NUM_DEPT     NUMBER(2),
  3     NOMBRE_DEPT  VARCHAR2(14),
  4     CIUDAD_DEPT  VARCHAR2(13),
  5     empleados    t_empleado);
  6  /

Tipo creado.

SQL>
SQL> create or replace type t_dept is table of r_dept;
2 /

Tipo creado.

SQL>
SQL>
SQL> select sys_xmlgen(
  2               r_dept(dept.deptno, dept.dname, dept.loc,
  3                 cast(multiset(select emp.*
  4                                 from emp
  5                                where emp.deptno = dept.deptno) as t_empleado)
  6                    ),xmlformat.createFormat(‘DEPARTAMENTO’)).getClobVal() as XML
  7  from dept
  8  where deptno=10;

XML
——————————————————————————–
<?xml version=»1.0″?>
<DEPARTAMENTO>
<NUM_DEPT>10</NUM_DEPT>
<NOMBRE_DEPT>ACCOUNTING</NOMBRE_DEPT>
<CIUDAD_DEPT>NEW YORK</CIUDAD_DEPT>
<EMPLEADOS>
  <R_EMPLEADO>
   <NUM_EMP>7782</NUM_EMP>
   <NOMBRE_EMP>CLARK</NOMBRE_EMP>
   <OFICIO_EMP>MANAGER</OFICIO_EMP>
   <JEFE_EMP>7839</JEFE_EMP>
   <FCONTRATO_EMP>09/06/81</FCONTRATO_EMP>
   <SALARIO_EMP>2450</SALARIO_EMP>
   <DEPARTAMENTO_EMP>10</DEPARTAMENTO_EMP>
  </R_EMPLEADO>
  <R_EMPLEADO>
   <NUM_EMP>7839</NUM_EMP>
   <NOMBRE_EMP>KING</NOMBRE_EMP>
   <OFICIO_EMP>PRESIDENT</OFICIO_EMP>
   <FCONTRATO_EMP>17/11/81</FCONTRATO_EMP>
   <SALARIO_EMP>5000</SALARIO_EMP>
   <DEPARTAMENTO_EMP>10</DEPARTAMENTO_EMP>
  </R_EMPLEADO>
  <R_EMPLEADO>
   <NUM_EMP>7934</NUM_EMP>
   <NOMBRE_EMP>MILLER</NOMBRE_EMP>
   <OFICIO_EMP>CLERK</OFICIO_EMP>
   <JEFE_EMP>7782</JEFE_EMP>
   <FCONTRATO_EMP>23/01/82</FCONTRATO_EMP>
   <SALARIO_EMP>1300</SALARIO_EMP>
   <DEPARTAMENTO_EMP>10</DEPARTAMENTO_EMP>
  </R_EMPLEADO>
</EMPLEADOS>
</DEPARTAMENTO>

PL/SQL y ejecuciones en host.

Por un motivo de seguridad, desde PL/SQL no es posible ejecutar llamadas al host. En principio, ningún módulo programado en PL/SQL debería ejecutar nada en sistema operativo. No obstante, seguro que cualquiera es capaz de recordar alguna situación concreta en la que poder invocar un «copy» o quizás ejecutar algún fichero .exe hubiera sido de gran ayuda.

Existe una forma de dar un rodeo a este escenario.

Oracle permite la llamada a procedimientos externos mediante un servicio extproc que debe configurarse en el fichero de alias de servicios «tnsnames.ora» para el cliente, y en el de configuración del listener «listener.ora» en el lado del servidor.

Los ficheros tendrán este aspecto.

Nota: puede haber cambios por la versión del servidor. El ejemplo se implementó sobre Oracle 8.1.7.

Fichero LISTENER.ORA
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = extproc_agent)
      (ORACLE_HOME = E:oracleora92)
      (PROGRAM = extproc)
      (ENVS = «EXTPROC_DLLS=ANY»)
    )
  )

Fichero TNSNAMES.ORA
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(Key = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = extproc_agent)
    )
  )

Con esta parametrización del listener, y la definición de un servicio para llamadas a ‘extproc’, Oracle es capaz de invocar desde PL/SQL librerías externas programadas en C o PASCAL.

Pasos:

      1.- Descarga de un compilador de C++. Este ejemplo utiliza C++Compiler de Borland.
      2.- Creación de un programa en C que ejecuta en S.O. un comando dado.
      3.- Compilación del programa e implementación de las librerías.
      4.- Desde Oracle, creación del objeto LIBRARY (biblioteca).
      5.- Creación de un procedimiento PL/SQL que invoque a una función de esa biblioteca.

Creación del programa shell.c
—————————–
   #include <windows.h>
   #include <stdio.h>
   #include <stdlib.h>

   void __declspec(dllexport) sh(char *);
   void sh(char *cmd)
   {
       system(cmd);
   }

Compilación del código C y creación de las librerías  .lib y .dll
—————————————————————–
  bcc32 -WD shell.c
  implib shell.lib shell.dll

Creación del objeto LIBRARY, en la consola de SQL.
————————————————–
SQL> create library shell_lib is ‘C:BorlandBCC55shell.dll’;
2 /

Biblioteca creada.

De modo que ya es posible crear un procedimiento PL/SQL que referencie externamente a esa librería. Ésta se encargará de ejecutar en host el parámetro que le incluyamos. Por ejemplo, un «copy».
————————————————————————————————–
SQL> create or replace procedure shell (
  2         cmd IN varchar2)
  3  as external
  4       library shell_lib
  5       name «_sh»
  6       language C
  7       parameters (cmd string);
  8  /

Procedimiento creado.

SQL>

El testeo podrá hacerse con un simple ‘exec’ desde SQL*Plus, ya que equivale a «BEGIN shell(‘parametro’); END;»
—————————————————————————————————————
SQL> exec shell(‘copy c:test*.* c:destino’);

Procedimiento PL/SQL terminado correctamente.

Funciona.

Uso de cursores y bulk insert.

Uno de los muchos mitos en oracle es que el uso de cursores perjudica seriamente el rendimiento. Incluso he visto algunos proyectos en los que se había prohibido el uso de esta técnica.

El impacto sobre el rendimiento es cierto en parte, ya que el servidor trata individualmente cada registro y, por tanto, esto debería implementarse únicamente cuando fuera estrictamente necesario (sql dinámico, iteraciones dentro del fetch, etc.) utilizando, siempre que sea posible, SQL estándar.

No obstante, hay situaciones en las que no es posible el uso de SQL convencional. Por ejemplo, cuando en una inserción es preciso controlar los errores e insertarlos en otra tabla. Ante este caso, los procedimientos ETL filtran previamente las filas, las mueven a otra tabla, y cuando las filas ya están totalmente «limpias» de errores, entonces realizan
INSERT INTO tabla SELECT * …

Oracle, a partir de la versión 9i, dispone de la funcionalidad de BULK sql, es decir, tratamiento de
conjuntos de filas «a montón». Realizar bulk inserts puede resultar muy práctico en el caso anterior, ya que las filas en el cursor no se ejecutan individualmente y no es preciso el filtrado previo de las filas.

Así pues, ante la necesidad de insertar en una tabla un volúmen de filas, omitir los errores e insertar las filas erróneas en una tabla, el siguiente código PL/SQL puede ser de gran utilidad, ya que su coste de ejecución resulta idéntico al del INSERT.

— Código PL/SQL para insertar en una tabla, manejando errores en filas, con BULK INSERT
————————————————————————————–
DECLARE
    type clientes_array is table of BK_CLIENTE_BDM_03%rowtype index by binary_integer;
    registros clientes_array;    errores NUMBER;
    dml_errores EXCEPTION;
    contador_errores number := 0;
    PRAGMA exception_init(dml_errores, -24381);
    cursor c is select * from BK_CLIENTE_BDM_03;
BEGIN
    open c;
    loop
      fetch c BULK COLLECT INTO registros LIMIT 1000;      begin
       FORALL i IN 1 .. registros.count SAVE EXCEPTIONS
         insert into TRASPASO_BD_CLIENTE values registros(i);      EXCEPTION
        WHEN dml_errores THEN
          errores := SQL%BULK_EXCEPTIONS.COUNT;          contador_errores := contador_errores + errores;
          FOR i IN 1..errores LOOP
            dbms_output.put_line (‘Se encontro el error ‘||;SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
                    ‘:’||SQL%BULK_EXCEPTIONS(i).ERROR_CODE);          end loop;
     end;
     exit when c%notfound;
    END LOOP;
    close c;
    dbms_output.put_line( contador_errores );
end;

Su coste de ejecución es idéntico al de ejecutar:

insert into TABLA_DESTINO select * from TABLA_ORIGEN;

Comparativa de costes de ejecución.
******************************

insert into TRASPASO_BD_CLIENTE select * from BK_CLIENTE_BDM_03

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.00       0.00          0          1          0           0
Execute      1      4.91     102.61      15713      31424      88189      600000
Fetch        0      0.00       0.00          0          0          0           0
——- ——  ——– ———- ———- ———- ———-  ———-
total        2      4.91     102.62      15713      31425      88189      600000

********************************************************************************

DECLARE
        type clientes_array is table of BK_CLIENTE_BDM_03%rowtype index by binary_integer;
        registros   clientes_array;
        errores NUMBER;
        dml_errores EXCEPTION;
        contador_errores number := 0;
        PRAGMA exception_init(dml_errores, -24381);
        cursor c is select * from BK_CLIENTE_BDM_03;
BEGIN
       open c;
       loop
           fetch c BULK COLLECT INTO registros LIMIT 1000;
           begin
              FORALL i IN 1 .. registros.count SAVE EXCEPTIONS
                 insert into TRASPASO_BD_CLIENTE values registros(i);
           EXCEPTION
               WHEN dml_errores THEN
                    errores := SQL%BULK_EXCEPTIONS.COUNT;
                    contador_errores := contador_errores + errores;
                    FOR i IN 1..errores LOOP
                        dbms_output.put_line
                        (‘Se encontrs el error ‘
                          SQL%BULK_EXCEPTIONS(i).ERROR_INDEX
                       ‘: ‘
                          SQL%BULK_EXCEPTIONS(i).ERROR_CODE );
                    end loop;
          end;
          exit when c%notfound;
        END LOOP;
        close c;
        dbms_output.put_line( contador_errores
end;

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.00       0.01          0          0          0           0
Execute      1      2.40       2.46          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
——- ——  ——– ———- ———- ———- ———-  ———-
total        2      2.40       2.47          0          0          0           1

SELECT * FROM BK_CLIENTE_BDM_03

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      601     20.90      65.99      15712      16373          0      600000
——- ——  ——– ———- ———- ———- ———-  ———-
total      603     20.90      65.99      15712      16373          0      600000

INSERT INTO TRASPASO_BD_CLIENTE
VALUES
(:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 ,
  :B15 ,:B16 ,:B17 ,:B18 ,:B19 ,:B20 ,:B21 ,:B22 ,:B23 ,:B24 ,:B25 ,:B26 ,
  :B27 ,:B28 ,:B29 ,:B30 ,:B31 ,:B32 ,:B33 ,:B34 ,:B35 ,:B36 ,:B37 ,:B38 ,
  :B39 ,:B40 ,:B41 ,:B42 ,:B43 ,:B44 ,:B45 ,:B46 ,:B47 )

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.00       0.00          0          0          0           0
Execute    600     17.40      26.39          0      15835      87647      600000
Fetch        0      0.00       0.00          0          0          0           0
——- ——  ——– ———- ———- ———- ———-  ———-
total      601     17.40      26.39          0      15835      87647      600000

********************************************************************************

RESUMEN:

Insert Select –> Execute 1 4.91 102.61 15713 31424 88189 600000

BULK INSERT   –>   Execute      1      2.40       2.46          0          0          0           1
+                   Fetch      601     20.90      65.99      15712      16373          0      600000
+                   Execute    600     17.40      26.39          0      15835      87647      600000

Tiempo insert select: 1’42» (mismos bloques físicos y en caché)
Tiempo bulk insert: 1’33» (mismos bloques físicos y en caché)
No hay diferencia…