PLSQL Server Pages

Recently, I’ve received a very interesting task at my job, I have to create a colourful report which uses different colours depending on values (if value < 5 then cell should has a green colour, if value between 5 and 9 then yellow, else red).

There are a lot of methods to create a report like this. First approach based on using any BI tool ( like SAS, Oracle BI, BO and etc.). These tools allow you to create a report and upload all data into Excel file. There is only one disadvantage – they cost money, so in my case I can’t use it because I don’t have a budget for that.

Second approach based on uploading a CSV file from Oracle DB by using PL/SQL, but in this case there is no opportunity to control the cells’ colours, so I’ve decided using PL/SQL server pages feature.

Let’s create a  simple example. First of all, create test tablespace and objects owner user.

SQL> create tablespace gena_tbls datafile '/u01/app/oracle/oradata/orcl/gena.dbf' size 100m autoextend on;

Tablespace created.

SQL> create user gena identified by gena default tablespace gena_tbls;

User created.

SQL> grant connect, resource to gena;

Grant succeeded.

Ensure that the database account ANONYMOUS is unlocked.

SQL> alter user anonymous account unlock;

User altered.

Create a simple table.

SQL> connect gena/gena
Connected.

SQL> create table t1 ( indicator_name varchar2(50), value number);

Table created.

SQL> insert into t1 values ('INDIC 1', 1);

1 row created.

SQL> insert into t1 values ('INDIC 2', 6);

1 row created.

SQL> insert into t1 values ('INDIC 3', 10);

1 row created.

SQL> insert into t1 values ('INDIC 4', 4);

1 row created.

SQL> insert into t1 values ('INDIC 5', 8);

1 row created.

SQL> commit;

Commit complete.

SQL>

Log on to the database as an XML DB administrator (SYS in this case), that is a user with the XDBADMIN role and create the DAD.

SQL> connect / as sysdba
Connected.
SQL> exec dbms_epg.create_dad('gena_dad', '/gena_report/*');

PL/SQL procedure successfully completed.

SQL>

Set the DAD attribute database-username to the database user whose privileges must be used by the DAD.

SQL> exec dbms_epg.set_dad_attribute('gena_dad', 'database-username', 'gena');

PL/SQL procedure successfully completed.

SQL>

Grant EXECUTE privilege to the database user GENA whose privileges must be used by the DAD.

SQL> grant execute on dbms_epg to gena;

Grant succeeded.

SQL>

Log on to the database as the database user whose privileges must be used by the DAD and authorize the embedded PL/SQL gateway to invoke procedures and access document tables through the DAD.

SQL> connect gena/gena
Connected.
SQL> exec dbms_epg.authorize_dad('gena_dad');

PL/SQL procedure successfully completed.

SQL>

Create a sample PL/SQL stored procedure. This procedure creates an HTML page that includes the result set of a query of gena.t1.

Ensure that the listener is able to handle HTTP requests.

[oracle@db ~]$ lsnrctl status listener

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 31-JUL-2012 12:24:24

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     listener
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                31-JUL-2012 12:24:00
Uptime                    0 days 0 hr. 0 min. 24 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/db/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@db ~]$

Run a web browser and put the following address: http://db:8080/gena_report/print_indicators