Friday, August 27, 2010

APEX Connection Frustration

I've just spent a few hours going round in circles trying to get connection to an APEX application. I developed an application at home and wanted to implement it at work. I downloaded and installed Oracle XE on my work PC, then installed APEX 4.01. This went fine, it installed without a hitch. I exported the Apex application and the data from my home PC, then copied the files and imported the APEX app. The data didn't import - it failed with an invalid header saying it wasn't an export file - I think because I exported from 11g and XE is 10g. Since there were only 3000 rows of data in one table I simply spooled it out to a csv file and used SQLLDR to load it after re-creating the table in the XE database.

I opened the browser, pointed it at the App and it worked fine. After running through a few tests I decided it was OK for others to use. This was where the fun began.

No-one else could connect to it.

I went through the doco, found that I could change the http port using

exec dbms_xdb.sethttpport(8090);

so tried that, thinking that perhaps I was using 8080 for something else.

That didn't work.

Going through the doco and some forums, I found that XE doesn't allow remote connections by default, so I needed another command:

EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);

Tried again, still didn't work.

I tried resetting the port again with the same result - I could connect to the APEX web page from my PC, but not from any others.

I could ping my IP address, could ping the hostname (after updating /etc/hosts on a server), but could not open the web page, it kept timing out.

I decided to ignore the APEX side and tried to tnsping: I got a "TNS-12535 TNS: Operation Timed Out".

Aha, now I was getting somewhere. I then tried a TELNET xx.xx.xx.xx 1521 - timed out.

So it wasn't APEX, it was the network.

The solution was to open the ports 1521 and 8080 in the firewall on my PC. Once that was done all the other PCs could connect.