A short overview of Invantive PSQL packages

[forums]

Invantive PSQL provides three types of PSQL packages:

• driver-specific packages for execution within the context of a driver
• product-specific packages for use with a limited range of products
• generic packages available across all products

Driver-specific Packages

Driver-specific packages are available only when the driver is opened as a data container. For instance, when a connection has been made to the Dummy-driver, the following code will print Hello world! John Doe:

declare
  l_result varchar2;
begin
  l_result := itgen_dummy.hello_world('John Doe');
  dbms_output.put_line(l_result);
end;

Product-specific Generic Packages

Product-specific packages currently only apply to Invantive Cloud. For instance, the following code in an application module presents a CSV download from an URL (for full details see Create a mini-site to download your Exact Online articles):

declare
  l_output          varchar2;
begin
  --
  -- Create a CSV of all articles in Exact Online.
  --
  select listagg
         ( itm.code || ',' || itm.description
         , chr(13)
         )
  into   l_output
  from   exactonlinerest..items@eol itm
  ;
  --
  -- Add header.
  --
  l_output := 'Code,Description' || l_output;
  --
  -- Return to user.
  --
  cloud_http.set_response_body_text(l_output);
  cloud_http.set_response_content_type('text/csv');
end;

Generic Packages

The following code will sleep for 5 seconds, using a function within the generic package dbms_lock:

begin
  dbms_lock.sleep(5);
end;

Data Dictionary

Details on the available packages can be found by querying the following two views in the data dictionary:

• SystemPackages@DataDictionary: list of packages. When driver-specific, he column provider_name specifies the driver.
• SystemPackageFunctions@DataDictionary: list of functions per package. When driver-specific, he column provider_name specifies the driver.

By executing the query:

select provider_name
,      package_name || '.' || function_name
,      function_documentation
from   SYSTEMPACKAGEFUNCTIONS@DataDictionary
order
by     provider_name
,      package_name
,      function_name

an overview will be shown of all three types of packages and their functions: