Recently I’ve been installing the OATOA (Oracle APEX on Tomcat with ORDS behind Apache) stack a number of times on both Oracle Linux 7 and CentOS7.

In the past this was no problem, Tomcat came up and a request to http://myserver:8080/ords/ gave me the expected APEX login screen.

However since Oracle Linux 7 and CentOS7, it seemed that Tomcat started before it could find the Oracle database. A simple restart of the tomcat service would do the trick. On my virtual machine (recently switched from VirtualBox to parallels btw) meant for doing presentations no problem at all.

systemctl restart tomcat

Now I’m playing around with “droplets” at DigitalOcean, just another name for virtual servers in my opinion, but they have great features at a reasonable price. Take a look: https://m.do.co/c/187c0416a2b3.

But my droplets showed the same behaviour as my parallels virtual machine. I first had to restart tomcat to get a working system.

You all should know that I’m not a linux guru (really I’m not), so broke my brains about this for a looooong time.

Yesterday I finally managed to get this working. The idea is to make the tomcat start-script wait until it can see “something” on port 1521 (the db listener) and only then continue to start.

Open the file /usr/libexec/tomcat/server

nano /usr/libexec/tomcat/server

now add some lines of code _before_ the line that starts with MAIN_CLASS like this:

#!/bin/bash
. /usr/libexec/tomcat/preamble
# .=.=.=.=.= START Make tomcat wait on oracle .=.=.=.=.=
i=1
while netstat -lnt | awk ‘$4 ~ /:1521$/ {exit 1}’0
do
  sleep 10
  let i+=1
  if [ “$i” -gt “5” ]
  then
    break    #Abandon the loop.
  fi
done
# give Oracle some slack to also start the database
sleep 10
# .=.=.=.=.= END Make tomcat wait on oracle .=.=.=.=.=

MAIN_CLASS=org.apache.catalina.startup.Bootstrap

add the obvious lines to your script.

What is happening?

The line “netstat -lnt | awk …” tries to find the string “:1521” in the result of a “netstat -lnt” command. This would indicate that something is listening on that port. In our case it will be the Oracle Listener.

If it does not find an open port 1521, it will sleep for 10 seconds, increment a counter (“i”) by 1 and do the loop again. The loop will loop a maximum of five times to prevent an endless loop.

After the loop completes we give the Oracle database another 10 seconds to get started.

That should do the trick. It does for me (I use the same code at digitalocean as well as my parallels virtual linux server.

I’m sure linux must have some system in place that could do the trick as well (make one service dependable on the other) but I haven’t found an easy one to use. If someone could help me out here, feel free to leave a comment.

Regards,

Richard

Inspired by Dan McGhan, today I launched a new meetup: ORCLAPEX-NL

Everybody speaking enthousiastic about Apex and speaking Dutch is welcome.

Eventhough the home-location is Tilburg, I think most of our meetups will end up to be more in the center of the country.

ORCLAPEX-NL

Tilburg, NL
8 Apex fanatics

Deze groep is voor iedereen in Nederland (en België) die een passie heeft voor Oracle Application Express (APEX) en geïnteresseerd in bijeenkomsten met gelijkgestemden. Zo kun…

Next Meetup

Pizza night

Thursday, Sep 18, 2014, 7:30 PM
5 Attending

Check out this Meetup Group →

Wow, I broke my brains over this for the last three days.

I installed the apex listener as mentioned in the manual. It mentions you can use Java 6 Update 20 JDK or later.

However, what they mean is that you should use Java 6 JDK, and you can use update 20 or later.

What I did in my stupidity is to use Java 7. That’s later then Java 6 upd 20 right?

wrong!

If you use Java 7 your listener might work (which was the case in my situation) but connecting to it using SQL Developer is a “no go” 🙁

Be warned.

regards, Richard

Got it from Andy’s Blog:

Within ApEx PL/SQL Processes, regions and items you can use the bind variable syntax (:PX_MY_ITEM) both to read and set the value of that item held in session state. Like so:

:PX_MY_ITEM := 'wibble';
l_my_local_variable := :PX_MY_ITEM;

However, in stored PL/SQL packages, procedures and functions you cannot use the bind variable syntax. Rather, you must use the v(‘PX_MY_ITEM’) syntax. But this is read only.

So how do you set the value held in session state for a given page item from within a stored package, procedure or function?

The answer lies in the set_session_state procedure found in the APEX_UTIL package. E.g.

APEX_UTIL.set_session_state(
        p_name  => 'PX_MY_ITEM'
      , p_value => 'wibble');

In my ongoing quest for freeware in combination with Oracle Apex I gave Oracle Unbreakable linux another try when I heard that you _are_ able to update/upgrade it using yum, without a support identifier.

First we install Oracle Unbreakable Linux here

I opted for the 64 bit version and installed a minimal version in my VirtualBox environment.

  • 2048 Mb memory
  • 32 Gb Harddisk
  • 2 CPU’s
  • 2 Network cards
    • Adapter 1: “NAT”
    • Adapter 2: “Host Only”

When the installer asks for the computername you should make the networkcards connect automatically:

On the bottom left you can select “Configure Network”

Select each of the network cards (eth0 and eth1) and click “Edit”

The trick is in selecting the “Connect automatically” checkbox. check it for both cards.

Continue the rest of the installer normally. Choose the “Minumal” install

After the installation log in as root.

mount /dev/dvd /mnt
cd /mnt/Packages
rpm -ivh wget #(and press the tab-key)
rpm -ivh nano #(and press the tab-key)
cd /etc/yum.repos.d
wget http://public-yum.oracle.com/public-yum-ol6.repo
nano /etc/yum.conf

We now need to enable yum by adding an extra line to the configuration file:

enabled=1

That should do the trick. Test it out by issueing

yum update

Patrick Wolf blogged about this, but I’d like to have it available here as well:

If you get the error Workspace “[workspace name]” is inactive. Contact your administrator. when you try to login into your workspace or when you run an application, then you should run the following script to fix the status of your workspaces.

  • Connect as SYS, SYSTEM, APEX_040100 or any user who has the APEX_ADMINISTRATOR_ROLE role
  • Run the following PL/SQL script:
    begin
      for l_workspace in ( select short_name
                           from   apex_040100.wwv_flow_companies
                           where  account_status='AVAILABLE' )
      loop
        apex_040100.apex_instance_admin.enable_workspace(l_workspace.short_name);
      end loop;
      commit;
    end;

Note: This problem only occurs for workspaces which have been created on the command line with the apex_instance_admin.add_workspace procedure. Workspaces which have been created through the UI should be fine.

The problem has been filed as bug# 13769526 and will get fixed in the next version of APEX. Thanks to Dimitri who notified us about the problem.

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.

taken from: http://stewstools.wordpress.com/2010/11/04/mystery-interactive-report-broken-for-end-users/ GREAT story 🙂

<COPY>

A friend dropped me a line saying her Apex app’s Interactive Report wasn’t working for her users anymore and she asked if I knew what could cause it. She said it worked for her in the Apex development environment, but not outside. I talked her into giving me an account on her Apex workspace and started digging through it.

For me in the developer environment, it wouldn’t do any of the JSON updates, like search for text, filter, change columns, sort, etc. – all the cool things we love about IRs. I’d seen this before, but it’d been so long I’d forgotten. I finally dug into the page template and noticed that there wasn’t much inclusion of external CSS going on and when I added that in, the report worked. So I sent her that as “fixed!” I couldn’t figure out how this application could have worked before, but had stopped!

Unfortunately, she wrote back saying that didn’t do it for someone running outside the development environment! Aaagh!

But I love a good challenge, so I dug back in. I got another session going in another browser so it was outside Apex’s environment. I noticed a difference between the two user sessions, that in the Development Environment, it showed my username on the top of the page, but not when I wasn’t. That was the hint I needed! It reminded me that the Interactive Reports store your queries by the APP_USER name into the database. No user name, no interactivity!

It turned out that this was a new application, she was using a custom authentication method that was new to her and she wasn’t setting the APP_USER value!

I’d used this same custom authentication method and found I needed to call

APEX_CUSTOM_AUTH.SET_USER(p_user => ‘some custom value’);

in the Post section of the authentication method.

Once that was set, everything worked great.

It was only after I told my friend the solution that she said that this was a new application she was building. Oops! If she’d said that up front, I might have figured out what was wrong sooner! :-/

</COPY>

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 🙂