OraNA :: APEX

Word count in Pages (OSX)

Dimitri Gielis — Fri, 12 Mar 2010 01:03:51 PST

Sometimes it's hard to find something if you don't use it that much.

I was searching for a Word and Character count in Pages (OSX). Pages is like Word, but than from Apple. Anyway, in Pages most nice "things" are under the Inspector icon.

The first icon (Document) and the third tab in there, give you what you need. Information about your document, like the number of words you used, number of characters etc.

The better you know something, the faster you can get what you want, it applies to everything ...

Automatic linebreak if word breaks out of table element

Tobias Arnhold — Thu, 11 Mar 2010 13:36:56 PST

I found a great source how to prevent word break outs in <th> and <td> elements:
Peter Bromberg's: FIREFOX / IE Word-Wrap, Word-Break, TABLES FIX
How does the break out looks like:

CSS code to prevent this issue:


.prevent_breakout
{
width: 250px;
white-space: pre-wrap; /* css-3 */
white-space: -moz-pre-wrap; /* Mozilla, since 1999 */
white-space: -pre-wrap; /* Opera 4-6 */
white-space: -o-pre-wrap; /* Opera 7 */
word-wrap: break-word; /* Internet Explorer 5.5+ */
}

With javascript it could look like this:


<script type="text/javascript">
 function set_lb(v_val, v_max_width){
  var v_browser=navigator.appName; 
  // ...
  if (v_browser=="Microsoft Internet Explorer")  
  {
    $x(v_val).style.width = v_max_width; // set width
    $x(v_val).style.wordWrap   = 'break-word';  // Internet Explorer 5.5+
  }else
  {
    $x(v_val).style.width = v_max_width; // set width
    $x(v_val).style.whiteSpace = 'pre-wrap'; // css-3
    $x(v_val).style.whiteSpace = '-pre-wrap';  // Opera 4-6
    $x(v_val).style.whiteSpace = '-o-pre-wrap';  // Opera 7
    $x(v_val).style.wordWrap   = 'break-word';  // Internet Explorer 5.5+
    $x(v_val).style.whiteSpace = '-moz-pre-wrap'; // Mozilla, since 1999
  }
</script>

Thanks Peter for this great hint!

Tabular Form - Preserve Changes

Denes Kubicek — Thu, 11 Mar 2010 07:58:02 PST

In our last training (08.03-10.03.2010) there was an interesting question on how to preserve the changes made in a tabular form while paginating through it. The requirement was also to save all the changes made independant of the visible set of rows. This problem can be solved using apex collections. This page in my Demo Application holds a working example including the full description of the code.

Things you have in common with Larry

david.peake@oracle.com (David Peake) — Tue, 09 Mar 2010 08:17:00 PST

Larry and I have something in common -- We both like sailing!
I prefer catamarans to trimarans, but hey all those design experts were proven right - the BMW ORACLE Racing Trimaran [BOR90]is arguably the fastest sailing boat ever built. I did a lot of sailing back in Australia where I owned a small cat. In fact a good mate of mine Kevin Crowley of Pacific DBMS (free plug least I could do ... and they offer APEX Consulting services as well) still has it in his back yard.

Now something else Larry and I have in common - We both like Application Express.
Larry has been a big supporter of Application Express since the beginning.
Sergio Leunissen, former APEX PM and now in the Linux group, developed an APEX application for the BMW ORACLE Racing team when they first started out years ago.
Again with the 33rd America's Cup challenge the team used APEX together with Data Mining to support the team.

Listen to this podcast from Ian "Fresh" Burns, design coordinator for the team on how Oracle technology helped the team to win the America's Cup: http://feedproxy.google.com/~r/OracleDatabaseInsider/~5/nussC-gxZFQ/8576609_Ian_Burns_030810.mp3

Cheers,
David

Weird IE behaviour with JS function String.charAt(Index)

Tobias Arnhold — Wed, 10 Mar 2010 13:33:18 PST

I had a real pain with the MS Internet Explorer and the javascript function String.charAt(Index)

I wanted to update a string and checked each value. IE was able to give just an empty value back. But it was not checkable!

if ((v_browser=="Microsoft Internet Explorer")&& (v_string.charAt(x)=='')&& (y+1==x) && (v_string.charAt(x+1)=='<'||v_string.charAt(x+2)=='<'))

Even the length was 1 and not 0!
Finally I tried this function String.charCodeAt(Index) with an alert box and voilà two values where prompted: 13 and 10

13 and 10 wait... LINEBREAK!

HELL why does it do such things...

Now I could check it!

if ((v_browser=="Microsoft Internet Explorer")&&(v_check_unicode=='13'||v_check_unicode=='10'||v_check_unicode=='NaN'))

My script works and I feel released.
Now I have to watch the end of Real against Lyon.. :D

Column header groups in APEX report

Tobias Arnhold — Wed, 10 Mar 2010 11:30:55 PST

Did you ever want a report header grouped like this:

Just add some html code into your report template > column headings > Before Column Heading:

Important to know:
- Changes like this have effect on every report you use with that report template
- Make a copy of your existing report template and use the new one instead

In my example I used 8 columns and three of them should have a group column above it. Use colspan="3" to stripe it over 3 columns.

Here the generated HTML code:

For APEX Interactive Reports watch there:
Dimitri Gielis: Group Headings in an Interactive Report (APEX)
And there:
Martin Giffy D'Souza: Column Groups in APEX Interactive Reports

My Interview with Mike Riley

Scott — Mon, 08 Mar 2010 07:34:08 PST

Recently, I sat down with ODTUG President Mike Riley and answered a few questions about the upcoming ODTUG Kaleidoscope Conference which will be held in Washington, DC this year. (OK, we didn't actually sit down together, but I answered his questions nevertheless!)

Have a look at my interview here.

Oracle APEX 4.0 Early Adopter Blog Coverage – Part 3

Patrick Wolf — Fri, 05 Mar 2010 02:05:13 PST

Since my last Oracle APEX 4.0 blog coverage – Part 2 posting, new articles have been posted about our early adopter release 2. Have fun reading them! APEX 4.0 EA2 – Websheets by Dimitri Gielis [Oracle APEX 4.0] Text Field with autocomplete by Whitehorses APEX 4.0 EA2: Improved debugging, my new favorite feature by Mark Lancaster [Oracle APEX 4.0] [...]

Oracle APEX 4.0 – New Features in Early Adopter 2

Patrick Wolf — Thu, 04 Mar 2010 03:04:15 PST

It took a little bit longer as expected, but the new feature list of Oracle APEX 4.0 Early Adopter 2 is now online (link is at the end of the posting). The list contains the EA1 features as well. Hopefully we didn’t miss any new feature, there are to many to count. One of the [...]

APEX 4.0 EA2 - Websheets

Dimitri Gielis — Thu, 04 Mar 2010 03:27:05 PST

In APEX 4.0 EA2 the Websheet functionality is enabled. Websheets allow you to share information with others in a very quick, user friendly and secure way.

Let's have a look at an example. If you go to Application Builder in APEX 4.0 there is a new type there called "Websheet Applications".

We create a new Websheet Application and give it a name and possibly some content that will appear on the home page. You can type the content with the WYSIWYG editor (CK Editor) by clicking on the arrow button.

Before it creates the Websheets, the wizard gives a summary page and that is it.
Websheets are even simpler to create than a normal APEX (database) application.

You can compare Websheets with a preconfigured APEX application. A whole Framework is build for you with a lot of features out-of-the-box. You can just use all that.

When you run the Websheet from the App Builder you come into "Websheet-land". As a default Authentication mechanism it uses the Application Express Account. But you can change that by going into the Websheet Properties to Public Access, Single Sign On, LDAP or Custom.

When logged in, you come into the Websheet Framework. As we have Websheet Development Access, we can change the Websheet. In the top menu and on the right hand side you see what you can do. E.g. you can add pages, sections, data grids etc. There's a lot to explore there!

In the different sections of the page you also have Edit links, which allow you to easily change the text, all with a (WYSIWYG) html editor.

I see two big parts of Websheets; sharing information and data. The information is done through using Pages, sections, tags, files etc. just like you do in a Wiki/CMS. But where it outperforms all the typical wiki or content management systems, is with the data part!

One of the killer features of Websheets are the Data Grids. If you go to Data > Data Grids and create a new Data Grid, you have the choice to start from scratch or from Excel/Text with copy/paste. That allows you to create "a table" on the fly which holds your data (actually it's a record in the Websheet repository). This Data Grid look very similar to an Interactive Report (see Actions button), but it allows to do inline editing and a lot more (see Manage button)!

Websheets are too big to discuss in one blog post. There are so many areas which could be covered. There's the wiki part, the data part, the administration (security, dashboards), ...

Maybe one last thing... look at the url of a Websheet (login with end/end)... it doesn't start with f?p... it starts with ws?p ;-)

DOCX Part II: How to Index Document Content With Oracle Text

Håvard Kristiansen — Tue, 02 Mar 2010 03:35:43 PST

Here I will demonstrate how to index content of CLOB data using Oracle Text. Other than the database objects used in the example, the post addresses the use of Oracle Text in a general way, and will work for any CLOB column. There is no ground breaking code here, but it completes the example on how to handle Microsoft Open Office XML Format (DOCX) documents. The examples and content here are based on my previous post.

The code here is tested with Oracle database release 11.1.0.6, but should work with releases down to 10g. When I say "tested", remember I am a developer, so it basically means it does compile and it did give the expected result on one run trough...

See it in Action
I have included a search page in my demo application. Upload a DOCX document, and try it for yourself: Go to demo application.

About Oracle Text
Oracle Text (formerly Oracle interMedia) is Oracles full-text retrieval technology, and part of Standard, Enterprise and even XE editions of the database. See Oracle Text on oracle.com for more information.

In my case I will use it to index text content stored in a CLOB column, and utilize it's search capabilities to enable users to retrieve relevant results based on search phrases.

Indexing CLOBs
Or BLOBs, or BFILEs, for that matter. It is very easy to get up and running with a text index. The following code is all that is needed to get a text index up and running against the TEXT_CONTENT column of the SAMPLES_DOCX table:

create index samples_docx_ctx on samples_docx
(text_content)
indextype is ctxsys.context
/

That's it! If you want to get fancy, there are a number of options and ways to enhance the indexing, look at the documentation for more information on the subject (lexers, sections, stopword lists, etc.).

Searching with Oracle Text
With the text index in place, the next step is to use the index to retrieve search results. In my example I will use a technique called "Progressive Relaxation" as described by Roger Ford here. It uses a query template to implement a progressive relaxation of the search tokens.

The following script creates a package to handle and use the new text index:

create or replace package samples_docx_search_p
as
   -- result types
   type t_result_rec is record (
      score         number,
      filename      samples_docx.filename%type,
      snippet       varchar2(4000));
   type t_result_tab is table of t_result_rec;
   -- synchronize search index
   procedure sync_search_index (
      p_ctx_index_name     in          varchar2 default 'SAMPLES_DOCX_CTX'
   );
   -- search docx content
   function search_text (
      p_tokens    in          varchar2
   ) return t_result_tab pipelined;
end;
/

create or replace package body samples_docx_search_p
as
   -- synchronize search index
   procedure sync_search_index (
      p_ctx_index_name     in          varchar2 default 'SAMPLES_DOCX_CTX'
   ) is
   begin
      ctx_ddl.sync_index(p_ctx_index_name, '2M');
   end;
   -- search docx content
   function search_text (
      p_tokens    in          varchar2
   ) return t_result_tab pipelined
   as 
      l_max_rows         integer := 10;
      l_counter          integer := 0;
      l_ret_rec          t_result_rec;
      l_tokens           varchar2(4000) := lower(p_tokens); 
      l_query_template   varchar2(32000):='<query>'
                              ||chr(10)||'   <textquery> heregoesthetokens'
                              ||chr(10)||'     <progression>'
                              ||chr(10)||'       <seq><rewrite>transform((TOKENS, "{", "}", " "))</rewrite></seq>'
                              ||chr(10)||'       <seq><rewrite>transform((TOKENS, "{", "}", "AND"))</rewrite>/seq>'
                              ||chr(10)||'       <seq><rewrite>transform((TOKENS, "?{", "}", "AND"))</rewrite></seq>'
                              ||chr(10)||'       <seq><rewrite>transform((TOKENS, "{", "}", "OR"))</rewrite></seq>'
                              ||chr(10)||'       <seq><rewrite>transform((TOKENS, "?{", "}", "OR"))</rewrite></seq>'
                              ||chr(10)||'     </progression>'
                              ||chr(10)||'   </textquery>'
                              ||chr(10)||'</query>';
      l_query            varchar2(32000);
   begin
      if l_tokens is null
      then
         return;
      end if;
      -- create query temlpate based on tokens
      l_query_template := replace(l_query_template, 'heregoesthetokens', l_tokens);
      -- restructure tokens for use with snippet
      l_tokens := replace(l_tokens, ' ', ' | ');
      for c in (select rowid
                     , filename 
                  from samples_docx 
                 where contains (text_content, l_query_template, 1) > 0)
      loop
         -- crude custom score, just listing the results as they come
         l_ret_rec.score := l_counter;
         l_ret_rec.filename := c.filename;
         -- create snippet (with tokenbased highlight) of content to return to the user
         ctx_doc.set_key_type('ROWID');
         l_ret_rec.snippet :=  ctx_doc.snippet('SAMPLES_DOCX_CTX', c.rowid, l_tokens, '<b>', '</b>', false);
         l_counter := l_counter + 1;
         -- return row
         pipe row (l_ret_rec);
         -- exit when max number of rows exceeded
         exit when l_counter >= l_max_rows;
      end loop;
   end;
end;
/

Some parts of the code are more cryptic than others.

First up is the "SYNC_SEARCH_INDEX" procedure. It's sole purpose is to synchronize the search index with the TEXT_CONTENT column. When you perform DML on the TEXT_CONTENT column, changes to the text index does not automatically propagate to the search index (unless the index is explicitly told to do so). So, in this example, if you insert a new row, the TEXT_CONTENT will not show up through Oracle Text searches until you have told it to synchronize the index. Indexing can be a resource demanding operation, so it makes sense to separate table DML from the indexing job.

In short: Make sure you synchronize the text index after DML on the indexed column has been performed.

Next is the "SEARCH_TEXT" function, particularly the query template. In the past I have used Oracle Text in its most simple form, but query templates adds a new dimension to controlling your searches. Basically you define how you want to use the search tokens (search phrase) to find matches in the text index. It is written in XML-form, and the <seq>-tag states the order of which you want to process your criteria. For more information on query templates, take a look at the documentation.

A short explanation on the query template:

<rewrite>transform((TOKENS, "{", "}", " "))</rewrite>: Matches when whole phrase (tokens in order) is present within document
<rewrite>transform((TOKENS, "{", "}", "AND"))</rewrite>: Matches when each token is present within document
<rewrite>transform((TOKENS, "?{", "}", "AND"))</rewrite>: Matches when each token is present within docoument, allowing typos (fussy search)
<rewrite>transform((TOKENS, "{", "}", "OR"))</rewrite>: Matches when any of the tokens are present within document
<rewrite>transform((TOKENS, "?{", "}", "OR"))</rewrite>: Matches when any of the tokens are present within document, allowing typos (fussy search)

Although it will return matches according to the sequence, the "score" within the sequence will be random (as far as I could see at least), so the "score" column in the example above needs a bit of work. I also learned the hard way that structure of the XML in the query template is not validated, and it swallows typos without a word.

Test the Code
You can test the code directly from SQL*Plus or your favorite weapon of choice. The following code will search for "test":

select filename
,      snippet
from   table(samples_docx_search_p.search_text('test'))
order  by score
/

Wildcards
My query template does not support searching for parts of words, there are some reasons for this behavior.

Wildcard searches requires more resources
The results returned will be unpredictable. Imagine searching for "ora*", both "oracle" and "orange" will match.
Wildcard searches are more suitable for look-ups in structured data

What did I mean by that last statement? As I see it, users today are using a wide range of search engines and site searches every day, and wildcard matching are in most cases available only through the "advanced search"-link (that is rarely used, if ever). Let alone that people do not expect this kind of behavior anymore. If you want to help the user, add ajax autocomplete to your search field, and use the Oracle Text index. In the traditional back office application, it is another matter (look-ups and such).

Create a Search Page in Oracle APEX
I will not do a detailed explanation here, but creating a report based on a query will do the trick. If you choose "Yes" for "Enable search" in the wizard it will save you some work by creating the search dialog on the page. Clean up the query in the report region so that tour query looks something like this:

select filename
,      snippet
from   table(samples_docx_search_p.search_text(:PXX_REPORT_SEARCH))
order  by score

When keeping the order by, I assume that you will let the score column determine the result order. For good measure you can create a row template for the result representation in the APEX report so you can have heading and snippet spread over two rows.

Update Sample Upload Page
If you created the upload page described in my previous post, include a new page process (After Submit, and after the "extract_text"-process). The process should be of PL/SQL-type, and the code should look something like this:

samples_docx_search_p.sync_search_index;

It does not necessary have to be a page process, but it is important to synchronize the text index after DML.

How to Handle DOC and DOCX
...And PDF, XLS, PPT, etc. Well, the DOCX is the exception here. Oracle Text does not understand the format yet (unless you are running on release 11.1.0.7 or higher). If you create an Oracle Text index on a BLOB column containing other file types which it does understand, they will be indexed automagically. If you want to index the content of a DOCX document in the same index, use the technique described in my last post, convert the output (plain text) into a BLOB, and insert the BLOB into the table with the other documents. Or said in a different way: you except and handle DOCX documents differently than the known document types.

Querying an Oracle Text index works the same for BLOB and CLOB columns.

There is definitely room for improvement in the code, but it serves well enough for demonstration purposes.

Enjoy :-)

AJAX based select list in APEX - Part 2

Tobias Arnhold — Mon, 01 Mar 2010 14:31:47 PST

I couple of days ago I wrote about a way using AJAX based select lists in APEX.
After some hints from Peter Raganitsch I tried the ApexLib Framework but I run into some issues with ExtJS.
Error message:
vFieldValue is undefined
populateLovField(Object { name="pFieldId"}, Object { name="pLovEntry"})ApexLib_Full.js
populateDependingLovs(Object { name="pFieldId"})ApexLib_Full.js
(?)()ApexLib_Full.js
(?)(Object { name="pEvent"})ApexLib_Full.js
vAjaxRequest.add(pLovEntry.oUsedFi...ace(/\:/g, String.fromCharCode(1)));

Error part where somehow an ExtJS function got called:
vAjaxRequest.add(pLovEntry.oUsedFieldList[ii], vFieldValue.replace(/\:/g, String.fromCharCode(1)));

I found another solution from Carl Backstrom: Ajax Selects
I modified his script a bit and used it in my environment.

Using the ApexLib would have been much easier luckily I just had a really small application.

Good for us that APEX 4.0 will solve these problems by a new standard feature:
Oracle APEX 4.0: Cascading LOVs/Select Lists

Get a Suite Deal at ODTUG

Scott — Mon, 01 Mar 2010 11:49:01 PST

As Dimitri mentioned, one way to get a Suite at this year's ODTUG is to mention his (or my) name when you register. Now, there's another way: just create a 3-minute video on "Why You Need to Attend ODTUG Kaleidoscope 2010".

Here's the details via the folks at ODTUG:

Make it funny…or Make it musical…or Make it informational…or Make it Rhyme…or…???. Use your imagination to create the catchiest video possible. Make sure to include the ODTUG Kaleidoscope logo.

To submit your video for consideration, send an e-mail to ODTUG with your name and YouTube video link.

Video Contest Guidelines: Video must be three minutes or less, contain no inappropriate content, and be posted publicly to YouTube by March 31, midnight EST. All videos must remain in public domain until after July 1^st, 2010. The winning video will be selected from among all the entries. The judges’ decision will be final. Judges may include members of the ODTUG Kaleidoscope Conference Committee and others. Winner will be notified by April 2^nd, 2010. Winner will have the choice of a complimentary non-transferable registration to ODTUG Kaleidoscope 2010 or a suite upgrade at theMarriott Wardman Park.

DOCX Part I: How to Extract Document Content as Plain Text

Håvard Kristiansen — Mon, 01 Mar 2010 06:48:33 PST

In this post I will demonstrate how to extract plain text from Microsoft Open Office XML Format (DOCX) documents stored as a BLOB data type in an Oracle database. It is part one of two posts concerning how to extract and index unstructured content stored in DOCX files. I found snippets of solutions several places, but no complete copy-paste solution, so perhaps this will save some of you a bit of work.

The solution is tested on Oracle database Enterprise Edition release 11.1.0.6 with Apex 3.2.1 running on Windows XP, but should work down to 10g Standard Edition (not Oracle XE, since part of the code is in Java).

See it In Action
I have updated my demo application with a couple of pages so you can try it out yourselves: Go to demo application. There is a limited space available at apex.oracle.com, so please take care when uploading (and it is very easy to test in your own environment).

When will You Ever Need This?
If you have an Oracle database version prior to 11.1.0.7, Oracle Text is not able to index DOCX documents (as it does DOC documents). From version 11.1.0.7 and on, Oracle uses technology from Stellent, and DOCX is indexed as the other formats. So if you want to index DOCX text content in Oracle Text in a version prior to 11.1.0.7, then this could be a way to do it.

Oracle is working on a back-port to 10.x, but I have no status when (if) this will be available. Microsoft Office 2007 has been around since.. Take a wild guess! So these things obviously takes some time.

About Microsoft Open Office XML Format
As seen from the eyes of a PL/SQL developer, that is. First of all, XML sounds promising, you can do a ton of things with a valid XML in an Oracle database. You get the first nasty surprise when opening a DOCX-document in your favorite text editor; it is a zip archive! The next is when you realize that utl_compress can't help you uncompressing it either.

So Google next, and realizing this cannot be easily done in a pure PL/SQL solution, Google yields this gem from Ted Neward. It is a DOCX (Open XML) walk through as seen from the eyes of a Java developer. Very educational.

How to Unzip in PL/SQL
You probably can, but that would probably also involve a lot of work. The easy way is to take hold of a Java method that already does what you want. After searching the net I came up with this post from peterv6i which does exactly what I want (and more, it can also add files (BLOB) to an existing zip archive).

My short version of this (as I only need to extract content), follows below. The script creates a java class with a method to extract a file from a zip archive, and a PL/SQL wrapper to the getFileFromZip method. The database user must have JAVA_DEPLOY and JAVAUSERPRIV (I think) roles.

create or replace java source named "ZIPImpl" 
AS 
import java.io.*; 
import java.util.zip.*; 
import java.sql.*; 
import oracle.sql.*; 
public class ZIPImpl 
{ 
public static void getFileFromZip(oracle.sql.BLOB srcBlob, oracle.sql.BLOB dstBlob[], java.lang.String name) { 
try { 
   OutputStream outBuffer = dstBlob[0].getBinaryOutputStream(); 
   InputStream inBuffer = srcBlob.getBinaryStream(); 
   ZipInputStream zip = new ZipInputStream(inBuffer); 
   ZipEntry entry; 
   byte[] tmpBuffer = new byte[2048]; 
   while((entry = zip.getNextEntry()) != null) { 
      if (entry.getName().compareTo(name)==0) { 
         int n; 
         while ((n = zip.read(tmpBuffer)) >= 0) 
           outBuffer.write(tmpBuffer, 0, n); 
      } 
   } 
   outBuffer.close(); 
 } 
 catch (SQLException e) { 
   System.err.println(e); 
 } 
 catch (IOException e) { 
   System.err.println(e); 
 } 
} 
}; 
/

alter java source "ZIPImpl" compile 
/      

create or replace package zip as 
   procedure unzip( 
      p_src       in          blob
   ,  p_dst       in out      blob
   ,  p_filename  in          varchar2); 
end; 
/

create or replace package body zip as 
   procedure unzip( 
      p_src       in          blob
   ,  p_dst       in out      blob
   ,  p_filename  in          varchar2) 
   as language java 
   name 'ZIPImpl.getFileFromZip(oracle.sql.BLOB, oracle.sql.BLOB[], java.lang.String)'; 
end; 
/

How to Extract Plain Text Content from DOCX
So, now you have a procedure to extract a file from a zip archive, next is to attack the content of the file. The DOCX file consists of several files, but the text content of the document resides in "word/document.xml". As the file is an XML document, I will perform an XML/XSL transformation of the file to be left with only plain text.

The script below creates a table SAMPLES_DOCX which will hold the original DOCX file stored as a BLOB, and a CLOB containing the plain text. It also creates a package with three procedures:

Store the DOCX (as uploaded into APEX_APPLICATION_FILES)
Download the original file (strictly not necessary for this exercise, but nice to have all the same)
Extract and store plain text from DOCX

The XSL used to transform the document.xml file is an (extremely) abbreviated version of the XSL posted here in the Oracle Technical Forums.

create table samples_docx (
   filename       varchar2(255) not null
,  mime_type      varchar2(255)
,  orig_file      blob
,  text_content   clob)
/

alter table samples_docx add constraint samples_docx_pk primary key (filename)
/

create or replace package sample_docx_p as
   -- get file from apex_application_files and store it in samples_docx table
   procedure store_docx (
      p_file_name    in       varchar2);
   -- download procedure for original docx file
   procedure retrieve_docx (
      p_file_name    in       varchar2);
   -- extract plain text from docx file (this is the meat)
   procedure store_text (
      p_file_name    in       varchar2);
end;
/

create or replace package body sample_docx_p as
   -- get file from apex_application_files and store it in samples_docx table
   procedure store_docx (
      p_file_name    in       varchar2)
   is
      l_file         blob;
      l_mime_type    apex_application_files.mime_type%type;
      l_name         apex_application_files.name%type;
   begin
      -- get file from apex files
      select  name
            , mime_type
            , blob_content
       into   l_name
            , l_mime_type
            , l_file
       from   apex_application_files
      where   name = p_file_name;
      -- insert record into samples table
      insert into samples_docx ( filename
                               , mime_type
                               , orig_file)
       values   (   l_name
                  , l_mime_type
                  , l_file);
      -- delete file from apex files when done
      delete from   apex_application_files
           where   name = p_file_name;
   end store_docx;
   -- download procedure for original docx file
   procedure retrieve_docx (
      p_file_name    in       varchar2)
   is
      l_file         blob;
      l_mime_type    apex_application_files.mime_type%type;
      l_name         apex_application_files.name%type;
      l_size         number;
   begin
      -- get file from apex files
      select  filename
            , mime_type
            , orig_file
            , dbms_lob.getlength(orig_file)
       into   l_name
            , l_mime_type
            , l_file
            , l_size
       from samples_docx   
      where   filename = p_file_name;
      -- return file
      owa_util.mime_header( nvl(l_mime_type,'application/octet'), false);
      htp.p('Content-length: ' || l_size);
      htp.p('Content-Disposition:  attachment; filename="'||replace(replace(substr(l_name,instr(l_name,'/')+1),chr(10),null),chr(13),null)|| '"');
      owa_util.http_header_close;
      wpg_docload.download_file(l_file);
   end retrieve_docx;
   -- perform xsl tranformation of document.xml
   function get_text (
      p_docx_xml     in       xmltype
   ) return clob
   is
   l_clob   clob;
   -- xsl monkeyed from http://forums.oracle.com/forums/thread.jspa?messageID=3368284
   -- abbreviated quite a bit, check out original posting by "user304344" for the original
   l_xsl    xmltype := xmltype('<?xml version="1.0" encoding="utf-8"?>'
                     ||chr(10)||'<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" '
                     ||chr(10)||'xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"'
                     ||chr(10)||'xmlns:v="urn:schemas-microsoft-com:vml"'
                     ||chr(10)||'exclude-result-prefixes="w v">'
                     ||chr(10)||'<xsl:output method="text" indent="no" encoding="UTF-8" version="1.0"/>'
                     ||chr(10)||'<!-- document root -->'
                     ||chr(10)||'<xsl:template match="/">'
                     ||chr(10)||'<!-- root element in document --> '
                     ||chr(10)||'<xsl:apply-templates select="w:document"/> '
                     ||chr(10)||'</xsl:template>'
                     ||chr(10)||'<!-- ****************************start document**************************** -->'
                     ||chr(10)||'<xsl:template match="w:document">'
                     ||chr(10)||'<xsl:for-each select="//w:p">'
                     ||chr(10)||'<xsl:apply-templates select="*/w:t"/> '
                     ||chr(10)||'<xsl:text>|¤¤</xsl:text> '
                     ||chr(10)||'</xsl:for-each> '
                     ||chr(10)||'</xsl:template>'
                     ||chr(10)||'<!-- get all text nodes within a para -->'
                     ||chr(10)||'<xsl:template match="*/w:t">'
                     ||chr(10)||'<xsl:value-of select="."/>'
                     ||chr(10)||'</xsl:template>'
                     ||chr(10)||'<!-- **************************** end document**************************** -->'
                     ||chr(10)||'</xsl:stylesheet>');
   begin
      -- "|¤¤" is just a hack to get linebreaks, should be an easier way to achieve this
      select replace(xmltransform(p_docx_xml, l_xsl).GetClobVal(), '|¤¤', chr(10))
        into l_clob
        from dual;
      return l_clob;
   end;   
   -- extract plain text from docx file (this is the meat)
   procedure store_text (
      p_file_name    in       varchar2)
   is
      l_docx            blob;
      l_docx_unzip      blob;
      l_doc             clob;
      l_dest_offset     integer := 1;
      l_src_offset      integer := 1;
      l_lang_context    integer := dbms_lob.default_lang_ctx;
      l_warning         integer;
   begin
      -- get original file
      select orig_file
        into l_docx
        from samples_docx
       where filename = p_file_name;
      -- create lob locators
      dbms_lob.createtemporary(l_docx_unzip,false);
      dbms_lob.createtemporary(l_doc,false);
      -- use java to unzip the docx file and retrieve document.xml
      zip.unzip(l_docx, l_docx_unzip, 'word/document.xml');
      -- convert blob to clob
      dbms_lob.converttoclob
               ( dest_lob => l_doc
               , src_blob => l_docx_unzip
               , amount => dbms_lob.lobmaxsize
               , dest_offset => l_dest_offset
               , src_offset => l_src_offset
               , blob_csid => nls_charset_id('AL32UTF8') --in my case, it is stored as UTF8
               , lang_context => l_lang_context
               , warning => l_warning
               );
      -- transform clob via xsl to get clean text
      l_doc := get_text(xmltype(l_doc));
      -- update the column containing document text
      update samples_docx
         set text_content = l_doc
       where filename = p_file_name;
      -- clean lob locators, should be repeated in exception block
      dbms_lob.freetemporary(l_docx_unzip);
      dbms_lob.freetemporary(l_doc);
   end;
end;
/

Bringing it Together in Oracle APEX
To test the code, you can create a simple upload page in APEX:

Create new page
Form
Form on a procedure
<schema name> where you installed the application
Choose "sample_docx_p.store_docx" as stored procedure name
Accept defaults (or change to your liking)
Choose desired tab options
Leave Invoking Page/Button Label blank if you do not want an invoking page
Choose branch pages (can be the same as the one you are creating)
Accept defaults
Click Finish
Click Edit Page
Choose PXX_FILE_NAME, and change "Display as" from "Text item" to "File Browse..."
Apply changes

To add post processing to extract DOCX file content:

Click Create under Processes
Choose PL/SQL
Give the process a name like "extract_text"
In the "Enter PL/SQL Page Process" dialog, paste "sample_docx_p.store_text(:PXX_FILE_NAME);" (XX being your page number)
Create process (and ensure the process comes after "Run Stored Procedure" process created by the page wizard

The last process could be implemented as a trigger on the samples_docx table.

Run the page and test by uploading a DOCX document (or test it in my demo application).

Building More
If you have a need to manipulate DOCX documents, this could probably be achieved by using the complete "ZIPImpl" as posted in this article, and using substitution variables in the same manner as I have described with RTF documents in an earlier post.

The zip/unzip may also come in handy when handling other office files (like Open Office ODF).

This is only a proof of concept, and the code is not production ready. Really! I'm not just saying that out of habit.

The next (and final) part of the DOCX "series" will address how to index the plain text content using Oracle Text.

Oracle APEX 4.0: Cascading LOVs/Select Lists

Patrick Wolf — Sat, 27 Feb 2010 12:20:26 PST

One of the new features of Early Adopter 2 are Cascading LOVs/Select Lists. I’m pretty sure that almost every APEX developer had already the requirement to refresh a child select list when a parent select list was changed. For example you pick a department in the first select list and the second should just [...]

Oracle Application Express 4.0 EA2 live

Dimitri Gielis — Sat, 27 Feb 2010 04:08:09 PST

A few hours after my previous post, APEX 4.0 EA2 hit the air.

The url to this new version of APEX 4.0 is still the same: http://tryapexnow.com/apex/

I'll start some proper testing next week, but if you already want to read what is changed, you find more information on Joel Kallman's post.

Application Express 4.0 - Early Adopter 2 Now Available

John Scott — Fri, 26 Feb 2010 15:53:27 PST

If you didn’t already see the news, the EA2 release of APEX4.0 has just been opened up. Head over to www.tryapexnow.com, sign up for a workspace and try out all the cool new features. EA2 includes the following key features - Websheets (this will be huge!) New collection enhancements Tree region Debugging has been rewritten and many more (see [...]

Oracle APEX 4.0 Early Adopter Release 2 available!

Patrick Wolf — Fri, 26 Feb 2010 14:15:14 PST

Tryapexnow.com has just been updated to our new Oracle APEX 4.0 Early Adopter Release 2. Have a look, there are many new features, like WebSheets for example. A detailed list will be made available on Monday. I have also updated my plug-in example application with new plug-ins. Tutorials will follow soon. You can check it [...]

Apex EA2 update

Paul Brookes — Fri, 26 Feb 2010 14:23:14 PST

To see the new style Plugins use this link to the Plugins Application provided by Patrick Wolf:

Plugins Application

I've managed to copy the syntax used in some of these plugins to update those on my Online Store demo.

I've still need to upload some images and it looks like there are a few issue with my schema, but generally the app appears to be working. So, in general it looks like EA1 apps can be imported into EA2 with little need to change anything.

Application Express 4.0 Early Adopter Phase II is available

Joel R. Kallman — Fri, 26 Feb 2010 13:45:26 PST

The second phase of the Application Express 4.0 Early Adopter is now available. A new instance with the latest build of Application Express 4.0 is now available on http://tryapexnow.com.

This database was created with database character set AL32UTF8 (we're still a little confused by the Amazon Machine Images from Oracle aren't already AL32UTF8.
The Websheets feature is now available. The security of Websheet applications still needs to be refined, but the functionality and ease of creation of these types of applications is amazing.
The new tree region makes its debut.
Collections now support NUMBER and DATE
apex_collection.create_collection_from_query_b and apex_collection.create_collection_from_queryb2 support bind variables and row limits
Debugging and logging has been completely rewritten - no longer is debug output emitted within the page, but it's collected and can be viewed later (and also queried via views).
You won't be able to see this, but the tablespaces / data files of provisioned workspaces will now autoextend
There have been many improvements in the Plug-In infrastructure, which Patrick will surely expand upon.
A Migration Assistant has been created, which lets you upgrade all of the elements of your pre APEX 4.0 application in one place.
Native validations and declarative validations have also been rewritten and improved (again, Patrick will expand upon this).
All of the item types have been consolidated (so you no longer see 1,000 different Date Picker types in your item type select list, as well as other item types)
A large number of bugs and usability issues have been corrected, thanks in a large part to the tremendous amount of feedback we received.

This isn't an exhaustive list. The updated list of new features will be available on tryapexnow.com.

If you had used the first Application Express 4.0 Early Adopter instance, your workspace and schema have not been migrated to this instance. You will need to sign up for a workspace but you shouldn't have to take the survey again. The original Application Express 4.0 Early Adopter instance is available at http://184.73.244.154/apex, and it will be available for the next couple of weeks.

Thanks to everyone who has participated and provided feedback, as the feedback and suggestions have been invaluable.