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.

Búsquedas fuzzy, índices context y gestión documental con Oracle. Parte I.

(Continua en parte II).

En muchas ocasiones he podido ver como el uso de las funcionalidades de gestión documental de Oracle era un misterio para los directores de proyecto, que solían confiar para estas tareas en otras herramientas documentales, analistas y programadores.

Éstos últimos, tenían serios problemas en realizar búsquedas por contenido sobre campos almacenados como CLOB, ya que en la mayoría de casos utilizaban la cláusula LIKE con comodines a ambos lados, lo cual impedía el uso por parte del servidor de cualquier índice asociado a esa columna. Otras veces la solución implementada era aun peor (lowers, uppers, substr, instr… ¡sobre un campo que puede llegar a tener hasta 4Gb de capacidad!). Terrible.

Oracle dispone de una potencia de gestión documental principalmente basada en el uso de índices domain. Se trata de índices por palabras (también conocidos como índices CONTEXT) que permiten a Oracle, en una columna de CLOBs o BFILEs, tener una relación del contenido de estos objetos.

El siguiente ejemplo es un pequeño ejercicio basado en el uso de las funcionalidades documentales del servidor Oracle9i.

Pasos:

       1.- Creación de una tabla con columna BFILE
       2.- Creación de un DIRECTORY oracle para vincular los documentos Word.
       3.- Concesión de privilegios sobre el DIRECTORY al usuario CTXSYS, propietario de Oracle Context.
       4.- Inserción de las filas en la tabla DOCUMENTOS.
       5.- Indexación de la columna BFILE.
       6.- Ejemplos de consultas.


SQL> — Inicialmente creo una tabla DOCUMENTOS donde vincularé los documentos en formato word
SQL> — para su posterior indexación

SQL> create table documentos(
  2        id number constraint pk_documentos primary key,
  3        tipo char(4),
  4        titulo varchar2(60) not null,
  5        documento bfile);

Tabla creada.

SQL> — Es preciso crear un objeto DIRECTORY, vinculado al directorio de sistema operativo
SQL> — donde se encuentran los documentos word.

SQL> create or replace directory documentos_word as ‘c:documentos’;

Directorio creado.

SQL> grant read on directory documentos_word to CTXSYS;

Concesión terminada correctamente.

SQL> host dir c:documentos
El volumen de la unidad C no tiene etiqueta.
El número de serie del volumen es: 2416-B169

Directorio de c:documentos

09/05/2005  09:31       <DIR>          .
09/05/2005  09:31       <DIR>          ..
25/04/2005  13:21              271.360 EntornosNoProd_Permisos extendidos.doc
25/04/2005  13:21               31.232 normas_dba.doc
25/04/2005  13:21              379.904 creacion_entorno_bbdd.doc
25/04/2005  13:21              252.928 CREACION_INSTANCIA_DE_ORACLE_EN_UNIX_v2_4.doc
               4 archivos        935.424 bytes
               2 dirs   4.859.199.488 bytes libres

SQL> — Inserción de los cuatro documentos sobre la tabla
SQL> — y creación del índice CONTEXT (la cláusula es INDEXTYPE IS ctxsys.context)
SQL> ———————————————————————————–
SQL> insert into documentos
  2  values (1,’WORD’,’Entornos no producción. Permisos extendidos.’,
  3          BFILENAME(‘DOCUMENTOS_WORD’,’EntornosNoProd_Permisos extendidos.doc’));

1 fila creada.

SQL> insert into documentos
  2  values (2,’WORD’,’Normativa de DBA.’,
  3          BFILENAME(‘DOCUMENTOS_WORD’,’normas_dba.doc’));

1 fila creada.

SQL> insert into documentos
  2  values (3,’WORD’,’Creación de un entorno de BBDD.’,
  3          BFILENAME(‘DOCUMENTOS_WORD’,’creacion_entorno_bbdd.doc’));

1 fila creada.

SQL> insert into documentos
  2  values (4,’WORD’,’Creación de instancia Oracle en UNIX.’,
  3          BFILENAME(‘DOCUMENTOS_WORD’,’CREACION_INSTANCIA_DE_ORACLE_EN_UNIX_v2_4.doc’));

1 fila creada.

SQL> CREATE INDEX idx_documentos_doc ON documentos(DOCUMENTO) INDEXTYPE IS ctxsys.context;

Índice creado.

— Las búsquedas CONTEXT sobre índices domain se realizan con la cláusula CONTAINS
— más documentación al respecto en   http://download-west.oracle.com/docs/cd/B10501_01/text.920/a96518/cqoper.htm )
————————————————————

— documentos con la palabra ‘tablespace’ cerca de ‘storage’
SQL> select titulo
  2  from documentos
  3  where CONTAINS(documento, ‘tablespace near storage’) &gt 0;

TITULO
————————————————————
Creación de instancia Oracle en UNIX.
Normativa de DBA.

— documentos con una palabra parecida a ‘locally’ en un 70%
SQL> select titulo
  2  from documentos
  3  where CONTAINS(documento, ‘fuzzy(locally, 70, 6, weight)’, 1) &gt 0;

TITULO
————————————————————
Creación de un entorno de BBDD.

— documentos con una palabra parecida a ‘locally’ en un 60%
— nota: la palabra LOCAL aparece en estos dos nuevos documentos
SQL> select titulo
  2  from documentos
  3  where CONTAINS(documento, ‘fuzzy(locally, 60, 6, weight)’, 1) &gt 0;

TITULO
————————————————————
Creación de instancia Oracle en UNIX.
Creación de un entorno de BBDD.
Entornos no producción. Permisos extendidos.

— documentos que hablen sobre ‘listener’
SQL> select titulo
  2  from documentos
  3  where CONTAINS(documento, ‘about(listener)’) &gt 0;

TITULO
————————————————————
Creación de instancia Oracle en UNIX.
Normativa de DBA.

— relevancia de documentos que hablan de ‘listener’
— nota: en ‘Normativa de DBA’, efectivamente, se habla bastante menos sobre el listener.
SQL> select CONTAINS(documento, ‘about(listener)’)||’%’ relevancia, titulo
  2  from documentos
  3  where CONTAINS(documento, ‘about(listener)’) &gt 0;

RELEVANCIA TITULO
———- ————————————————————
78%        Creación de instancia Oracle en UNIX.
20%        Normativa de DBA.


Más información en http://download-west.oracle.com/docs/cd/B10501_01/text.920/a96518/csql.htm#21732

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…