wpg_docload.download_file : mime type not recognized by client

For a project we are currently working on, we needed to generate, and send a Word 2010 document to the client. The document was generated by a great PL/SQL document generation tool called Doxxy, and was sent to the client using the wpg_docload package. This is a standard Oracle pl/sql package that can be used to download files, BLOBs and BFILEs.

Before the download, we set the Content-type in the http header as follows :

owa_util.mime_header('application/vnd.openxmlformats-officedocument.wordprocessingml.document',FALSE);

When sending the document to the client, we got the following popup in our browser :

Image

So it looked like our browser didn’t recognized that this was an Word 2010 document.

Looking at the response header, using Firebug, we got the following result :

Image

Somehow the content type for Word 2010 was overwritten to text/html; charset=utf-8.

So, time for the good old trial and error approach, which, after a while, paid off.

Before setting the response header to : owa_util.mime_header(‘….’,FALSE); we need to issue the following commands :

htp.flush();
htp.init();

Now the code looks like this  :

-- first clear the header
 htp.flush;
 htp.init;
 -- set up HTTP header
 owa_util.mime_header('application/vnd.openxmlformats-officedocument.wordprocessingml.document', FALSE);
 -- set the size so the browser knows how much to download
 htp.p('Content-length: ' || DBMS_LOB.getlength(v_blob));
 -- the filename will be used by the browser if the users does a save as
 htp.p('Content-Disposition:attachment; filename="'||nvl(v_filename,'export')||v_ext||'"');
 -- Set COOKIE (for javascript download plugin)
 htp.p('Set-Cookie: fileDownload=true; path=/');
 -- close the headers
 owa_util.http_header_close;
 -- download the BLOB
 wpg_docload.download_file(v_blob);

After adding these 2 lines, we got the correct mime type :

Image

Many thanks to Willem Albert and Bjorn Fraeys for delivering the content for this blog !

5 neat little features of the 12C database to remember

In this post, I’d like to introduce 5 of the many new features Oracle 12C brings to us, database developer’s.
Of course this blog would be to long to explain them all in detail, so I will stick
to a small introduction.

  1. Generating a primary key without triggers, using nextval or identity
    In 12C, you are now able to use sequence.nextval or the new keyword ‘identity’ as default values.
    The ‘identity’ keyword will generate the value max(id)+1 for your primary key. So now you don’t need to create triggers anymore, when generating PK’s with a sequence.
    And problems with sequences that are not in sync, when moving/copying tables to another schema/database, can be avoided by using the ‘identity’ keyword.
    Example PK row declaration :
    id_pers         number default person_seq.nextval primary key;
    id_pers         number generated as identity;
  2. Accessible key word : define which code can call your function/procedure.
    One of the major problems of PL/SQL is, when developing a lot of packages/procedures/functions, in the end there is no telling who is called by who. This problem can now be answered by ‘white listing’. This means that, on creation, you are telling the package/function/procedure/type by whom it is accessible, or may be used.
    The accessible by clause takes packages/functions/procedures/triggers as accessor clause.
    Example white listing :
    - create procedure get_sales_data accessible by (my_sales_proc)…
    – create procedure get_sales_data accessible by (my_after_update_trigger)
    - create package my_package accessible by (my_other_package)…When the object is not accessible, following error will be thrown during compilation, or at runtime, in case of an anonymous PL/SQL block :PLS-00904: insufficient privilege to access object MY_PACKAGE.MY_PROCEDURE
  3. Temporal Validity of a row
    Sometimes rows in a table are valid or not, depending on a timeframe. For instance a subscription for a magazine, may only be valid for a year. Adding this validity to a row goes as follows :

     create table subscriptions
     ( person_id             number,
     subscription_id                 number,
     person_name                   varchar2(500),
     subscr_start_date   date,
     subscr_end_date              date,
     period for valid(subscr_start_date , subscr_end_date)
    )
    

    Now with following query we can select the ‘valid’ subscriptions :

     select * from subscriptions
     as of period for valid sysdate;
    
  4. New PL/SQL Package UTL_CALL_STACK
    The UTL_CALL_STACK package provides subprograms to return the current call stack for a PL/SQL program. This could already be done by DBMS_UTILITY.FORMAT_CALL_STACK, but this new package returns this information in a more structured way, and includes the depth of the call (calling level) and the names of the subprograms.
    This will make this information more usable in code. Related to this subject, 2 new directives are added in 12c, next to $$PLSQL_LINE and $$PLSQL_UNIT that already existed).
    – $$PLSQL_OWNER
    – $$PLSQL_TYPE

       dbms_output_put.line("Owner of this package is "+$$PLSQL_OWNER);
    

    Will print : Owner of this package is SCOTT

  5. An Invoker’s Rights Function Can Be Result Cached
    Caching results of a PL/SQL function already exists in 11g. Basically what happens is that, for a certain function, you define that the result, for given parameters should be cached in memory.
    So first time function getPerson(123) is executed, the data is fetched from the database, second time the function is called with parameter ‘123’, the result is fetched from the cache in memory, resulting in a better performance.
    Whenever a DML statement is executed on the table(s) used in that function, the cache is automatically cleared, causing the next call to return the new data. (Since 11G rel. 2, Oracle manages these dependencies himself.)
    So in our case, Oracle caches the result’s of function getPerson() for every key it is called with.
    Through Oracle Database 11g Release 2 (11.2), only definer’s rights PL/SQL functions could be result cached. Now in 12c, the identity of the invoker is implicitly added to that key.

As already mentioned, the possibilities of these new features go way beyond what I describe here. But hopefully it’s a start to a few experiments on your side !

More info can be found at http://docs.oracle.com/cd/E16655_01/server.121/e17906/chapter1.htm

All things Oracle: Generating XML from SQL

Did you ever needed your table data in an XML format?
Well, it isn’t that hard…  Oracle provides several ways to generate XML, in both SQL and PL/SQL.

I wrote an article on All Things Oracle in which I explain step by step how you can generate XML from table data.
In this first part I show you some SQL functions which are easy to use.  Check it out!

All things Oracle

There’s a new Oracle source available:  All Things Oracle.

The aim of All Things Oracle is to provide a gateway to the wealth of information and material available for Oracle developers and DBAs.
The site brings articles and other resources of Oracle experts.
Just to name a few:

All very experienced experts that will bring interesting articles!

I will also contribute to this site.
My specialities are SQL, PL/SQL, Forms and Forms Modernization, so expect articles on these topics in the near future on All Things Oracle.