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.

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>

<EDIT sept 2, 2011>

Today oracle released the definitive version of Oracle XE 11.2. I haven’t had the opportunity to test that version against my blogs, but I’ll do that shortly. stay tuned

</EDIT>

In addendum on my installation series (OraXE11 on CentOS5.5) here my experiences on doing the same on CentOS6

Please be informed that Oracle is not supported on CentOS (let alone CentOS 6) !!

Swap area

When doing the installation on a virgin system (CentOS 6), I discovered that XE now requires a 2Gb swap area. Searching around I found this. It coveres an installation of XE on Ubuntu, but I only used the swap suggestion.

cat /proc/meminfo

That should give you info on the current status.

look at SwapTotal and SwapFree

SwapTotal:       1048564 kB
SwapFree:        1048564 kB

To install a 1 GB swapfile named swapfile in /, for example:

dd if=/dev/zero of=/swapfile bs=1024 count=1048576

This may take a while. After it completes, issue:

mkswap /swapfile
swapon /swapfile
cp /etc/fstab /etc/fstab.orig
echo '/swapfile swap swap defaults 0 0' >> /etc/fstab

You can check it too:

swapon -a
swapon -s

You’re ready to do the actual installation.

Installation

First you need to make sure libaio and are installed.

yum install libaio bc

And do the installation

mkdir OracleXE
unzip linux.x64_11gR2_OracleXE.zip -d OracleXE
cd OracleXE
rpm -ivh oracle-xe-11.2.0-0.5.x86_64.rpm

Oracle now needs to get configured:

/etc/init.d/oracle-xe configure

Specify the HTTP port that will be used for Oracle Application Express [8080]: <- ENTER
Specify a port that will be used for the database listener [1521]: <- ENTER
Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of
different passwords for each database account.  This can be done after
initial configuration: TYPE YOUR PASSWORD
Confirm the password: TYPE YOUR PASSWORD
Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]: <- ENTER

Starting Oracle Net Listener...Done
Configuring Database...

This takes a looooong while

...Done
Starting Oracle Database 11g Express Edition instance...Done
Installation completed successfully.

You can now continue at step 4 in step 1 of the installation guide

That’s it folks

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.

For me, myself and I (as a reminder) 🙂

I needed to calculate the difference between a log entry and the previous log-entry to be able to investigate where performance was dropping:

With alittle bit of help here and here I figured out how to do it:

with iv 
as  (select PROGRAM
     ,      RUN_ID
     ,      PAL_N_LEVEL
     ,      MESSAGE
     ,      DATE_TIME
     ,      LOG_D_DATE
     ,      case
              when message like 'Start%' then ''
              when message like 'End%' then ''
              else substr(to_char(numtodsinterval(LOG_D_DATE - lag(LOG_D_DATE, 1, sysdate) over (order by LOG_D_DATE asc), 'day')), 15, 5)
            end time_spent
     from   vla 
     where  run_id = 264225 
     order  by log_d_date asc)
select PROGRAM
,      RUN_ID
,      PAL_N_LEVEL
,      MESSAGE
,      DATE_TIME
,      LOG_D_DATE
,      TIME_SPENT "Time spent (mm:ss)"
from   iv
where  1=1
  and  (1=0
        or message like 'refreshed%'
--        or message like 'refresh%'
        or message like 'Start%'
        or message like 'End%'
       )
Posted in SQL.

This is post 7 in the Fully Freeware APEX environment series. The post will cover the configuration of the Oracle HTTP server.

Please mention, that I’m not fully aware about the license restrictions on the Oracle HTTP server. What I heard is that if you use it for an APEX installation and install it physically on the same box as the Oracle database server there is no license fee. But please correct me if I’m wrong.

Information has been taken from

  • The APEX installation guide

Unlocking the APEX public user account

Unlocking and chinging its password:

connect system
alter user apex_public_user account unlock;
alter user apex_public user identified by secret_password;

Unzip the apex distribution

I may have a crazy directory structure, but this is what I understood from the Oracle standards:

su
mkdir -p /u01/app/oracle/product/4.0.2/
chmod 777 /u01/app/oracle/product/4.0.2/
unzip apex_4.0.2.zip -d /u01/app/oracle/product/4.0.2/

Loading the mod_plsql module

Loading the mod_plsql module into the (apache) server is as easy as editing a text-file. The key off-course is what text to edit:

If you used the directory I proposed in step VI, then the httpd.conf should be edited as follows:

su
export OHS=/u01/app/oracle/product/11.1.1/ofm
gedit $OHS/Oracle_WT1/instances/instance1/config/OHS/ohs1/httpd.conf

Add these lines at the end of the file:

LoadModule plsql_module "${ORACLE_HOME}/ohs/modules/mod_plsql.so"
include /u01/app/oracle/product/11.1.1/ofm/Oracle_WT1/ohs/conf/mod_plsql/*.conf

Save the file.

Configure the dads.conf

The dads.conf holds the information apex uses to log in into the database. Open it and edit:

gedit /u01/app/oracle/product/11.1.1/ofm/Oracle_WT1/ohs/conf/mod_plsql/dads.conf

The file should look like this:

# ============================================================================
#                     mod_plsql DAD Configuration File
# ============================================================================
# 1. Please refer to dads.README for a description of this file
# ============================================================================ 

# Note: This file should typically be included in your plsql.conf file with
# the "include" directive.

# Hint: You can look at some sample DADs in the dads.README file

# ============================================================================ 

Alias   /i/               "/u01/app/oracle/product/4.0.2/apex/images/"
AddType text/xml          xbl
AddType text/x-component  htc

<Location /apex>
 Order                          deny,allow
 PlsqlDocumentPath              docs
 AllowOverride                  None
 PlsqlDocumentProcedure         wwv_flow_file_manager.process_download
 PlsqlDatabaseConnectString     localhost:1521:XE ServiceNameFormat
 PlsqlNLSLanguage               AMERICAN_AMERICA.AL32UTF8
 PlsqlAuthenticationMode        Basic
 SetHandler                     pls_handler
 PlsqlDocumentTablename         wwv_flow_file_objects$
 PlsqlDatabaseUsername          APEX_PUBLIC_USER
 PlsqlDefaultPage               apex
 PlsqlDatabasePassword          parsingschemapasswd
 PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize
 Allow from all
</Location>

Please have a look and correct the following directives:

  • PlsqlDatabaseConnectString
  • PlsqlDatabasePassword (pay attention. case is important)

Now we must (re-)start the OHS:

su
ln -s /u01/app/oracle/product/11.1.1/ofm/Oracle_WT1/instances/instance1/bin/opmnctl /bin/opmnctl
exit
opmnctl stopall
opmnctl startall

This piece of script also creates a symbolic link in /bin/ to the OHS management program “OPMNCTL”. We will use this later on to make OHS start automatically.

Automating the startup process

We’d like the OHS to start automatically when the server boots.

Create a file /etc/init.d/ohs

#!/bin/bash
#
# chkconfig: - 91 35
# description: Starts and stops Oracle HTTP Server. 

# Source function library.
. /etc/init.d/functions

# Source networking configuration.
. /etc/sysconfig/network

# Check that networking is up.
[ ${NETWORKING} = "no" ] && exit 0

prog=$"Oracle HTTP server"

start() {
    echo -n $"Starting $prog: "
    /bin/opmnctl startall>nul:
}

stop() {
    echo -n $"Shutting down $prog: "
    /bin/opmnctl stopall>nul:
}
status() {
    /bin/opmnctl status
}

# See how we were called.
case "$1" in
  start)
	start
	;;
  stop)
	stop
	;;
  restart|reload)
	stop
        echo ""
	start
        echo ""
	;;
  status)
	status
	;;
  *)
	echo $"Usage: $0 {start|stop|restart|status}"
	exit 1
esac

Now the server is controllable by:

  • /etc/init.d/ohs start
  • /etc/init.d/ohs stop
  • /etc/init.d/ohs restart
  • /etc/init.d/ohs status

This can then be easily be automated:

su
/sbin/chkconfig --add ohs
/sbin/chkconfig ohs on

Et voila! we’re be able to restart the box. OHS should now start automatically.

Obfuscating the password in dads.conf

Plain text passwords in plain textfiles is obviously not a good idea. The password in dads.conf can be obfuscated as follows:

This is post 6 in my series about a fully freeware APEX installation:

Information has been taken from

Shut down EPG

The EPG is still running. To shut it down, open SQL*plus:conn

conn system
execute dbms_xdb.sethttpport(0);
exit

Prerequisits

Make sure your system has enough memory (2 Gigs or more)

We need to alter the system a bit:

Oracle

Start SQL*plus as system

alter system set PROCESSES=500 scope=SPFILE;
alter system set open_cursors=500 scope=SPFILE;
alter system register;

Kernel Parameters

Open up /etc/security/limits.conf in gedit.

Add these lines just before “# End of file”

# tbv oracle soa suite 11g
*    hard    nofile  4096
*    soft    nofile  4096

Install packages

And we need to install some extra packages:

su
yum install compat-libstdc++-33
yum install elfutils-libelf
yum install elfutils-libelf-devel
yum install gcc-c++
yum install glibc-devel
yum install libaio-devel
yum install libstdc++-devel
yum install sysstat

Instaling the WebTier

Unzip the ofm_webtier zip into a separate folder:
Make sure you’re not root. The installer will stop.

su
unzip ofm_webtier_linux_11.1.1.2.0_64_disk1_1of1.zip -d webtier
cd webtier/Disk1
./runInstaller

The installer will start:

Click “OK”, a confirmation dialog appears:

Execute the proposed script in a separate terminal:

su
/home/apex/oraInventory/createCentralInventory.sh

Now the installer will start:

Click “Next”

Choose “Install and Configure” and click “Next”

All should be Okay since we did the prerequisites earlier. Click “Next”

I used “/u01/app/oracle/product/11.1.1/ofm” (created it first in a separate rooted terminal).

su
mkdir -p /u01/app/oracle/product/11.1.1/ofm
chmod 777 /u01/app/oracle/product/11.1.1/ofm

Click “Next”.

Configure Components

We’re only installing the HTTP server. Uncheck all  other checkboxes (including the “Associate Components”) and click “Next”.

Keep the defaults: “Next”

Keep the defaults: “Next”

Fill in your credentials or keep everything empty (and uncheck the checkbox)

Click “Install”

After the installer completes:

Click “Next”

Note the URL’s mentioned

  • Oracle HTTP server URL: http://localhost:7777
  • Oracle HTTP server SSL URL: http://localhost:4443

Click “Finish”

Now we need to configure the HTTP-Server. This I’ll cover in the next post.

This is part 3 of my series on a fully freeware APEX environment on CentOS:

This post consists of 3 parts:

  • Install Java
  • Glassfish preparations
  • Install GlassFish
  • Configure Glassfish (part 3a)

Information has been taken from:

Here we go…

Install Java

CentOS allready comes with java pre-installed:

/usr/bin/java -> /etc/alternatives/java -> /usr/lib/jvm/jre-1.6.0-openjdk.x86_64/bin/java

Glassfish however requires JDK 6:

su -c "yum install java-1.6.0-openjdk"
su -c "yum install java-1.6.0-openjdk-devel"

There might be a message at step openjdk that the package is allready installed.

After install we need to figure out the JAVA_HOME. You can do this by issuing:

which java

And with the results repeatedly issue

ls -l /path/returned/by/the/previous/command

In my case I ended up here:

/usr/lib/jvm/jre-1.6.0-openjdk.x86_64/bin/java

this will be my JAVA_HOME.

Glassfish preparations

open up /etc/bashrc as su:

su
gedit etc/bashrc

add this to the end of the file and save the file:

#GLASSFISH
export JAVA_HOME=/usr/lib/jvm/jre-1.6.0-openjdk.x86_64/bin/java
export GLASSFISH_HOME=$ORACLE_BASE/product/3/glassfish
export PATH=$PATH:$JAVA_HOME/bin:$GLASSFISH_HOME/bin

Of course you need to change line 2 with what you found to be your JAVA_HOME, but I reckon that it’ll be the same as what I found.

Start SQL*plus. We need to shut down the EPG:

execute dbms_xdb.sethttpport(0)
exit

Exit the terminal to get your changes applied.

Install Glassfish

Open a terminal again

su
cd /u01/app/oracle/product/3/glassfish/
wget http://dlc.sun.com.edgesuite.net/glassfish/v3/release/glassfish-v3-unix.sh
sh ./glassfish-v3-unix.sh

This downloads the Glassfish 3 installer and executes it.

glassfisinst-01-welcome

Click “Next”

glassfishinst-02-license

Accept the terms.. “Next”

glassfishinst-03-installdir

specify the directory: /u01/app/oracle/product/3/glassfish.. “Next”

glassfishinst-04-adminsettings

We write down the values here for later reference:

  • 4848  admin port
  • 8080  http port
  • admin  username
  • secret  password

Click “Next”.

glassfishinst-05-updateconfig

Make the changes that apply for your network. I don’t have a proxy in mine. So “Next” suffices…

glassfishinst-06-JDKselection

I didn’t change anything here.. “Next”

glassfishinst-07-ready2install

Nothing to do here: “Install”

glassfishinst-08-installing

Now we wait…

glassfishinst-09-prodreg

I skipped this

glassfishinst-10-summary

“Exit”

Ready.. Now we must configure GlassFish.

In this post we’ll configure the GlassFish 3 server that we installed in the previous post.

Information has been aggregated from different blogs:

Apex & Apex listener download

Apex and the listener should be downloaded now.

Now we start the admin console and

mkdir -p $ORACLE_BASE/product/1.1.2/apxlistener
mkdir -p $ORACLE_BASE/product/4.0.2
unzip apex_listener.1.1.2.131.15.23.zip -d $ORACLE_BASE/product/1.1.2/apxlistener
unzip apex_4.0.2.zip -d $ORACLE_BASE/product/4.0.2

Running Glassfish as a service

A script must be created in /etc/init.d

gedit /etc/init.d/glassfish

Paste this into the new file:

#!/bin/bash
# description: Glassfish Start Stop Restart
# processname: glassfish
# chkconfig: 244 20 80
export JAVA_HOME=/usr/lib/jvm/jre-1.6.0-openjdk.x86_64/bin/java
export PATH=$JAVA_HOME/bin:$PATH
export GLASSFISH_HOME=/u01/app/oracle/product/3/glassfish

case $1 in
start)
sh $GLASSFISH_HOME/bin/asadmin start-domain domain1
;;
stop)
sh $GLASSFISH_HOME/bin/asadmin stop-domain domain1
;;
restart)
sh $GLASSFISH_HOME/bin/asadmin stop-domain domain1
sh $GLASSFISH_HOME/bin/asadmin start-domain domain1
;;
esac
exit 0

Now, make the script executable and add it to our chkconfig so it starts at boot.

chmod 755 /etc/init.d/glassfish
chkconfig --add glassfish
chkconfig glassfish on
/etc/init.d/glassfish start

Copy Oracle Apex Images

Apex assumes the /i/ folder on the http server to contain the apex images. We copy the files like:

$GLASSFISH_HOME/bin/asadmin start-admin
cp -r $ORACLE_BASE/product/4.0.2/apex/images $GLASSFISH_HOME/glassfish/domains/domain1/docroot/i/

Glassfish configuration

Now we open the browser at this address: http://localhost:4848/.
The server will ask us to sign in. Use the username “admin” and the password you supplied when installing Glassfish.

  1. Go to “Configuration – Security” in the tree at your left hand.
  2. Check the checkbox at “Default Principal To Role Mapping”
  3. Click the “Save” button at the top of the screen.

Now we’re going to add some users:

  1. Expand the “Configuration – Security – Realms” node in the tree
  2. Select the “file” realm
  3. Click the “Manage Users” button
  4. Click “New..”
  5. Create
    1. User: “Aminlistener”
    2. Group List: “Admin”
    3. Password: “verysecret”
    4. Click “OK”
  6. Repeat step 5 for:
    1. User: “managerlistener”
    2. Group List: “Manager”
    3. Click “OK”

Install the apax.war listener:

  1. Click the “Applications”-node in the tree
  2. Click the “Deploy” button
  3. Select “Packaged File to Be Uploaded to the Server”
  4. Click the “Browse” button and navigate to:
    /u01/app/oracle/product/1.1.2/apxlistener/apex.war
  5. On the “Deploy Applications or Modules”  specify:
    1. Type: Web Application
    2. Content Root: apex
    3. Application Name: apex
    4. Status: Enabled (checkbox: on)
    5. Description: Application Express Listener
    6. Click “OK”

Configure the Listener

  1. Open a new browser (tab/window): http://localhost:8080/apex/listenerConfigure
  2. See the section on Configuring the apex listener

That’s it.

This is post 3 in my series about a fully freeware CentOS Apex environment

Configure the APEX Listener

Your listener is deployed, but not yet configured. Go to http://localhost:8080/apex/listenerConfigure

We must set parameters in three tabs:

  1. Connection
    • Username: apex_public_user
    • Password: the password as set earlier for this user
    • Connection Type: Basic
    • Hostname: localhost
    • Port: 1521
    • SID: XE
  2. Security
    • Database Validation Function: wwv_flow_epg_include_modules.authorize
  3. Caching
    • Procedure Names: wwv_flow_file*
    • Maximum entries: 500

listener_config_01 listener_config_02 listener_config_03

Click the “Apply” button after you made all changes. The page remembers the settings when you switch between tabs.

As you see I’m configuring from my Windows host towards the Linux guest (running VirtualBox).

Apex is now ready. Have a look at these links:

Creating a workspace:

You can create a workspace by going to : http://localhost:8080/apex

apex_internal_workspace_01

The ADMIN@INTERNAL password is the same as what you used for the SYS and SYSTEM “users” when installing Oracle XE.

That’s it.