Exécuter des commandes du système d'exploitation depuis SQL avec dbms_os

Le package dbms_os exécute un programme depuis Invantive SQL et renvoie son code de sortie, sa sortie standard et son erreur standard en tant que colonnes, ainsi que des fonctions pour lire, écrire, lister et hacher des fichiers.

Disponibilité

Le package est disponible à partir de la version 27.0 sur les produits qui fonctionnent pour un seul utilisateur dans le contexte de cet utilisateur :

  • Invantive Query Tool
  • Invantive Data Hub
  • Invantive Control for Excel
  • Invantive Composition for Word

Accès au Système d’Exploitation

Invantive Script offrait déjà local host pour démarrer un programme :

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

Le code de sortie et la sortie atterrissent dans les variables de script local:hostexitcode, local:hoststdout et local:hoststderr.

Pour permettre à la logique PSQL de se brancher dessus en utilisant la fonction de table dbms_os.run, retournez une ligne avec les résultats:

  • EXIT_CODE: code de sortie, vide lorsque la commande a été arrêtée après avoir dépassé sa durée maximale.
  • TIMED_OUT: indicateur si un délai d’attente est survenu;
  • STDOUT et STDERR: sortie et sortie d’erreur, tronquées à 100 MB.
  • STDOUT_TRUNCATED et STDERR_TRUNCATED: indicateurs si la sortie a été tronquée.
  • DURATION_MS: durée en millisecondes.
  • CPU_MS: utilisation CPU en millisecondes.
  • PROCESS_ID: identifiant de processus du système d’exploitation.

Pour échouer en cas d’erreur, passez raise_on_error => true à run.

Trois autres différences avec Invantive Script sont :

  • Pas d’implication du shell : le programme est démarré directement, donc |, >, && et *.txt ne sont pas interprétés. Une ligne de commande qui en a besoin est exécutée à travers cmd.exe /c explicitement, exactement comme avant.
  • Arguments cités si nécessaire : outre arguments, qui est ajouté à la ligne de commande tel quel, run accepte argument1 jusqu’à argument10. Chacun de ceux-ci arrive au programme comme exactement un argument, même s’il contient un espace ou une barre oblique inversée finale. Pour une ligne de commande assemblée au moment de l’exécution, la fonction dbms_os.quote_argument permet de citer.
  • Le dépassement de délai met fin à l’arborescence des processus : une commande qui dépasse sa durée maximale est arrêtée avec tout ce qu’elle a commencé.

En plus de run, le package contient 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 et open_url.

La plupart de ces procédures et fonctions de package ont un équivalent dans le fournisseur de services os.

Version de la commande de Programme

La version du client Git sur la station de travail, comme données :

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

Les paramètres après le programme sont le répertoire de travail, la durée maximale en millisecondes, le texte pour l’entrée standard, s’il faut échouer en cas de code de sortie non nul, et la ligne de commande. Voir l’image ci-dessous :

Un programme qui n’est pas sur le chemin de recherche est nommé avec son chemin complet et dbms_os.which('git.exe') indique lequel serait trouvé.

Archive Auditable PDF/A des Factures Sortantes

Un cabinet comptable conserve les factures de vente qu’il a envoyées pour l’obligation de conservation de sept ans. Les factures quittent le package comptable sous forme de PDF dans un dossier de sortie. L’archive doit les contenir au format PDF/A, par année et mois, avec des preuves que le document archivé est bien celui qui a été envoyé. La preuve est un hachage par fichier, enregistré au moment de l’archivage, afin qu’une comparaison ultérieure montre si quelque chose a été déplacé.

La routine ci-dessous s’exécute chaque nuit dans Invantive Data Hub. Les noms de dossier proviennent des variables de script Invantive.

local define OUTBOX_PATH "c:\acme\outbox"

local define ARCHIVE_PATH "c:\acme\archive"

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

create or replace table archive_log@InMemoryStorage
( file_name        varchar2
, archived_path    varchar2
, bytes            int64
, sha256           varchar2
, exit_code        int32
, error_message    varchar2
)

declare
  l_archive_month varchar2;
  l_target_path   varchar2;
  l_exit_code     int32;
  l_stderr        varchar2;
  l_bytes         int64;
  l_sha256        varchar2;
begin
  --
  -- Un dossier par année et mois, de sorte qu'une période de conservation puisse être libérée dans son ensemble.
  --
  l_archive_month := dbms_os.path_combine
                     ( '${ARCHIVE_PATH}'
                     , to_char(sysdateutc, 'YYYY')
                     , to_char(sysdateutc, 'MM')
                     );
  dbms_os.create_directory(l_archive_month);
  --
  for r in
  ( select fle.file_path
    ,      fle.file_name
    from   table(dbms_os.list_files('${OUTBOX_PATH}', '*.pdf')) fle
  )
  loop
    l_target_path := dbms_os.path_combine(l_archive_month, r.file_name);
    --
    -- Ghostscript convertit en PDF/A-2b. Chaque argument est transmis individuellement, donc un nom de pratique contenant un espace dans le chemin ne peut pas diviser la ligne de commande.
    --
    select rce.exit_code
    ,      rce.stderr
    into   l_exit_code
    ,      l_stderr
    from   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
    ;
    --
    -- Le hachage est pris à partir du fichier archivé plutôt qu'à partir de l'original.
    --
    if l_exit_code = 0
    then
      l_bytes  := dbms_os.file_size(l_target_path);
      l_sha256 := dbms_os.file_hash(l_target_path);
    else
      l_bytes  := null;
      l_sha256 := null;
    end if;
    --
    insert into archive_log@InMemoryStorage
    ( file_name
    , archived_path
    , bytes
    , sha256
    , exit_code
    , error_message
    )
    values
    ( r.file_name
    , case when l_exit_code = 0 then l_target_path else null end
    , l_bytes
    , l_sha256
    , l_exit_code
    , l_stderr
    );
    --
    -- Une facture convertie quitte la boîte de sortie. Une échouée reste là où elle est, avec sa raison dans le journal pour la prochaine exécution.
    --
    if l_exit_code = 0
    then
      dbms_os.delete_file(r.file_path);
    end if;
  end loop;
end;

--
-- Vue d'ensemble.
--
select ale.file_name
,      ale.bytes
,      ale.sha256
,      ale.error_message
from   archive_log@InMemoryStorage ale
where  ale.exit_code != 0
order
by     ale.file_name

Voir Aussi : dbms_os

Voir Aussi : l’instruction local host

Voir Aussi : fournisseur du système d’exploitation