A blog about Application Express and Oracle related topics.

Debugging APEX Authentication Issues

Developers are sometimes lost when debugging APEX authentication or session related issues. For example, we regularly get help requests from developers who enabled Social Sign-In and get an error when APEX processes the apex_authentication.callback request. Because of redirects, they can not set the debug parameter in the URL to see what is going on.

There are two techniques to debug these issues, which I will explain below.

System Debug Scripts

APEX ships with a few sqlplus scripts for debugging. They are in directory apex/utilities/debug. You need to connect as SYS, APEX_nnnnnn or as another user with DBA privileges to run them. Below is an example setup for debug sessions in sqlplus:

$ sqlplus / as sysdba
SQL> alter session set container=my_pdb;
SQL> alter session set current_schema=apex_200100;
SQL> set serveroutput on size unlimited lines 250
SQL> define _editor=vim


This script prints recent activity log entries. It takes one sqlplus parameter, which is a comma separated list of settings. Below is an example:

APEX_200100@apx201dev> @activity last_days=1,error
...activity.sql last_days=1,error
TIME                ELAP  APP:PAGE     SESSION         VIEW_TYPE     USER                           DEBUG           ERROR
2020-04-02 05:45:39 0.028 4000:0       8774400245076   Ajax          nobody                                         Your session has expired\n\nClick <a href="f?p=4000:1500">here</a> to create a new s
2020-04-02 06:56:09 0.168 4550:1       5045198616010   Processing    nobody                                         Invalid Login Credentials (user=APEX_PM)
2020-04-02 07:44:18 0.051 557:9        22249639258846  Rendering     HILARY                         2811560         APEX - Region Static ID must be specified as the page contains multiple Interactive
2020-04-02 07:52:09 0.016 443:8        98060896989     Processing    JOHN                                           APEX - Session state protection violation: This may be caused by manual alteration o

Use @activity help for details and some examples. Note the ID in the DEBUG column. We can drill down and get more information about that request with d2.sql, see below.


The script d0.sql toggles LEVEL9 debug for the whole instance. If it is enabled, every request will log with LEVEL9. Be careful when doing that on busy systems, debug can significantly slow a system down. You should immediately disable it again after reproducing the error, by running d0.sql again.

APEX_200100@apx201dev> @d0
Changed debug level from "" to "9"
APEX_200100@apx201dev> @d0
Changed debug level from "9" to ""


With d1.sql, you get a report of the last 30 requests which had debug enabled. You can use it to find the request you are interested in and then drill down to the actual debug entries.

APEX_200100@apx201dev> @d1

PAGE_VIEW_ID  STARTED  SECS    COUNT PATH_INFO                    APP:PAGE   SESSION_ID                        WORKSPACE            USER
------------- -------- ------ ------ ---------------------------- ---------- --------------------------------- -------------------- ---------------------
@d2 3092855   22:12:03   1.21   1231 show IG[ig1]_managers        557:8      3881610683508                     HILARY               HILARY
@d2 3125238   01:21:17   0.00      1                              624:14     6424476817196                     JOHN                 JOHN


Alternatively, you can use ds.sql to get a report of requests with debug data for a given session:

APEX_200100@apx201dev> @ds 3881610683508

PAGE_VIEW_ID  STARTED  SECS    COUNT PATH_INFO                    APP:PAGE   SESSION_ID                        WORKSPACE            USER
------------- -------- ------ ------ ---------------------------- ---------- --------------------------------- -------------------- -----------
@d2 2947096   10:47:44   0.14    232                              557:2      3881610683508                     HILARY               HILARY
@d2 2947099   10:47:49   3.92   2736 f?p=557:2:3881610683508::LEV 557:2      3881610683508                     HILARY               HILARY


Finally, there is d2.sql, which logs the details for a given page view id into a file and opens that in the editor. Make sure that you set an _editor in sqlplus when running this script, or make yourself familiar with ed commands. The output looks like this:

  SECS LVL MESSAGE                                                                                    APP/PAGE SID/SGID USER      CALL_STACK1                                        CALL_STACK2
------ --- ------------------------------------------------------------------------------------------ -------- -------- --------- -------------------------------------------------- ------------------------
 0.000 -8- init cgi_var_name.count=>50                                                                     557 3881610~           %CGI.init:615                            
    0%                                                                                                         9100131~           %CGI.__pkg_init:744                                %LANG.reset_nls:557
                                                                                                                                  %DEBUG.log_session_info:1102                       %.start_page_request:236
 0.002 -9- CGI: PATH_INFO = /f?p=557:2:3881610683508::LEVEL9:::                                            557 3881610~           %LANG.reset_nls:557                      
    0%                                                                                                         9100131~           %.start_page_request:236                           F.f:175
 0.002 -9- GET                                                     557 3881610~
    0%     REMOTE_ADDR=                                                                            9100131~
           HTTP_COOKIE=ORA_WWV_REMEMBER_LANG=en; s_fid=06...
 0.003 -9- APEX Version=                                                                       557 3881610~
    0%     Patch Version=2020-06-01                                                                            9100131~
 0.003 -4- Reset NLS settings                                                                              557 3881610~
    0%                                                                                                         9100131~
 0.004 -4- alter session set  NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_SORT='BINARY' NLS_C      557 3881610~           %LANG.reset_nls:570                      
    0%     OMP='BINARY' NLS_CALENDAR='GREGORIAN'                                                               9100131~           %.start_page_request:236                           F.f:175
...                                                                                                                                                                                     F.f:175

Session Debug in the UI

It is not always feasible to use the debug scripts, e.g. when you do not have sqlplus access as a DBA or when the scripts are not available at a remote site. For these scenarios, APEX provides useful alternatives in the development environment.

Personally, I always prefer to use the scripts, as not even the APEX UI beats a good text editor for analysing these logs. If you need help with them and send the logs to me, please do me a favour and send d2.sql output instead of HTML or Excel sheets. If that is not possible, please at least include the call stack, it is by default hidden in the Interactive Report.

The first requirement is to get an APEX session. If your application does not have a public page, create one for debugging (make sure to remove it again afterwards).

Next, run this page. It is public, so APEX does not redirect to let you log in.

In a separate browser tab, the APEX development environment has to be open. Navigate to Administration > Monitor Activity, then click Active Sessions. Click on the public session for the app:

Change Debug Level to APEX Trace (a friendlier term for LEVEL9) and click Apply Changes. APEX will now log debug data for this session on each request, including apex_authentication.callback.

You now need to reproduce the error. In your application, change the URL to navigate to a protected page and press Enter. APEX will detect that authentication is required and redirect to your SSO login server. Enter your credentials and continue, until you see the APEX error page.

Finally, go to the development environment tab again. Press Go in the Page Views region below, to see the latest requests. Click on the Debug ID for the request that ran into an error to see the details, just like with d2.sql above.

APEX and Forms: Automatic Login

This note describes a simple way of integrating Oracle APEX with other frameworks. Developers do not want their users to log in again when navigating to APEX pages and they want to pass parameters, without creating a new APEX session on each request.

Very often, developers want to discuss these topics when they have existing Oracle Forms applications and need to extend them with APEX. Hence the title, but the solution below only assumes that you are on APEX >= 18.1 and the product you want to integrate with has access to the same database. For simplicity, the example uses sqlplus instead of Forms.

The idea is to create a JSON Web Token (JWT) with user credentials and some item values that we want to pass along, sign it with a secret key and append it to an APEX URL via the X01 parameter. If your APEX custom authentication scheme’s sentry function detects a JWT, it decodes it, sets the session user and any passed in parameters. Even if your page is checksum protected, X01 will not be part of the checksum. Note that you must keep the secret key hidden from other applications and users on the database. This obviously also means that you should not use the sample key from this example.

Let’s start with the implementation. In APEX, create a new authentication scheme of type “Custom”. It needs a custom sentry function that contains the logic for passing the username and item values:

function sentry
    return boolean
    l_x01      varchar2(32767);
    l_jwt      apex_jwt.t_token;
    l_jwt_user varchar2(255);
    l_jwt_elts apex_t_varchar2;
    -- parse JWT payload in X01
    l_x01 := v('APP_AJAX_X01');
    apex_debug.trace('X01=%s', l_x01);
    if l_x01 like '%.%.%' then
            l_jwt := apex_jwt.decode (
                         p_value         => l_x01,
                         p_signature_key => sys.utl_raw.cast_to_raw('secret!') );
            apex_debug.trace('JWT payload=%s', l_jwt.payload);
            apex_jwt.validate (
                 p_token => l_jwt,
                 p_iss   => 'sqlplus',
                 p_aud   => 'APEX' );
            apex_json.parse (
                 p_source => l_jwt.payload );
            l_jwt_user := apex_json.get_varchar2('sub');
        exception when others then
            apex_debug.trace('...error: %s', sqlerrm);
    end if;
    -- if not logged in yet:
    -- - log in with JWT user if JWT given
    -- - or trigger custom invalid session/login flow
    if apex_authentication.is_public_user then
        if l_jwt_user is not null then
            apex_authentication.post_login (
                p_username => l_jwt_user );
            return false;
        end if;
    elsif apex_application.g_user <> l_jwt_user then
        apex_debug.trace('...login user %s does not match JWT user %s',
            l_jwt_user );
        return false;
    end if;
    -- if JWT given, assign additional parameters to items
    if l_jwt_user is not null then
        l_jwt_elts := apex_json.get_members('.');
        for i in 1 .. l_jwt_elts.count loop
            if l_jwt_elts(i) like 'P%' then
                apex_debug.trace('...setting %s', l_jwt_elts(i));
                apex_util.set_session_state (
                    p_name  => l_jwt_elts(i),
                    p_value => apex_json.get_varchar2(l_jwt_elts(i)) );
            end if;
        end loop;
    end if;
    return true;
end sentry;

Users sometimes need to work with the APEX application, without coming from Forms. It would therefore be useful to have a login option, too. You can set the custom auth scheme’s “Authentication Function Name” to “-BUILTIN-” to use APEX login credentials or “-DBACCOUNT-” for database authentication. Of course, you can also implement your own user credentials checking in a custom function.

In Forms, you need to create the JWT with the same secret key, aud(ience) and iss(uer). For simplicity, the example below uses sqlplus to generate a URL:

CHRISTIAN@apx182dev> declare
  2      l_jwt varchar2(32767);
  3  begin
  4      l_jwt := apex_jwt.encode (
  5                   p_iss           => 'sqlplus',
  6                   p_aud           => 'APEX',
  7                   p_sub           => 'TESTUSER',
  8                   p_exp_sec       => 10,
  9                   p_other_claims  => '"P1_NEW1":'||
 10                                      apex_json.stringify('foo'),
 11                   p_signature_key => sys.utl_raw.cast_to_raw('secret!') );
 12      sys.dbms_output.put_line (
 13          ''||l_jwt);
 14  end;
 15  /

This token is valid for 10 seconds (p_exp_sec). As you can see, you are able to pass items via the p_other_claims JSON fragment. Use apex_json.stringify to properly escape item values.

When trying this out multiple times, you will notice that APEX generates a new session ID on each request. This is not ideal in many cases. You can use the application-level “Rejoin Sessions” attribute to get the same session. There are security implications with this attribute, though. Please follow the link to the documentation for more details.

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
--      Read Excel file (xls or xlsx) in PL/SQL, using a pipelined table
--      function.
--      SQL> select xls.*
--             from my_data,
--                  table(excelreader.get_cells(my_data.blob_value)) xls
--            where = 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
--    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

-- 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
--      Read Excel file (xls or xlsx) in PL/SQL, using a pipelined table
--      function.
--    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
    g_cells := new t_cells();

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

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

    g_cells := null;
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 )
    l_row t_cell;
    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(g_cells.count) := l_row;
end private_add_cell;

end excelreader;
show err

-- the parsing code below is based on
create or replace java source named "ExcelReader" as
import java.lang.*;
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.openxml4j.exceptions.InvalidFormatException;

public class ExcelReader {

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

    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 (
                                lValue );


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/”
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

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->headers_out->set(‘Content-Length’, length($got));
    } else {
        # pass through
        while ($f->read(my $buffer, BUFF_LEN)) {
    return Apache2::Const::OK;
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
    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,
             from emp )
              ‘ [label=”‘||
              replace(i.ename, ‘”‘, ‘\”‘)||
end loop;
— edges, via mgr
for i in ( select empno,
             from emp
            where mgr is not null )
    sys.htp.p(‘e’||i.empno||’ -> e’||i.mgr||’;’);
end loop;
— close graph
Finished! Here is the result:
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
        l_group  varchar2(4000) := p_authorization.attribute_01;
        l_count  number;
        l_result apex_plugin.t_authorization_exec_result;
        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.