Output in MS Excel-format for browser in PL/SQL

This example will show how to output Tab-Separated-Values with Oracle PL/SQL for Microsoft Excel in a browser. This is not exactly JEE, but the main idea can also be applied in Java.

Create a public accessible procedure that outputs Tab-Separated-Values. Make sure the execute rights are granted on user 'PUBLIC' for the procedure, otherwise the procedure is not allowed to be called by public users.

procedure example is
    cursor c_cursor is
        select col1, col2
        from table;

    r_row c_cursor%rowtype;
begin
    -- Print TSV header.
    owa_util.mime_header('application/vnd.ms-excel');

    htp.print('COL1' || CHR(9) ||
              'COL2' || CHR(9) ||
              CHR(13));

    open c_cursor;
    loop
      fetch c_cursor into r_row;
      exit when c_cursor%NOTFOUND;

      -- Print TSV row.
      htp.print(r_row.col1 || CHR(9) ||
                r_row.col2 || CHR(9) ||
                CHR(13));
    end loop;
    close c_cursor;
end example;

The main point in this procedure is to let the browser know we are dealing with output intended for Microsoft Excel. We use this procedure to set the MIME-type in the HTTP-header of the response:

owa_util.mime_header('application/vnd.ms-excel');

The browser will then forward the output to MS Excel for processing.

In the procedure we output the values separated by tabs, which are generated with the PL/SQL function CHR(9). The line is then closed with a new-line using CHR(13).

The output is accessible using the URL structure:

http://<hostname>/pls/portal/<schema>.<package>.<procedure>