Invantive UniversalSQL provides various statements to generate output besides query execution. For example, you can generate Excel workbook using Invantive PSQL statements. But it is also possible to collect and print output using dbms_output.
However, many platforms also provide native output mechanisms, such as print on SQL Server. Invantive UniversalSQL also collects and provides the native platform’s output. This topic will illustrate some examples.
Invantive UniversalSQL Output
Output can be generated from Invantive PSQL-procedural logic using the dbms_output package as described in Invantive SQL generic PSQL packages.
For example, the following block of code:
begin
dbms_output.put_line('hello world using Invantive UniversalSQL output');
end;
will print a message to the output window:
PostgreSQL Output
A (virtual) Invantive database can contain one or more PostgreSQL data containers, which each can print output.
For example, the following block of code:
begin
execute native 'do $$begin raise notice ''hello world using native PostgreSQL notice''; end$$;' datacontainer 'postgresql';
end;
will print output to the output window:
Oracle RDBMS Output
A (virtual) Invantive database can contain one or more Oracle RDBMS data containers, which each can print output.
For example, the following block of code:
begin
execute native 'begin dbms_output.put_line(''hello world using native Oracle RDBMS''); end;' datacontainer 'oracle';
end;
will print output to the output window:
SQL Server Output
A (virtual) Invantive database can contain one or more SQL Server data containers, which each can print output.
For example, the following block of code:
begin
execute native 'print ''hello world using native SQL Server''' datacontainer 'sqlserver';
end;
will print output to the output window:
Alternatives to execute native
Besides using procedural logic with Invantive PSQL, the Invantive UniversalSQL also provides native command execution using the view NativePlatformScalarRequests, available with each data container.
Tips on using NativePlatformScalarRequests can be found in Evaluate Native SQL Statements from Invantive PSQL. A practical use case which reaps enormous performanec benefits is illustrated in Estimate size of an Exact Online company.