Ejecuta comandos del sistema operativo desde SQL con dbms_os

El paquete dbms_os ejecuta un programa desde dentro de Invantive SQL y devuelve su código de salida, la salida estándar y el error estándar como columnas, junto con funciones para leer, escribir, listar y hacer hash de archivos.

Disponibilidad

El paquete está disponible a partir de la versión 27.0 en los productos que se ejecutan para un solo usuario en el propio contexto de ese usuario:

  • Herramienta de Consulta de Invantive
  • Centro de Datos de Invantive
  • Control de Invantive para Excel
  • Composición de Invantive para Word

Acceso al Sistema Operativo

El script de Invantive ya ofrecía local host para iniciar un programa:

host local "cmd.exe" "/c dotnet build" "c:\folder" "600000"

El código de salida y la salida se almacenan en las variables del script local:hostexitcode, local:hoststdout y local:hoststderr.

Para permitir que la lógica de PSQL ramifique en estos usando la función de tabla dbms_os.run, devuelve una fila con los resultados:

  • EXIT_CODE: código de salida, vacío cuando el comando se terminó después de exceder su duración máxima.
  • TIMED_OUT: indicador de si se produjo un tiempo de espera;
  • STDOUT y STDERR: salida y salida de error, truncados a 100 MB.
  • STDOUT_TRUNCATED y STDERR_TRUNCATED: indicadores de si la salida fue truncada.
  • DURATION_MS: duración en milisegundos.
  • CPU_MS: uso de CPU en milisegundos.
  • PROCESS_ID: ID del proceso del sistema operativo.

Para fallar en un error, pasa raise_on_error => true a run.

Tres diferencias adicionales con el script de Invantive son:

  • No hay participación del shell: el programa se inicia directamente, por lo que |, >, && y *.txt no se interpretan. Una línea de comandos que los necesite se ejecuta a través de cmd.exe /c explícitamente, exactamente como antes.
  • Argumentos entre comillas donde sea necesario: además de arguments, que se añade a la línea de comandos tal como está, run acepta argument1 hasta argument10. Cada uno de estos llega al programa como un argumento único, incluso cuando contiene un espacio o una barra invertida final. Para ensamblar una línea de comandos en tiempo de ejecución, la función dbms_os.quote_argument permite citar.
  • El tiempo de espera finaliza el árbol de procesos: un comando que exceda su duración máxima se termina junto con todo lo que inició.

Además de run, el paquete incluye read_file, read_file_text, write_file, write_file_text, file_exists, file_size, file_modified_utc, file_hash, copy_file, move_file, delete_file, create_directory, directory_exists, list_files, list_directories, which, get_env, path_combine, path_full, open_file y open_url.

La mayoría de estos procedimientos y funciones de paquete tienen un equivalente en el proveedor de servicios os.

Versión de Consulta del Programa

La versión del cliente Git en la estación de trabajo, como datos:

select rce.exit_code
,      rce.stdout
,      rce.duration_ms
from   table(dbms_os.run('git.exe', null, 10000, null, false, '--version')) rce

Los parámetros después del programa son el directorio de trabajo, la duración máxima en milisegundos, el texto para la entrada estándar, si debe elevarse en un código de salida distinto de cero y la línea de comandos. Ver imagen abajo:

Un programa que no se encuentra en la ruta de búsqueda se nombra con su ruta completa y dbms_os.which('git.exe') informa cuál se encontraría.

Archivo PDF/A Auditable de Facturas de Salida

Una práctica contable conserva las facturas de ventas enviadas durante el período de retención de siete años. Las facturas salen del paquete contable como PDF en una carpeta de salida. El archivo debe almacenarlas como PDF/A, por año y mes, con evidencia de que el documento archivado es el enviado. La evidencia es un hash por archivo, registrado en el momento del archivo, para que una comparación posterior muestre si algo cambió.

La rutina a continuación se ejecuta nocturnamente en el Centro de Datos de Invantive. Los nombres de las carpetas provienen de variables del script de Invantive.

definir local OUTBOX_PATH "c:\acme\outbox"

definir local ARCHIVE_PATH "c:\acme\archive"

definir local GHOSTSCRIPT_PATH "c:\Program Files\gs\gs10.20.30\bin\gswin64c.exe"

crear o reemplazar tabla archive_log@InMemoryStorage
( file_name        varchar2
, archived_path    varchar2
, bytes            int64
, sha256           varchar2
, exit_code        int32
, error_message    varchar2
)

declarar
  l_archive_month varchar2;
  l_target_path   varchar2;
  l_exit_code     int32;
  l_stderr        varchar2;
  l_bytes         int64;
  l_sha256        varchar2;
begin
  --
  -- Una carpeta por año y mes, para que un período de retención pueda liberarse como un todo.
  --
  l_archive_month := dbms_os.path_combine
                     ( '${ARCHIVE_PATH}'
                     , to_char(sysdateutc, 'YYYY')
                     , to_char(sysdateutc, 'MM')
                     );
  dbms_os.create_directory(l_archive_month);
  --
  para r en
  ( seleccionar fle.file_path
    ,      fle.file_name
    de     table(dbms_os.list_files('${OUTBOX_PATH}', '*.pdf')) fle
  )
  bucle
    l_target_path := dbms_os.path_combine(l_archive_month, r.file_name);
    --
    -- Ghostscript convierte a PDF/A-2b. Cada argumento se entrega por separado, por lo que
    -- un nombre de práctica con un espacio en la ruta no puede dividir la línea de comandos.
    --
    seleccionar rce.exit_code
    ,      rce.stderr
    en   l_exit_code
    ,      l_stderr
    de   table
           ( dbms_os.run
             ( '${GHOSTSCRIPT_PATH}'
             , null
             , 300000
             , null
             , false
             , '-dPDFA=2 -dPDFACompatibilityPolicy=1 -dBATCH -dNOPAUSE -sColorConversionStrategy=UseDeviceIndependentColor -sDEVICE=pdfwrite'
             , '-sOutputFile=' || l_target_path
             , r.file_path
             )
           ) rce
    ;
    --
    -- El hash se toma del archivo archivado en lugar del original.
    --
    si l_exit_code = 0
    entonces
      l_bytes  := dbms_os.file_size(l_target_path);
      l_sha256 := dbms_os.file_hash(l_target_path);
    sino
      l_bytes  := null;
      l_sha256 := null;
    fin si;
    --
    insertar en archive_log@InMemoryStorage
    ( file_name
    , archived_path
    , bytes
    , sha256
    , exit_code
    , error_message
    )
    valores
    ( r.file_name
    , caso cuando l_exit_code = 0 entonces l_target_path sino null fin caso
    , l_bytes
    , l_sha256
    , l_exit_code
    , l_stderr
    );
    --
    -- Una factura convertida abandona la carpeta de salida. Una fallida permanece donde está, con su razón en
    -- el registro para que la próxima ejecución la recoja.
    --
    si l_exit_code = 0
    entonces
      dbms_os.delete_file(r.file_path);
    fin si;
  fin bucle;
fin;

--
-- Resumen.
--
seleccionar ale.file_name
,      ale.bytes
,      ale.sha256
,      ale.error_message
from   archive_log@InMemoryStorage ale
where  ale.exit_code != 0
order
por     ale.file_name

Véase también: dbms_os

Véase también: declaración host local

Véase también: proveedor del sistema operativo