The dbms_os package runs a program from within Invantive SQL and returns its exit code, standard output and standard error as columns, alongside functions to read, write, list and hash files.
Availability
The package is available from release 27.0 on the products which run for a single user in that user’s own context:
- Invantive Query Tool
- Invantive Data Hub
- Invantive Control for Excel
- Invantive Composition for Word
Operating System Access
Invantive Script already offered local host for starting a program:
local host "cmd.exe" "/c dotnet build" "c:\folder" "600000"
The exit code and the output land in the script variables local:hostexitcode, local:hoststdout and local:hoststderr.
To allow PSQL logic to branch on these using the table function dbms_os.run return a row with the outcomes:
EXIT_CODE: exit code, empty when the command was ended after exceeding its maximum duration.TIMED_OUT: flag whether time out occurred;STDOUTandSTDERR: output and error output, truncated to 100 MB.STDOUT_TRUNCATEDandSTDERR_TRUNCATED: flags whether the output was truncated.DURATION_MS: duration in milliseconds.CPU_MS: CPU usage in milliseconds.PROCESS_ID: operating system process ID.
To fail on an error, pass raise_on_error => true to run.
Three further differences with Invantive Script are:
- No shell involvement: the program is started directly, so
|,>,&&and*.txtare not interpreted. A command line which needs them is run throughcmd.exe /cexplicitly, exactly as before. - Arguments quoted where needed: besides
arguments, which is appended to the command line as it stands,runacceptsargument1up toargument10. Each of those arrives at the program as exactly one argument even when it holds a space or a trailing backslash. For a command line assembled at run time, the functiondbms_os.quote_argumentallows quoting. - Time-out ends process tree: a command which outstays its maximum duration is ended together with everything it started.
Besides run, the package carries 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 and open_url.
Most of these package procedures and functions have a counterpart in the os service provider.
Query Version of Program
The version of the Git client on the workstation, as data:
select rce.exit_code
, rce.stdout
, rce.duration_ms
from table(dbms_os.run('git.exe', null, 10000, null, false, '--version')) rce
The parameters after the program are the working directory, the maximum duration in milliseconds, the text for the standard input, whether to raise on a non-zero exit code, and the command line. See picture below:
A program which is not on the search path is named with its full path and dbms_os.which('git.exe') reports which one would be found.
Auditable PDF/A Archive of Outgoing Invoices
An accountancy practice keeps the sales invoices it sent for the seven-year retention obligation. The invoices leave the accounting package as PDF into an outbox folder. The archive has to hold them as PDF/A, per year and month, with evidence that the archived document is the one that was sent. The evidence is a hash per file, recorded at the moment of archiving, so that a later comparison shows whether anything moved.
The routine below runs nightly in Invantive Data Hub. The folder names come from Invantive Script variables.
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
--
-- One folder per year and month, so that a retention period can be released as a whole.
--
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 converts to PDF/A-2b. Every argument is handed over on its own, so a
-- practice name holding a space in the path cannot split the command line.
--
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
;
--
-- The hash is taken from the archived file rather than from the 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
);
--
-- A converted invoice leaves the outbox. A failed one stays where it is, with its reason in
-- the log for the next run to pick it up.
--
if l_exit_code = 0
then
dbms_os.delete_file(r.file_path);
end if;
end loop;
end;
--
-- Overview.
--
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