I’ve discovered that if you use an item-plugin for the first item of a page and the property ‘Cursor Focus’ is set to ‘First item on a page’ the cursor will not be focused at the first item on the page but on the first item that is not an item-plugin.

It looks like that item-plugins are not taking into account when the cursor should be focused on the first item on the page. APEX will add first_field('<name of the first item that's not a item-plugin>'); to  the code that will be executed  when the page loads.

Workaround: set the property ‘Cursor focus’ to ‘Do not focus cursor’ and add first_field('<name first item>'); to the property ‘Execute when page loads’ of the page.

I’ve got a reaction from Patrick Wolf by e-mail that i want to share:

A better approach is to change the item-plugin: the item-plugin has to set is_navigable to true in the render result record to support the “First item on page” feature.

Inside APEX it’s possible to specify ‘Page Items to Submit’:

  • Within the action of the type ‘Execute PL/SQL-code’ of a dynamic action.
  • Within a region of the type ‘Interactive report’ or ‘Classic Report’.

The listed page items will be submitted to the server and thus available to use within your ‘PL/SQL-code’ or ‘Source’.
When you’re using a page item the ‘Session State Protection’ of this item must be ‘Unrestricted’.  If you use another setting,  par example ‘Checksum Required – Application Level’, the following Javascript-error will occur:

Firefox:
Error: JSON.parse: unexpected character
Source: http://localhost:8585/i/javascript/apex_4_1.min.js
Line: 16

Chrome:
Uncaught SyntaxError: Unexpected token <      apex_4_1.min.js:16

Watching our visitors and how they arrive on warp11.com I cam across this page:

http://krisrice.blogspot.com/2010/12/listener-startupshutdown-script.html

definately worthwile caching… here we go:

Kris gives a script, which I altered a little bit.

#!/bin/sh
#
#
# chkconfig: 2345 80 05
# description: This is a program that is responsible for taking care of
# starting the apex-listener as a service. 
#
# processname: apxlistener
# Red Hat or SuSE config: /etc/sysconfig/apxlistener
# Debian or Ubuntu config: /etc/default/apxlistener
#
. /etc/rc.d/init.d/functions
NAME="Oracle Application Express Listener"
JAVA="/usr/java/jdk1.7.0_04/bin/java"
APEX_LISTENER_HOME="/u01/app/oracle/product/1.1.3/apex-listener"
APEX_IMAGES="/u01/app/oracle/product/4.1/apex/images/"
LOGFILE="$APEX_LISTENER_HOME/apex_listener.log"
PIDFILE="$APEX_LISTENER_HOME/apex_listener.pid"

start() {
        echo -n "Starting $NAME: "
        if [ -f $PIDFILE ]; then
                PID=`cat $PIDFILE`
                echo APEX Listener already running: $PID
                exit 2;
        else
            nohup $JAVA -Dapex.home="$APEX_LISTENER_HOME/tmp.apxlistener" -Dapex.images="$APEX_IMAGES" -Dapex.port=8080 -jar "$APEX_LISTENER_HOME/apex.war" 2>&1 > $LOGFILE  &
            RETVAL=$!
            echo Started PID: $RETVAL
            echo
            echo $RETVAL >>$PIDFILE
            return $RETVAL
        fi

}

status() {
        echo -n "Status $NAME: "
        if [ -f $PIDFILE ]; then
                PID=`cat $PIDFILE`
                echo APEX Listener already running: $PID
                ps -ef | grep $PID
        else
                echo APEX Listener not running
        fi
}

stop() {
        if [ -f $PIDFILE ]; then
                PID=`cat $PIDFILE`
                echo -n "Shutting down $NAME PID:$PID"
                echo
                kill $PID
                rm -f $PIDFILE
        else
                echo APEX Listener not running
        fi
        return 0
}

log() {
        tail -f $LOGFILE
}

info() {
    echo Before first use you must run the command below manually to initiate the service.
    echo 
    echo $JAVA -Dapex.home="$APEX_LISTENER_HOME/tmp.apxlistener" -Dapex.images="$APEX_IMAGES" -Dapex.port=8080 -jar "$APEX_LISTENER_HOME/apex.war"
    echo 
}

case "$1" in
    start)
        start
        ;;
    stop)
        stop
        ;;
    status)
        status
        ;;
    restart)
        stop
        start
        ;;
    log)
        log
        ;;
    info)
        info
        ;;
    *)
        echo
        echo "Usage:  {start|stop|status|restart|log|info}"
        echo
        info
        exit 1
        ;;
esac
exit $?

You should edit highlighted lines:
JAVA: the location of your JDK. be advised the minimum version your listener needs
APEX_LISTENER_HOME: the location the listener uses to store its config files
APEX_IMAGES: the location of the apex images directory

Create the file as /etc/init.d/apxlistener

Now set it as executable

chmod a+x /etc/init.d/apxlistener

add it to the startup-list:

chkcondig --add apxlistener
chkconfig apxlistener on

Now we’re allmost there. The listener needs a console to do the initial configuration. If you would start the service as is, it will not have a console.

I added an option “info” that echoes the commandline the service will use:

so.. execute:

/etc/init.d/apxlistener info

will give you something like this:

Before first use you must run the command below manually to initiate the service.

/usr/java/jdk1.7.0_04/bin/java -Dapex.home=/u01/app/oracle/product/1.1.3/apex-listener/tmp.apxlistener -Dapex.images=/u01/app/oracle/product/4.1/apex/images/ -Dapex.port=8080 -jar /u01/app/oracle/product/1.1.3/apex-listener/apex.war

execute that line in your console, it will ask for two usernames ( I used “admin” in both cases) and passwords.

Enter them as requested after which you can go to http://yourserver:8080/apex/listernerConfigure

Here you can enter your details as described in the apex listener.

after a reboot the listener should start automatically.

By using Shared Components->Text Messages it’s possible to translate internal messages used by APEX.

See http://docs.oracle.com/cd/E17556_01/doc/user.40/e15517/global.htm#CHDJFHBD for a more detailed description of translating internal messages. The list that’s given is not complete.

How can you find the internal message you need to translate when it’s not in the list as mentioned above? You can find the internal message by looking in the table ‘WWV_FLOW_MESSAGES$’.

Example:

I wanted to translate the message ‘Password Expired’ that is issued by the procedure ‘apex_authentication.login’ (see the process ‘Login’ of the default login-page (101) that’s created when creating a new application) when a password is entered that is expired.

Because the message that I needed was not mentioned in the list I looked in the table ‘WWV_FLOW_MESSAGES$’. I found the message ‘PASSWORD_EXPIRED’ but that it’s not the message I must use to translate ‘Password Expired’. Instead of the message ‘PASSWORD_EXPIRED’ the message ‘F4500_P2613_EXPIRED’ should be used. So the table ‘WWV_FLOW_MESSAGES$’ contains two messages with the text ‘Password Expired’ and at first I was using the wrong one.

Another example:

To translate the hint-text ‘Calendar’ of the image of the datepicker you’ll find 4 messages that contains the text ‘Calendar’: ‘CALENDAR_TYPE’, ‘F4000.CALENDAR’, ‘WWV_FLOW_UTILITIES.CAL’ and ‘APEXIR_CALENDAR’. I’ve tried all four and ‘CALENDAR_TYPE’ is the message to use.

Recently (since 4.1 update) I’ve experienced issues when importing an application into an Apex environment.

After a little bit of searching I found the way to do the import on the command-line.

It is documented in the Apex documentation, but as you know I’d like this blog to be an “OPI” (one point of information) 🙂

Please have a look at the script, alter it whenever appropriate.

declare
  -- the name of the workspace in wich to import
  t_workspace    varchar2(30):= 'MERIDA';
  -- an application number of an existing application in the workspace.
  t_existing_app number      := 100;
  -- the "new" application number, an existing number will be dropped first.
  t_new_app      number      := 100;
  -- security group id, you don't have to set this variable
  t_secgrp_id    number;
begin
  -- get the Security Group ID
  select workspace_id
  into   t_secgrp_id
  from   apex_applications
  where  application_id = t_existing_app;

  -- Set the Security Group ID
  wwv_flow_api.set_security_group_id(p_security_group_id => t_secgrp_id);
  -- Set the Application ID to use
  apex_application_install.set_application_id(t_new_app);
  -- This procedure generates the offset value used during application import.
  -- The offset value is used to ensure that the metadata for the Application
  -- Express application definition does not collide with other metadata on
  -- the instance. For a new application installation, it is usually sufficient
  -- to call this procedure to have Application Express generate this offset
  -- value for you.
  apex_application_install.generate_offset;
  -- Set the parsing schema
  apex_application_install.set_schema( t_workspace );
  -- Set the application alias
  apex_application_install.set_application_alias( 'F' || t_new_app );
end;
/
-- Do the actual import
@D:ProjectsStaplesMeridaexportsmerida-srv022-20111222-1157.sql;

commit;

That’s all folks 🙂

Creating a new page in an application I’m writing for a customer APEX gave me an error against the WWV_FLOW_UNIQUE_MENU_OPT constraint, after I hit the finish button in the last step of the wizard.. Now, that’s strange, why would APEX break on me in this situation?

I logged in as APEX_040100 to see and understand what actually went wrong.

The constraint mentioned is an index on the WWV_FLOW_MENU_OPTIONS table on columns MENU_ID and PAGE_ID. The page_id is the page_id as known in apex (1040) in my case. The menu_id seems to be some kind of identifier, in my case ‘6.69651082468548E15’.

Hmm.. what can we do to resolve this?

I think this index is in error.. shouldn’t the flow_id also be taken in consideration?

Apparently it is: the menu_id seems to be the same for all pages within the same flow.

Even after a restart of the database I’m not able to generate pages with the requested Page_id (1040).

After a bit of searching on the APEX forums at Oracle it ended to be a very logical problem.

As I often do for referential source data, I created a “Form on a table with report”. I tend to put both the report and the form on one page.

This is easy in maintenance. The report I put first and in column 1, the form I put in second and in column 2. This way I have an easy way of manipulating the source data.

However in my normal examples I don’t use breadcrumbs, and in this case I did. The wizard gives me the option, so why shouldn’t I?

The index is preventing me to insert two records in the table for those two breadcrumbs on one and the same table.

So the index IS correct afterall.

I do think that:

  • The wizard should detect that I’m using one page for both report and form and therefore only insert one breadcrumb
  • The wizard should warn me that I’m using one form AND breadcrumbs on bith pages.

Apex team? This probably will get a low priority, but the bug is ugly and easily to prevent.

Introduction

For a client of me, the Ministry of Defence of Kingdom of The Netherlands I have developed a solution to use CDM RuleFrame with APEX. They are using CDM RuleFrame 6.5, Apex 4.0 and  the framework ‘ApexLib’.

CDM RuleFrame with APEX

If CDM RuleFrame is used and one or more business rules are violated on the APEX’s Error Page an error message will be shown that is very cryptic to the end user. Example:

‘ORA-20505: Error in DML: p_rowid=346, p_alt_rowid=TANKBON_ID, p_rowid2=, p_alt_rowid2=. ORA-20998: Transaction failed ORA-06512: at "DGP.QMS$ERRORS", line 184 ORA-06512: at "DGP.QMS_TRANSACTION_MGT", line 840 ORA-06512: at "DGP.CG$AUS_DGP_TANKBONNEN", line 64 ORA-04088: error during execution of trigger 'DGP.CG$AUS_DGP_TANKBONNEN'
 Unable to process row of table DGP_TANKBONNEN.’

The error message issued by CDM RuleFrame is ‘ORA-20998 Transaction failed’ (added with the (back)trace). The ‘real’ error messages are ‘stored’ in the database – in the same session that is used for the DML – and must be retrieved separately.

To retrieve the error messages from the database APEX will issue a new database connection and so it is not guaranteed that the same database connection is used and the correct error messages are retrieved. To avoid this problem, the ‘real’ error messages are added to ‘ORA-20998’ (without ‘Transaction failed’). To recognize the ‘real’ error messages easily they are surrounded by a start- (~QMS~) and end tag (|QMS|). Example:

‘ORA-20505: Error in DML: p_rowid=344, p_alt_rowid=TANKBON_ID, p_rowid2=, p_alt_rowid2=. ORA-20998: ~QMS~DGP-90301 Aantal liters moet groter of gelijk zijn aan 0. (344)<br >DGP-90302 : Kilometerstand moet groter of gelijk zijn aan 0. (344)|QMS| ORA-06512: at "DGP.QMS$ERRORS", line 172 ORA-06512: at "DGP.QMS_TRANSACTION_MGT", line 840 ORA-06512: at "DGP.CG$AUS_DGP_TANKBONNEN", line 64 ORA-04088: error during execution of trigger 'DGP.CG$AUS_DGP_TANKBONNEN'
 Unable to process row of table DGP_TANKBONNEN.’.

To achieve this some adjustments to CDM RuleFrame are necessary.

An additional advantage of this solution is that in par example SQL Plus the ‘real’ error messages are shown directly too. You do not have to retrieve the ‘real’ error messages anymore. However, the format of the error messages differs from the format that is used for APEX. So it is necessary that APEX ‘tells’ CDM RuleFrame that APEX is used.

Despite of the fact that the error message that is returned by CDM RuleFrame contains the ‘real’ error messages it is still not very readable for the end user. In addition, the error message is still shown on the ‘Error Page’ instead of on the page itself (like the other error messages issued by APEX). In order to solve this some adjustments to APEX are necessary.

Adjustments for CDM RuleFrame (database)

Create a package with the name ‘QMS_APEX’. This package contains several settings (par example the start- and end tag surrounding the error messages) for using CDM RuleFrame with APEX.

Source package specification:

CREATE OR REPLACE PACKAGE QMS_APEX AS
  FUNCTION get_message_tag_start RETURN VARCHAR2;
  FUNCTION get_message_tag_end   RETURN VARCHAR2;
  FUNCTION get_apex_used         RETURN VARCHAR2;
  PROCEDURE set_apex_on;
  PROCEDURE set_apex_off;
END QMS_APEX;

Source package body:

CREATE OR REPLACE PACKAGE BODY QMS_APEX AS
  gc_apex_used         varchar2(1)  := 'N';
     gc_message_tag_start varchar2(10) := '~QMS~';
     gc_message_tag_end   varchar2(10) := '|QMS|';
    FUNCTION get_apex_used
     RETURN VARCHAR2
     IS
     BEGIN
         return gc_apex_used;
     END get_apex_used;
    PROCEDURE set_apex_on
     IS
     BEGIN
         gc_apex_used := 'J';
     END;
    PROCEDURE set_apex_off
     IS
     BEGIN
         gc_apex_used := 'N';
     END;
    FUNCTION get_message_tag_start
     RETURN VARCHAR2
     IS
     BEGIN
          return gc_message_tag_start;
     END get_message_tag_start;
    FUNCTION get_message_tag_end
     RETURN VARCHAR2
     IS
     BEGIN
          return gc_message_tag_end;
     END get_message_tag_end;
END QMS_APEX;

For this package – equals the other packages of CDM RuleFrame – a synonym with the same name (public or private) should be created and execute-privileges should be granted to the correct user(s) or role(s).

In addition, the existing procedure ‘QMS$ERRORS.RaiseQMSFailure’ should be changed as follows:

procedure RaiseQMSFailure
 is
     --
     l_apex_used           boolean := qms_apex.get_apex_used = ‘J’
     l_message_rectype_tbl hil_message.message_tabtype;
     l_message_count       number;
     l_raise_error         boolean;
     l_error_display       varchar2(4000);
     l_next_line           varchar2(10);
     --
 begin
    --
    cg$errors.get_error_messages(p_message_rectype_tbl => l_message_rectype_tbl
                                ,p_message_count       => l_message_count
                                ,p_raise_error         => l_raise_error
                                );
    --
    if l_message_count > 0
    then
       --
       if not l_apex_used
       then
           l_error_display := 'Transaction failed' || chr(10); -- not needed for APEX
           l_next_line     := chr(10);
       else
           l_error_display := qms_apex.get_message_tag_start;
           l_next_line     := '<br >';
       end if;
       --
       for i in 1..l_message_count loop
          --
          if l_message_rectype_tbl(i).severity = 'E'
          then
              l_error_display := substr(   l_error_display
                                        || case
                                              when i > 1 then l_next_line
                                           end
                                        || l_message_rectype_tbl(i).msg_code
                                        || ' '
                                        || l_message_rectype_tbl(i).msg_text
                                        ,1,4000);
          end if;
          --
          -- Put the message back on the stack to preserve the error stack
          --
          cg$errors.push(p_errorrec => l_message_rectype_tbl(i));
          --
       end loop;
       --
       if l_apex_used
       then
           l_error_display := l_error_display || qms_apex.get_message_tag_end;
       end if;
       --
    end if;
    --
    raise qms$exception;
    --
 exception
    when others
    then
       raise_application_error(-20998,l_error_display);
 end RaiseQMSFailure;

The existing source:

procedure RaiseQMSFailure
 is
 begin
    raise qms$exception;
 exception
    when others
    then
           raise_application_error(-20998,'Transaction Failed');
 end RaiseQMSFailure;

By calling several procedures of CDM RuleFrame from the procedure ‘QMS$ERRORS.RaiseQMSFailure’ for several functions/procedures compiling errors will occur regarding ‘PRAGMA RESTRICT REFERENCES’. Since Oracle 8i the ‘PRAGMA’ are not necessary anymore and so the ‘PRAGMA’ that are causing the compiling errors can easily be deleted.

It concerns the following procedures/functions:

  • QMS$ERRORS.RaiseQMSFailure
  • QMS$ERRORS.OldHeadstartException
  • QMS$ERRORS.internal_error
  • QMS$ERRORS.show_message
  • QMS$ERRORS.show_debug_info
  • QMS$ERRORS.unhandled_exception
  • QMS_PROFILE.get_profile_value
  • HIL_PROFILE.get_profile_value
  • QMS_MESSAGE.get_message
  • QMS_MESSAGE.SendDebugPipe
  • HIL_MESSAGE.get_message
  • HIL_MESSAGE.SendDebugPipe
  • CG$ERRORS.push (p_error_rec)
  • CG$ERRORS.QMSRecord2Stack

Adjustments for the APEX-application

The framework ‘ApexLib’ (http://apexlib.oracleapex.info/) is used. By using ‘ApexLib’ error messages raised within the database are already shown on the page itself instead of on the ‘Error Page’. To achieve this ‘ÁpexLib’ use a ‘trick’: code is added to the ‘Error Page’ to retrieve any messages displayed on the ‘Error Page’ and store them in a cookie. After that the ‘Error Page’ is left to go back the page. On this page the messages stored in the cookie are displayed.

To retrieve the messages from the ‘Error Page’ ‘ApexLib’ has added the code displayed below to the section ‘Error Page Template Control’ of each Page-template used by the application. This code has been changed to extract the ‘real’ error messages from the error message issued by CDM RuleFrame; the modifications are marked with ‘// CDM RuleFrame’.

<div id="ApexLibErrorMessage" style="display:none">#MESSAGE#
 </div>
 <a href="javascript:ApexLib_extractErrorPage()">#OK#</a>
<script language="javascript">
 function ApexLib_extractErrorPage()
 {
  // CDM Ruleframe
 function extractMessage(pMessage)
  {
    var vTagStart = "~QMS~";
    var vTagEnd = "|QMS|";
    var vMessage = pMessage;
    var vStart = pMessage.indexOf(vTagStart);
    if (vStart != -1)
    {
      var vEnd = pMessage.lastIndexOf(vTagEnd);
      if (vEnd != -1)
      {
        vMessage = vMessage.substring(vStart+vTagStart.length,vEnd);
      }
    }
    return vMessage;
  }
  // CDM Ruleframe
  var vElementList = null;
  var vErrorStack  = null;
  //----------------------------------------------------------------------------
  // Find our ErrorPageMessage and ApexLibErrorMessage div elements.
  // Extract and store them in a cookie
  //----------------------------------------------------------------------------
  vElementList = window.document.getElementsByTagName("div");
  for (i=0; i < vElementList.length; i++)
  {
    if ((vElementList[i].className == "ErrorPageMessage") ||
        (vElementList[i].className == "ApexLibErrorMessage"))
    {
      vErrorStack=(vErrorStack==null?"":vErrorStack+"<br />")+vElementList[i].innerHTML;
    }
  }
  // store the error message in a cookie
  // CDM Ruleframe
  vErrorStack = extractMessage(vErrorStack);
  //CDM Ruleframe
  var vCookieCount   = 0;
  var vCookieContent = null;
  while (vErrorStack != "")
  {
    vCookieCount++;
    vCookieContent = vErrorStack.substr(0, 4000);
    vErrorStack    = vErrorStack.substr(3999);
  document.cookie="ApexLibErrorStack"+vCookieCount+"="+encodeURIComponent(vCookieContent);
  }
  //----------------------------------------------------------------------------
  // now go back the the previous page, on that page an onload event will check
  // if the cookie exists and paste it into the page.
  //----------------------------------------------------------------------------
  window.history.go(-1);
 } // ApexLib_extractErrorPage
// If debug mode is enabled, don't do a redirect immediatly so that
 // the debug info isn't lost
 if (!html_GetElement('pdebug'))
 {
   ApexLib_extractErrorPage();
 }
 else
 {
   document.getElementById("ApexLibErrorMessage").style.display="block";
 }
 </script>

N.B. If you change the start- and end tag in the package ‘QMS_APEX’ the function ‘extractMessage’ should be changed accordingly.

The ‘trick’ used by ‘ApexLib’, can also be used without using ‘ApexLib’:
– the code for the ‘Error Page’ can be equal to the code used by ‘ApexLib’.
– for an example of the code to retrieve the messages stored in the cookie and display them in the page take a look at the function ‘apexlib.error.handleErrorPage’ (and ‘apexlib.error.init’ for the initialization) in the ‘ApexLib’-library (Javascript-library). The call to these functions are added to page zero.

Finally the process ‘Set Apex on’ should be added to the application:
– process point: ‘On Submit: After Page Submission – Before Computations and Validations’
– type: ‘PL/SQL Anonymous Block’
– process text: ‘qms_apex.set_apex_on’

This process ensures that CDM RuleFrame knows that APEX is being used. When APEX is used CDM RuleFrame will format the error messages for APEX. Normally CDM RuleFrame will format the error messages to display them for example by SQL Plus.

This is a one-on-one copy of an item blogged by Scott Splendolini

<COPY>

Earlier today, I tweeted the following:

After this morning, I don’t think I will ever use the “v” function again. #orclapex

I wanted to qualify what I meant by that, since sometimes you only see one side of the conversation on Twitter. Also, it’s been a while since my last post, so this give me the opportunity to remedy that as well. The APEX “v” function works, and works quite well. For those who have not used it, the “v” function is an APEX-specific function that when you pass an APEX item to it, it will return the value of that item for a specific user session. What’s cool about it is that it also works from named PL/SQL program units, as long as they were initiated from an APEX session. Thus, you can write a PL/SQL package that takes in few, if any parameters and still can refer to items that are set in the APEX session state via the “v” function:

PROCEDURE foo
IS
  l_customer_name VARCHAR2(255) := v('P1_CUSTOMER_NAME');
BEGIN
  ...
END; /

The specific issue that I had was that I did used the “v” function in quite a few places across a suite of PL/SQL packages. It cut down on what I needed to pass from package to package, and even allowed me to omit some procedures from the package specification. It worked magnificently. That is, until I tried to call one of the packages from SQL*Plus. Since there is no APEX session context set in SQL*Plus – and even if there was, which you can do, the items that I required to be set would not be – my package failed spectacularly. Thus, I had to go back through several packages and retro-fit them to be APEX-agnostic and remove all traces of the “v” function in favor of parameters. The lesson to learn from this is simple: take some time to consider whether or not you think a block of code will ever be called from outside of APEX. Even if there is a remote chance that it will, it may pay off big time later if you choose to make that code APEX-agnostic and rely on parameters instead.

</COPY>

Hi,

I recently installed a new server (mainly by using the installation guide you can find in the menu).

I think this needs an extra step, since I wasn’t able to send out emails from that system using Apex.
In the email-queue it kept saying: ORA-24247: network access denied by access control list (ACL)

A quick “Google”, I ended up here : http://blogs.oracle.com/pankaj/entry/how_to_get_mail_working_with_a

So I needed to execute something (read the blog), I did that as SYS:

<EDIT 12 sep 2011>
Please adjust the name of your apex schema in the code below, by adding / removing the remarks for the t_apex_user variable within the declare section of the code.
</EDIT>

<EDIT 29 mar 2013>
I altered the script into a more generic thing. It now takes a few variables:
t_apex_user contains the user for which you need to grant.
t_hostname is the host invloved (this can be ‘*’)
t_new_acl_name holds the name of the ACL in case a new ACL must be created
t_new_acl_descr is the description for this ACL
This makes this into a more generic ACL script, not only for opening up email for the apex user
</EDIT>

declare
  t_acl_path varchar2(4000);
  t_acl_id raw(16);
--  t_apex_user varchar2(30) := 'APEX_030200';
--  t_apex_user varchar2(30) := 'APEX_040000';
  t_apex_user varchar2(30)       := 'SPELEN';
  t_hostname  varchar2(2000)     := 'accounts.google.com';
  t_new_acl_name varchar2(2000);
  t_new_acl_descr varchar2(2000) := 'ACL that lets power users to connect to maps.google.com';
begin
  -- Look for the ACL currently assigned to '*' and give t_apex_user
  -- the "connect" privilege if t_apex_user does not have the privilege yet.
  
  if t_hostname = '*' then
    t_new_acl_name := 'power_users.xml';
  else
    t_new_acl_name := t_hostname || '.xml';
  end if;

  select acl
  into   t_acl_path
  from   dba_network_acls
  where  host = t_hostname
    and  lower_port is null
    and  upper_port is null;

  -- Before checking the privilege, make sure that the ACL is valid
  -- (for example, does not contain stale references to dropped users).
  -- If it does, the following exception will be raised:
  --
  -- ORA-44416: Invalid ACL: Unresolved principal t_apex_user
  -- ORA-06512: at "XDB.DBMS_XDBZ", line ...
  --
  select sys_op_r2o(extractvalue(p.res, '/Resource/XMLRef'))
  into   t_acl_id
  from   xdb.xdb$acl a
  ,      path_view p
  where  extractvalue(p.res, '/Resource/XMLRef') = ref(a)
    and  equals_path(p.res, t_acl_path) = 1;

  dbms_xdbz.validateacl(t_acl_id);
  if dbms_network_acl_admin.check_privilege(t_acl_path, t_apex_user, 'connect') is null
    then
      dbms_network_acl_admin.add_privilege(t_acl_path, t_apex_user, true, 'connect');
  end if;

  commit;

exception
  -- when no acl has been assigned to '*'.
  when no_data_found
    then
      dbms_network_acl_admin.create_acl(t_new_acl_name, t_new_acl_descr, t_apex_user, TRUE, 'connect');
      dbms_network_acl_admin.assign_acl(t_new_acl_name, t_hostname);
end;

I had to replace the “FLOWS_030000” by “APEX_040000” in Pankaj’s script. After this everything worked like a charm.

This is part two of my series regarding a fully freeware APEX setup.

Post 1 coveres the installation of Oracle XE 11 beta on CentOS 5

Shut Down Apex-EPG

Log in as system into the XE database using SQL*plus. We must close the EPG within the database, because Tomcat is going to use the same port (8080)

execute dbms_xdb.sethttpport(0);

Tomcat installation

I took this from here.

su -
cd /etc/yum.repos.d
wget 'http://www.jpackage.org/jpackage50.repo' yum update
yum install tomcat6 tomcat6-webapps tomcat6-admin-webapps
service tomcat6 start

Tomcat is running!

tomcat_01

That was easy enough. We need to do some configuration.

If you get dependency errors please have a look at the original post essentially it says to try this:

rpm -Uvh http://plone.lucidsolutions.co.nz/linux/centos/images/jpackage-utils-compat-el5-0.0.1-1.noarch.rpm

and then try again.

Firewall configuration

setup
"Firewall configuration"
"Customize"
"Other ports" 8080:tcp
"Ok"
service tomcat6 restart

Downloads: APEX Listener and APEX itself

Even though XE 11g is shipped with Apex preconfigured, for this setup we do need to link to the images folder. We do this by downloading the full Apex archive. At the same time we can allready download the listener as well:

Unzip Apex:

unzip apex_4.0.2.zip
unzip apex_listener.1.1.2.131.15.23.zip -d apex_listener

We only need the images folder from the apex distribution. Nevertheless it can come in handy when the full apex installer is available.

The Tomcat htmlroot (or docroot) is located at /var/lib/tomcat6/webapps.

We copy the apex and the listener installer files into the Oracle tree and create a symbolic link in tomcats’ webapps folder:

su -
mkdir $ORACLE_BASE/product/4.0.2
cp -r apex $ORACLE_BASE/product/4.0.2/apex/
ln -s $ORACLE_BASE/product/4.0.2/apex/images /var/lib/tomcat6/webapps/i
mkdir $ORACLE_BASE/product/1.1.2
cp -r apex_listener $ORACLE_BASE/product/1.1.2/apex_listener

Tomcat configuration

Edit the /etc/tomcat6/tomcat-users.xml. We’re still root..

gedit /etc/tomcat6/tomcat-users.xml

replace the last line (having “</tomcat-users>”). Replace the username and password as desired.

  <role rolename="manager"/>
  <role rolename="admin"/>
  <role rolename="Manager"/>
  <role rolename="Admin"/>
  <user username="apex" password="verysecret" roles="manager,admin,Manager,Admin"/>
</tomcat-users>

It’s a bit silly that the roles are mentioned twice, but apparently not all implementations use the same casing.
I’m using the “apex” user here with the password “verysecret”

Save the file (Duhh 😉 )

Now we must make sure that Tomcat gets started at boot-time:

chkconfig --add tomcat6
chkconfig tomcat6 on
reboot

That should do the trick. So far for Tomcat for now.

Installing the APEX listener

First we need to unlock the “apex_public_user” and set its password. Open up the SQL*plus commandline and enter:

alter user apex_public_user account unlock;
alter user apex_public_user identified by the_secret_password;

Now open Tomcat manager: http://localhost:8080/manager/html. Go to the section: WAR file to deploy.

tomcat_manager_01

Click the browse button and select the warfile from /u01/app/oracle/product/1.1.2/apex_listener/apex.war

Click the “deploy” button.

Now you’re ready to configure the listener