chrisonoracle

A blog about Application Express and Oracle related topics.

Read Excel file in PL/SQL, using Java in the DB

We made some necessary internal changes in APEX 4.2.3, that stopped the Apex Listener Excel upload from working. As mentioned on our Known Issues page, a fix is available now for 4.2.3. While investigating the problem, I noticed that it is quite easy to parse Excel directly in the database, with Java and the Apache POI library. Below is an example how to use POI from PL/SQL. As mentioned, we do have a fix now, but maybe my little experiment is still useful to somebody.

First, I downloaded POI from here (I used version 3.9). After unzipping, I installed lib/commons-codec-1.5.jar, poi-ooxml-3.9-20121203.jar and poi-3.9-20121203.jar with loadjava. The package below has a pipelined table function that returns Excel cells, see the spec for an example.

create or replace package excelreader as
--------------------------------------------------------------------------------
--
--    NAME
--      excelreader.sql
--
--    DESCRIPTION
--      Read Excel file (xls or xlsx) in PL/SQL, using a pipelined table
--      function.
--
--    SYNOPSIS
--      SQL> select xls.*
--             from my_data,
--                  table(excelreader.get_cells(my_data.blob_value)) xls
--            where my_data.id = 42;
--
--      SHEET_NAME              ROW_NUM    COL_NUM CELL_VALUE
--      -------------------- ---------- ---------- --------------------
--      Sheet1                        1          1 h1
--      Sheet1                        1          2 h2
--      Sheet1                        1          3 h3
--      Sheet1                        2          1 1
--      Sheet1                        2          2 two
--      Sheet1                        2          3 three
--
--    MODIFIED   (MM/DD/YYYY)
--    cneumuel    10/31/2013 - Created
--
--------------------------------------------------------------------------------

type t_cell is record (
    sheet_name varchar2(4000),
    row_num    number,
    col_num    number,
    cell_value varchar2(32767) );
type t_cells is table of t_cell;

--==============================================================================
-- pipelined table function to return cells of an excel (xls or xlsx) file
--==============================================================================
function get_cells (
    p_source in blob )
    return t_cells
    pipelined;

--==============================================================================
-- internal use only
--==============================================================================
procedure private_add_cell (
    p_sheet_name in varchar2,
    p_row_num    in number,
    p_col_num    in number,
    p_cell_value in varchar2 );

end excelreader;
/

create or replace package body excelreader as
--------------------------------------------------------------------------------
--
--    NAME
--      excelreader.plb
--
--    DESCRIPTION
--      Read Excel file (xls or xlsx) in PL/SQL, using a pipelined table
--      function.
--
--    MODIFIED   (MM/DD/YYYY)
--    cneumuel    10/31/2013 - Created
--
--------------------------------------------------------------------------------

g_cells t_cells;

--==============================================================================
procedure java_generate_cells (
    p_source in blob )
is language java
name 'ExcelReader.generateCells(java.sql.Blob)';

--==============================================================================
function get_cells (
    p_source in blob )
    return t_cells
    pipelined
is
begin
    g_cells := new t_cells();

    if p_source is not null then
        java_generate_rows (
            p_source => p_source );
    end if;

    for i in 1 .. g_cells.count loop
        pipe row (g_cells(i));
    end loop;

    g_cells := null;
    return;
end get_cells;

--==============================================================================
procedure private_add_cell (
    p_sheet_name in varchar2,
    p_row_num    in number,
    p_col_num    in number,
    p_cell_value in varchar2 )
is
    l_row t_cell;
begin
    l_row.sheet_name := p_sheet_name;
    l_row.row_num    := p_row_num;
    l_row.col_num    := p_col_num;
    l_row.cell_value := p_cell_value;
    g_cells.extend;
    g_cells(g_cells.count) := l_row;
end private_add_cell;

end excelreader;
/
show err

--
-- the parsing code below is based on
--
-- https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/ToCSV.java
--
create or replace java source named "ExcelReader" as
import java.lang.*;
import java.io.InputStream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

public class ExcelReader {

    static void addCell (
        String pSheetName,
        int    pRowNum,
        int    pColNum,
        String pVarchar2Value )
        throws Exception
    {
        #sql {
            begin
                excelreader.private_add_cell (
                    p_sheet_name => :pSheetName,
                    p_row_num    => :pRowNum,
                    p_col_num    => :pColNum,
                    p_cell_value => :pVarchar2Value );
            end;
        };
    }

    public static void generateCells (
        java.sql.Blob pBlob )
        throws Exception
    {
        InputStream      lBlobStream = pBlob.getBinaryStream();
        HSSFWorkbook     lWorkbook   = new HSSFWorkbook(lBlobStream);
        FormulaEvaluator lEvaluator  = lWorkbook.getCreationHelper().createFormulaEvaluator();
        DataFormatter    lFormatter  = new DataFormatter(true);
        //---------------
        HSSFSheet        lSheet;
        int              lNumSheets = lWorkbook.getNumberOfSheets();
        //---------------
        int              lLastRowNum;
        Row              lRow;
        //---------------
        int              lLastCellNum;
        Cell             lCell;
        //---------------
        String           lValue;

        for (int lSheetNum=0; lSheetNum < lNumSheets; lSheetNum++) {
            //
            // process sheet #lSheetNum
            //
            lSheet = lWorkbook.getSheetAt(lSheetNum);

            if (lSheet.getPhysicalNumberOfRows() > 0) {
                lLastRowNum = lSheet.getLastRowNum();
                for (int lRowNum = 0; lRowNum <= lLastRowNum; lRowNum++) {
                    lRow = lSheet.getRow(lRowNum);
                    //
                    // process row #lRowNum
                    //
                    if (lRow != null) {
                        lLastCellNum = lRow.getLastCellNum();
                        for (int lCellNum = 0; lCellNum <= lLastCellNum; lCellNum++) {
                            //
                            // process cell #lCellNum
                            //
                            lCell = lRow.getCell(lCellNum);
                            if (lCell == null) {
                                lValue = "";
                            } else if (lCell.getCellType() != Cell.CELL_TYPE_FORMULA) {
                                lValue = lFormatter.formatCellValue(lCell);
                            } else {
                                lValue = lFormatter.formatCellValue(lCell, lEvaluator);
                            }
                            ExcelReader.addCell (
                                lWorkbook.getSheetName(lSheetNum),
                                lRowNum+1,
                                lCellNum+1,
                                lValue );
                        }
                    }
                }
            }
        }

        lBlobStream.close();
    }
};
/

That code is not very efficient. It calls back from Java to PL/SQL for each cell, but better solutions should not be difficult to implement. Btw, there exists a solution for XLSX only (i.e. no XLS), in pure PL/SQL.

Showing diagrams in Apex, with Graphviz and OHS

This Apex forum thread reminded me that I mentioned how to integrate Graphviz in Apex to somebody at last year’s OOW. Since the next OOW is right around the corner, it’s high time to finally publish how this can be done.

Setting up an Output Filter in Oracle HTTP Server

The Apache-based OHS allows to specify output filters, to post-process data. Such filters can be written in shell script or Perl. My example uses the latter.

In dads.conf, I entered

LoadModule perl_module “${ORACLE_HOME}/ohs/modules/mod_perl.so”
PerlSwitches -I/home/cneumuel/perl
PerlModule CN::DotToSvgFilter

at the top level to load mod_perl, add my local Perl directory to Perl’s path and register my filter module. Then I added

PerlOutputFilterHandler CN::DotToSvgFilter

within the DAD’s <location> tags. This line tells OHS that all output from the DAD has to be post processed by my module.

In /home/cneumuel/perl/CN, I created the file DotToSvgFilter.pm:

package CN::DotToSvgFilter;
use strict;
use warnings;
 
use IPC::Open2;
 
use base qw(Apache2::Filter);
use Apache2::RequestRec ();
use APR::Table ();
use Apache2::Const -compile => qw(OK);
 
use constant BUFF_LEN => 4096;
 
sub handler : FilterRequestHandler {
    my $f = shift;
    my $r = $f->r;
 
    if ($r->content_type() =~ /^text\/dot;/) {
        #
        # convert dot to svg
        #
 
        my $pid = open2(*Reader, *Writer, “/home/cneumuel/usr/bin/dot -Tsvg”) or die “can not run dot”;
 
        while ($f->read(my $buffer, BUFF_LEN)) {
            print Writer $buffer;
        }
        close Writer;
        my $got = do { local $/; <Reader> };
        close Reader;
        waitpid $pid, 0;
        $r->content_type(‘image/svg+xml’);
        $r->headers_out->set(‘Content-Length’, length($got));
        $f->print($got);
    } else {
        #
        # pass through
        #
        while ($f->read(my $buffer, BUFF_LEN)) {
            $f->print($buffer);
        }
    }
 
    return Apache2::Const::OK;
}
1;
This script checks if the content type is “text/dot”. That’s not an official mime type, we just use that internally to tell the filter when content has to be post-processed. If we get “text/dot”, it filters the input with /home/cneumuel/usr/bin/dot, which is called with a parameter that specifies svg as the output format. Otherwise, the script just passes the input data through.

After a restart of OHS, the filter is active.

Creating a sample Apex application that uses the filter

I created a new application with a blank page. On that page, I defined a HTML region “Org Chart” with the following region source:

< object
    data=”f?p=&APP_ID.:&APP_PAGE_ID.:&SESSION.:APPLICATION_PROCESS%3dorg_chart_graph”
    type=”image/svg+xml” >
< /object >

I also created an Ajax callback on the page, called “org_chart_graph”:

– set mime type – converted by apache
sys.owa_util.mime_header (
    ccontent_type => ‘text/dot’,
    bclose_header => true );
– open graph
sys.htp.p(‘digraph orgchart {‘);
– empoyee nodes
for i in ( select empno,
                  ename
             from emp )
loop
    sys.htp.p(‘e’||i.empno||
              ‘ [label="'||
              replace(i.ename, '"', '\"')||
              '"];’);
end loop;
– edges, via mgr
for i in ( select empno,
                  mgr
             from emp
            where mgr is not null )
loop
    sys.htp.p(‘e’||i.empno||’ -> e’||i.mgr||’;’);
end loop;
– close graph
sys.htp.p(‘}’);
Finished! Here is the result:
Image
Note that this is just a simple graph, Graphviz has many advanced options (clickable nodes and edges, custom shapes, etc.) and different layout algorithms that are just a few lines of code away. The gallery has some quite impressive examples.

How to create an APEX 4.1 authorization plugin

The EA1 for APEX 4.1 is out, so people are trying to use what we built for the last months, hopefully with success. Building Authorization Plugins was one of my tasks. The new plugin architecture allows developers to build authorizations in a declarative way, instead of copying and pasting SQL and PL/SQL code. Here’s a small walkthrough on how to make use of it. The example plugin can be used for authorizations using the built-in user and group management of APEX.

  1. Go to Shared Components / Plug-ins
  2. Click “Create >”
  3. Enter Name: APEX Group Authorization
  4. Enter Internal Name: COM.MY-COMPANY.APEX.GROUP-AUTH
  5. Enter Type: Authorization Scheme Type
  6. Enter PL/SQL Code:
    function is_authorized (
        p_authorization in apex_plugin.t_authorization,
        p_plugin        in apex_plugin.t_plugin )
        return apex_plugin.t_authorization_exec_result
    is
        l_group  varchar2(4000) := p_authorization.attribute_01;
        l_count  number;
        l_result apex_plugin.t_authorization_exec_result;
    begin
        select count(*)
          into l_count
          from apex_workspace_group_users
         where user_name  = p_authorization.username
           and group_name = l_group;
    
        l_result.is_authorized := l_count > 0;
        return l_result;
    end is_authorized;
  7. Enter Execution Function Name: is_authorized
  8. Click “Create”
  9. Click “Add Attribute”
  10. Enter Label: Group Name
  11. Enter Type: Text
  12. Enter Required: Yes
  13. Click “Create”

The plugin has been created. Now we define an authorization based on the plugin.

  1.  Go to Shared Components / Authorization Schemes
  2. Click “Create >”
  3. Click “Next >”
  4. Enter Name: Is Manager
  5. Enter Scheme Type: APEX Group Authorization [Plug-in]
  6. Enter Group Name: Manager
  7. Enter Error Message: Only Managers can see that (it gets displayed if a page or application authorization fails)
  8. Click “Create”

To test our authorization scheme, we have to create users and assign groups to them, then apply the authorization to some pages, regions or items. But I’m sure you already know how to do that ;-)

In APEX 4.0, you would have probably built the authorization scheme using an exists SQL query. And copied the query to every other group authorization scheme (Is Sales, Is Accounting, …), which is harder to maintain. Plugins can help to encapsulate the authorization code in one place and prevent mistakes.

Because EA1 users do not have easy access to APEX package specs, here are the data type declarations for authorization plugins:

create or replace package wwv_flow_plugin as
...
type t_authorization is record (
    id                   number,          -- internal id of authorization scheme
    name                 varchar2(255),   -- authorization scheme's name (see step 17)
    username             varchar2(255),   -- current user's name
    attribute_01         varchar2(32767), -- plugin attribute values 1 to 15
    attribute_02         varchar2(32767),
    attribute_03         varchar2(32767),
    attribute_04         varchar2(32767),
    attribute_05         varchar2(32767),
    attribute_06         varchar2(32767),
    attribute_07         varchar2(32767),
    attribute_08         varchar2(32767),
    attribute_09         varchar2(32767),
    attribute_10         varchar2(32767),
    attribute_11         varchar2(32767),
    attribute_12         varchar2(32767),
    attribute_13         varchar2(32767),
    attribute_14         varchar2(32767),
    attribute_15         varchar2(32767) );

type t_authorization_exec_result is record (
    is_authorized        boolean );        -- set to true if authorization succeeds
...
end wwv_flow_plugin;

The 2nd type might seem excessive, but we prefer records, so extensions in later releases will not change function signatures.

EDIT: 2011-08-25
There was an inconsistency in the record types t_authorization and t_authentication that we could fix before the 4.1 release. Both now contain an attribute username, I renamed t_authorization‘s user_name. Thanks go to Scott Spendolini for finding that issue.

Follow

Get every new post delivered to your Inbox.