Access platform-native and Invantive SQL printed output

Invantive SQL 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 SQL also collects and provides the native platform’s output. This topic will illustrate some examples.

Invantive SQL 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 SQL output');
end;

will print a message to the output window:

dbms_output

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:

PostgreSQL raise notice

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:

Oracle dbms_output

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:

SQL Server print

Alternatives to execute native

Besides using procedural logic with Invantive PSQL, the Invantive SQL 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.