Excel output directly from Oracle Application Concurrent Request Output


Need to create Microsoft Excel style files directly openable from Oracle Applications concurrent request output … without using BI Publisher?

A little know file format with acronym SYKL is a handy tool for create files readable in Microsoft Excel. here’s the recipe for a very simple Excel (SYLK) output concurrent program for an FND_USERS listing with creation date from/to parameters. So, without further ado, here’s the recipe for a very simple Excel (SYLK) output concurrent program for an FND_USERS listing with creation date from/to parameters

   1. Take a PL/SQL package based on the Oracle provided OWA SYLK ppckage (owasylk.sql / owa_sylk.sql) and make some changes: ( http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:728625409049 )

  • rename it to owa_sylk_apps
  • Change it to use fnd_file.put_line(fnd_file.output instead of utl_file.put_line(g_file
  • Remove parameters for p_file

   2. Create a PL/SQL package for the concurrent program that outputs SYLK file to the concurrent request output.

create or replace package XXXV8_USERS_SYLK_PKG AUTHID CURRENT_USER

AS

procedure main

( errbuf      out varchar2

, retcode     out varchar2

, p_date_from in  varchar2

, p_date_to   in  varchar2

);

end XXXV8_USERS_SYLK_PKG;

/

create or replace package body XXXV8_USERS_SYLK_PKG

AS

procedure main

( errbuf      out varchar2

, retcode     out varchar2

, p_date_from in  varchar2

, p_date_to   in  varchar2

) as

  l_date_from date;

  l_date_to   date;

begin

  l_date_from := fnd_date.canonical_to_date(p_date_from);

  l_date_to   := fnd_date.canonical_to_date(p_date_to);

  owa_sylk_apps.show(

        p_query => ‘select user_id user_id, user_name user_name, ‘

                   ‘       description description, creation_date created ‘

                   ‘from fnd_user ‘

                   ‘where trunc(creation_date) >  :DATE_FROM ‘

                   ‘and   trunc(creation_date) <= :DATE_TO ‘,

        p_parm_names =>

                 owa_sylk_apps.owaSylkArray( ‘DATE_FROM’, ‘DATE_TO’),

        p_parm_values =>

                 owa_sylk_apps.owaSylkArray(l_date_from ,l_date_to),

        p_widths =>

                 owa_sylk_apps.owaSylkArray(20,20,20,20)

                 );

end main;

END XXXV8_USERS_SYLK_PKG;

   3. Setup the concurrent program

   4. Hijack one of the little used Viewer Options (PCL) so that we can get the Concurrent Request output browser to automatically open Microsoft Excel. Note this is optional, you can just create a new Viewer Option but then you may get the “Choose Viewer” box when viewing concurrent request output. NB: Navigation path is System Administrator, Install, Viewer Options

update fnd_mime_types_tl

set    mime_type = ‘application/vnd.ms-excel’

,      description = ‘Excel (SYLK) used to be application/vnd.hp-PCL: Printer Control Language’

,      last_updated_by = 0

,      last_update_date = sysdate

where  file_format_code = ‘PCL’

and    mime_type = ‘application/vnd.hp-PCL’;

commit;

   5. Assign the concurrent program to the appropriate request group (e.g. System Administrator, All Reports, Application Object Library) and run the concurrent program to test it all out! And there you have it – Excel style output direct from concurrent request generated by PL/SQL!

Article courtesy and credit goes to  —  http://garethroberts.blogspot.com/2007/10/excel-file-output-from-oracle.html

8 thoughts on “Excel output directly from Oracle Application Concurrent Request Output

  1. As mentioned above we have created concurrent program wit output type as PCL and all the other steps including UPDATE MIME TYPE to get the output in .XLS.

    But we have a problem here,
    We are able to open the output as .pcl.xls format, but when we save the file the format is in .PCL only.
    What could be the reason for this.?
    How to resolve this issue? We need the output should be saved in .XLS instead of .PCL format.

    Thanks
    Alaka

    Like

  2. Dear shiv;
    Thanks for great solution.
    I have a question for the place where i will put my query in.
    Im facing a problem which is the output is placed in one column in the Excell, how can i separte columns in excell sheet to reflect the different columns in the database.

    Waiting ur response ASAP.

    Regards,

    Like

  3. As mentioned above we have created concurrent program wit output type as PCL and all the other steps including UPDATE MIME TYPE to get the output in .XLS.

    But we have a problem here,
    We are able to open the output as .pcl.xls format, but when we save the file the format is in .PCL only.
    What could be the reason for this.?
    How to resolve this issue? We need the output should be saved in .XLS instead of .PCL format.

    Thanks
    Alaka

    Like

  4. ERROR at line 1:
    I am always seeing below error msg when I use the above package. Please help at the earliest..

    ORA-06533: Subscript beyond count
    ORA-06512: at “APPS.OWA_SYLK”, line 30
    ORA-06512: at “APPS.OWA_SYLK”, line 267
    ORA-06512: at line 5

    Like

  5. Hi

    Thanks for great solution .
    I am having on queston.
    Instead of updating “Viewer Option” of PCL, can we create our own viewer option.So that whenver i need output in excel format i need to add that output format to my concurrent request.
    If yes please suggetest how.

    Waiting for reply

    Like

Want to give some comment to author ( Shivmohan Purohit )

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s