A central access layer — the standardised reporting interface

Handing data over to other systems and producing reports can be covered by a single standard tool. The standardised reporting interface decouples users' database access from the actual data warehouse schema.

Overview

The purpose of a standardised access layer is to decouple data delivery to consumers from the underlying data structures of the warehouse. Table functions combine maximum flexibility with maximum performance, so the access layer becomes independent of structural changes to the warehouse schema.

The configuration and definition of the reports lives in a metadata repository. The table function provides the data to be delivered according to that definition and can be queried from the access layer with a standard SQL select statement. The querying user needs nothing more than select privileges on dual, which reduces the required privileges to an absolute minimum.

Metadata for the configuration

Reports and queries are configured through two tables. The master table holds the basic information about the underlying data set, which is defined by a view or a table.

CREATE TABLE rpt_master
 (  rep_id VARCHAR2 (10),
    name VARCHAR2 (100),
    date_format VARCHAR2 (20),
    number_format VARCHAR2 (2),
    filename VARCHAR2 (50),
    deliminator VARCHAR2 (5),
    table_name VARCHAR2 (50),
    where_cond VARCHAR2 (500),
    filename_date VARCHAR2 (20)
)
/

The second table defines the column names to be displayed.

CREATE TABLE RPT_FIELDS
(   rep_id VARCHAR2(10),
    header_text VARCHAR2(50),
    orderby NUMBER,
    column_name VARCHAR2(50)
 )
/

The table function

Reports are produced by the procedure pk_file_exp, which can both write files through utl_file and return data through a table function in SQL*Plus.

CREATE OR REPLACE PACKAGE Pkg_File_Exp
IS

   TYPE refcur_t IS REF CURSOR RETURN dual%ROWTYPE;

   -- für die Ausgabe über utl-file
   PROCEDURE process (
           p_report rpt_master.rep_id%type
           );

   -- für die Ausgabe über sql
   FUNCTION tf_process(
           p Pkg_File_Exp.refcur_t,
           v_rep_id in rpt_master.rep_id%type )
        RETURN T_file_exp_set PIPELINED;

end Pkg_File_Exp;
/

Returning the data

Data can be delivered either as data files — CSV, for example — or through a plain select statement in an Oracle session. A significant advantage of this approach is that the executing user only needs an execute privilege on the package pk_file_exp, not a select privilege on the underlying data view.

	begin
  pk_file_exp.process('MY_REP_ID');
end;

For output through SQL, the select statement can look like this:

select * from
   table (pk_file_exp.tf_process(CURSOR(select * from dual ,'rep_id')));