Friday, February 26, 2010

APEX Listener EA2 with native Excel Upload

Kris Rice blogged about the new version of the APEX Listener here and here. The APEX Listener will become the preferred way of connecting to APEX (before Apache and the Embedded PL/SQL Gateway). If you are not familiar with the APEX Listener, you find more information here.

To install the APEX Listener EA2 (0.10.56.22.08) on my Oracle Linux VM I followed these steps:

  • Download Java SE JDK (before I used the JRE, but Kris told me the debugger doesn't work with that as you need the JDK for that part of the code)
    As a side note; now that Oracle and Sun are together, that website already has the "Oracle look". I wasn't expecting that so fast, but it's nice. The theme looks very similar to the APEX 4.0 Theme 1.
  • Become root: su
  • Go to the path you want java to install: cd /usr/java
  • Copy the file you downloaded in that directory
  • Change the permission of the file you downloaded to be executable: chmod a+x jdk-6u18-linux-i586-rpm.bin
  • Start the installation process: ./jdk-6u18-linux-i586-rpm.bin
    If that is done you should see a screen like this and a directory jdk1.6.0_18 should exist:

  • You find a complete guide of installing Java here.
  • Next you need to download and install the APEX Listener
  • The APEX Listener needs a webserver, depending the webserver you need to follow different steps. You find a complete guide how to install the webserver and the APEX Listener here.
  • I already had Apache Tomcat installed on my VM, so I went with that and just followed the installation notes.
  • Basically copy a war file to the webserver and change the config file
  • I was interested to test the new Excel upload feature, to get to work I added following line to the config file (apache-tomcat/temp/apex/apex-config.xml)
    true
  • Finally run APEX via the port of the Listener and create a new page with a File Browse item, a button and a report that queries the collection.
  • That's it!

With the latest release of the APEX Listener you query the collection like this:
select *
from apex_collections
where collection_name = 'P1_EXCEL_FILE'
P1_EXCEL_FILE is the item name of the File Browse Item. The other thing to remember is to call your button XLS2COLLECTION or have that as the request value.

4 comments:

Kris Rice said...

Good overview!

vll said...

Thanks for sharing! One question - and sorry, I'm new to apex. Is it possible to query in a similar way Excel spreadsheet that is stored in BLOB field in some table? Not "Browse" element on the screen, but just a binary stored data? Thanks a lot!

Anonymous said...

Hi,
In the application code where it was called the XLS2COLLECTION

Jaysheel Ramjee said...

Hi Dimitri,

Thanks for this tip. I am have tried this and the problem I am having is after selecting the file and clicking the XLS2COLLECTION button, the page just goes blank and freezes. If I refresh there is no change. I have to manually 'Go back' in the browser to view the items in the apex_collections (via a report).

Do have any idea, what's going on and how I can resolve this?

Thanks,
Jay