Betriebssystembefehle mit dbms_os aus SQL ausführen

Das Paket dbms_os führt ein Programm aus Invantive SQL heraus aus und liefert dessen Exit-Code, Standardausgabe und Standardfehler als Spalten zurück, zusammen mit Funktionen zum Lesen, Schreiben, Auflisten und Hashen von Dateien.

Verfügbarkeit

Das Paket ist ab Version 27.0 in den Produkten verfügbar, die für einen einzelnen Benutzer im Kontext dieses Benutzers ausgeführt werden:

  • Invantive Query Tool
  • Invantive Data Hub
  • Invantive Control für Excel
  • Invantive Composition für Word

Betriebssystemzugriff

Invantive Script bot bereits local host zum Starten eines Programms an:

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

Der Exit-Code und die Ausgabe landen in den Skriptvariablen local:hostexitcode, local:hoststdout und local:hoststderr.

Um es PSQL-Logik zu ermöglichen, darauf mit der Tabellenfunktion dbms_os.run zu reagieren, wird eine Zeile mit den Ergebnissen zurückgegeben:

  • EXIT_CODE: Rückgabecode, leer, wenn der Befehl nach Überschreitung der maximalen Dauer beendet wurde.
  • TIMED_OUT: Flag, ob ein Timeout aufgetreten ist;
  • STDOUT und STDERR: Ausgabe und Fehlerausgabe, auf 100 MB gekürzt.
  • STDOUT_TRUNCATED und STDERR_TRUNCATED: Flags, ob die Ausgabe gekürzt wurde.
  • DURATION_MS: Dauer in Millisekunden.
  • CPU_MS: CPU-Nutzung in Millisekunden.
  • PROCESS_ID: Prozess-ID des Betriebssystems.

Um bei einem Fehler abzubrechen, übergeben Sie raise_on_error => true an run.

Drei weitere Unterschiede zu Invantive Script sind:

  • Keine Shell-Beteiligung: Das Programm wird direkt gestartet, sodass |, >, && und *.txt nicht interpretiert werden. Eine Befehlszeile, die diese benötigt, wird ausdrücklich wie zuvor durch cmd.exe /c ausgeführt.
  • Argumente werden bei Bedarf umschlossen: Neben arguments, die wie sie sind an die Befehlszeile angehängt werden, akzeptiert run argument1 bis argument10. Jedes davon kommt als genau ein Argument zum Programm, auch wenn es ein Leerzeichen oder einen abschließenden Backslash enthält. Für eine zur Laufzeit zusammengefügte Befehlszeile ermöglicht die Funktion dbms_os.quote_argument das Umhüllen.
  • Timeout beendet den Prozessbaum: Ein Befehl, der seine maximale Dauer überschreitet, wird zusammen mit allem beendet, was er gestartet hat.

Neben run enthält das Paket 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 und open_url.

Die meisten dieser Paketprozeduren und -funktionen haben ein Gegenstück im os Dienstanbieter.

Abfrageversion des Programms

Die Version des Git-Clients auf dem Arbeitsplatz, als Daten:

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

Die Parameter nach dem Programm sind das Arbeitsverzeichnis, die maximale Dauer in Millisekunden, der Text für die Standardeingabe, ob bei einem nicht-null Exit-Code abgebrochen werden soll, und die Befehlszeile. Siehe Bild unten:

Ein Programm, das sich nicht im Suchpfad befindet, wird mit seinem vollständigen Pfad benannt und dbms_os.which('git.exe') gibt an, welches gefunden wird.

Nachprüfbares PDF/A-Archiv ausgehender Rechnungen

Ein Buchhaltungsbüro bewahrt die Verkaufsrechnungen, die es für die siebenjährige Aufbewahrungspflicht verschickt hat, auf. Die Rechnungen verlassen das Buchhaltungspaket als PDF in einen Ausgabeverzeichnis. Das Archiv muss sie als PDF/A pro Jahr und Monat enthalten und belegt, dass das archivierte Dokument das verschickte ist. Der Beleg ist ein Hash pro Datei, der zum Zeitpunkt der Archivierung erfasst wird, sodass eine spätere Überprüfung zeigt, ob sich etwas verändert hat.

Die folgende Routine läuft nachts im Invantive Data Hub. Die Ordnernamen stammen aus Invantive Script-Variablen.

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
  --
  -- Ein Ordner pro Jahr und Monat, damit eine Aufbewahrungsfrist als Ganzes freigegeben werden kann.
  --
  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 konvertiert zu PDF/A-2b. Jedes Argument wird einzeln übergeben, sodass ein
    -- Praxisname im Pfad bei leerem Zeichen nicht die Befehlszeile teilt.
    --
    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
    ;
    --
    -- Der Hash wird aus der archivierten Datei und nicht aus dem Original entnommen.
    --
    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
    );
    --
    -- Eine konvertierte Rechnung verlässt das Ausgabeverzeichnis. Eine fehlgeschlagene bleibt dort, 
    -- mit ihrem Grund im Log, damit sie beim nächsten Lauf aufgenommen werden kann.
    --
    if l_exit_code = 0
    then
      dbms_os.delete_file(r.file_path);
    end if;
  end loop;
end;

--
-- Übersicht.
--
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

Siehe auch: dbms_os

Siehe auch: local host Anweisung

Siehe auch: Betriebssystemanbieter