This discussion is archived
1 4 5 6 7 8 10 Previous Next 146 Replies Latest reply: Dec 2, 2009 10:18 PM by user786836 Go to original post RSS
  • 75. Re: Export Excel instead of .csv
    601867 Newbie
    Currently Being Moderated
    Hi Denes.

    Your package is pretty nice. I was wondered at a first time I downloaded a test xls file and opened it in simple notepad that it is just HTML file with defined xml-excel scheme. So it is pretty simple and straight.

    Problem is that my reports (and present xls exports) are pl/sql based exports. To understand: report in APEX contains concatenated strings for example but strings in exported file should be not concatenated. My xls eport works fine but it is poor csv excel file so I have no options to color the background of cells and/or define some filters in it.

    As Your export is just HTML file it allows You to color the cells but still You are not able to define some filters in this exported file (as my customer needs them).

    Is there any way to handle it?

    Many thanks.
    Peter Krajcar

    PS: I know I am able to turn on some basic filtering in exported csv file but I cannot set this to be turned on as default immediately when exported file is opened for the first (and next) time(s).
  • 76. Re: Export Excel instead of .csv
    paulgall Explorer
    Currently Being Moderated
    Hello,

    I've just tried to compile the package and I am getting the error PLS-00103 at the following lines:

    v_header := q'!
    <body link=blue vlink=purple>

    <table>
    <tr>!';

    in the print_report_header procedure and

    v_footer :=
    q'!
    <![if supportMisalignedColumns]>
    <tr>
    </tr>

    <![endif]>
    </table>

    </body>

    </html>!';

    in the print_footer procedure.

    What is q?

    Should q go into the comments?

    We are on a Oracle 9.2 database and using apex 3.0.1

    Cheers,
    Paul.
  • 77. Re: Export Excel instead of .csv
    628828 Newbie
    Currently Being Moderated
    Hi Denes,

    I've tried to install the export_excel_pkg:
    http://htmldb.oracle.com/pls/otn/f?p=31517:108

    But after running it, at compilation I get the following error:

    Compilation failed,line 29 (07:04:20)
    PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ; The symbol ";" was substituted for "end-of-file" to continue. Compilation failed,line 493 (07:04:20)
    PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ; The symbol ";" was substituted for "end-of-file" to continue.

    Where am I going wrong?

    Thanks a lot !
    Adela

    p.s. I need this solution, because if I export to csv chinese/korean/japanese characters they mess up in the report and I get garbled data.
    csv download problem
  • 78. Re: Export Excel instead of .csv
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    Adela,

    I just downloaded the package as it is available for download and run it. It compiles and it is running. Have you touched the code after download, had a copy paste problem? The error you receive would point to an sytax error, which isn't there in my package.

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.opal-consulting.de/training
    http://apex.oracle.com/pls/otn/f?p=31517:1
    -------------------------------------------------------------------
  • 79. Re: Export Excel instead of .csv
    628828 Newbie
    Currently Being Moderated
    Thanks Denes!
    I've runned the sql by parts that's why it didn't work, but now it compiles succesfuly.
    But in the Excel file I get the following error: "Report Values Error: ORA-06550: line 1, column 187: PL/SQL: ORA-00936: missing expression ORA-06550: line 1, column 179: PL/SQL: SQL Statement ignored / -6550 / select from (select cust "cust", sub_lob "sub_lob" from requests where request_id = 2491)"
    Also wanted to ask you what alternative there is to " Function Returning SQL type of report" or an idea for proper downloading chinese/korean/japanese characters, if you happen to came across this kind of pb.

    Thanks in advance !
    Adela
  • 80. Re: Export Excel instead of .csv
    666835 Newbie
    Currently Being Moderated
    I'm getting the following message written in the first cell of the generated .xls file:

    Report Values Error: ORA-06550: Zeile 1, Spalte 166: PL/SQL: ORA-00936: Ausdruck fehlt ORA-06550: Zeile 1, Spalte 158: PL/SQL: SQL Statement ignored / -6550 / select from (SELECT * FROM employees WHERE INSTR (':' || (SELECT v('P1_HIDDEN_ITEM') FROM DUAL) || ':', ':'||bsaomr_id||':') > 0)

    Could it be the "select from(SELECT..." ? But I don't see why that happens...

    my select is:

    SELECT *
    FROM employees
    WHERE INSTR (':' || :p1_hidden_item || ':', ':'||bsaomr_id||':') > 0

    Any Ideas?

    /A
  • 81. Re: Export Excel instead of .csv
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    SELECT * FROM table

    will not work. Use column names.

    Also, using column alias is not working with this version of package.

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.opal-consulting.de/training
    http://apex.oracle.com/pls/otn/f?p=31517:1
    -------------------------------------------------------------------
  • 82. Re: Export Excel instead of .csv
    628828 Newbie
    Currently Being Moderated
    Hi Denes,

    I've removed the alias and still I get the error:
    "Report Values Error: ORA-06550: line 1, column 187: PL/SQL: ORA-00936: missing expression ORA-06550: line 1, column 179: PL/SQL: SQL Statement ignored / -6550 / select from (select cust , sub_lob from gcra_request_data where request_id = 2491)"
    Where am I going wrong ?

    Thanks a lot !
    Adela
  • 83. Re: Export Excel instead of .csv
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    Adela,

    What is:

    select from (select cust , sub_lob from gcra_request_data where request_id = 2491) ?

    This is not a valid sql statement.

    Show me the complete sql you use in your report.

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.opal-consulting.de/training
    http://apex.oracle.com/pls/otn/f?p=31517:1
    -------------------------------------------------------------------
  • 84. Re: Export Excel instead of .csv
    M Tajuddin Journeyer
    Currently Being Moderated
    Hi Denes,
    Great work. It worked for me nice without any trouble.


    M Tajuddin
  • 85. Re: Export Excel instead of .csv
    666835 Newbie
    Currently Being Moderated
    Still getting an error written to the first cell of the resulting .xls -file.

    My select now looks like this instead of select *:

    SELECT pnr, signature, f_name, l_name, sales_time, a_sales, b_sales, c_sales, omr_id, omr_namn
    FROM employees
    WHERE INSTR (':' || :p1_hidden_item || ':', ':'||omr_id||':') > 0

    and I'm getting this:

    Report Values Error: ORA-06550: Zeile 1, Spalte 166: PL/SQL: ORA-00936: Ausdruck fehlt ORA-06550: Zeile 1, Spalte 158: PL/SQL: SQL Statement ignored / -6550 / select from (SELECT pnr, signature, f_name, l_name, sales_time, sj_sales, other_sales, tim_sales, bsaomr_id, bsaomr_namn FROM employees WHERE INSTR (':' || (SELECT v('P1_HIDDEN_ITEM') FROM DUAL) || ':', ':'||bsaomr_id||':') > 0)

    :p1_hidden_item is the container for the text string of values from a series of checkboxes (which omr_id's the user wants to view)

    Any ideas?

    Regards

    /A
  • 86. Re: Export Excel instead of .csv
    628828 Newbie
    Currently Being Moderated
    Hi Denes,
    This is what I was trying to explain .. that even though my sql for the report is
    select cust , sub_lob from request where request_id = 2491
    (I took a trivial example so to be sure my select is not to blame for the error)
    the error is
    select from (select cust , sub_lob from request where request_id = 2491)

    Thanks,
    Adela
  • 87. Re: Export Excel instead of .csv
    666835 Newbie
    Currently Being Moderated
    Adela, I had the same problem... but after replacing


    AND include_in_export = 'Yes'

    with

    AND NVL(include_in_export,'Yes') = 'Yes'

    as suggested by dario.bilic on page 6 of this thread...

    Mine works! Thanks Denes and Dario.

    One question... In a regular report I paste the code into link label (Download Excel)

    But if I want .xls for an interactive report? There is no link label -field... If I create a HTML region with the above href I cant get it to refer to my report region... Anyone?

    Edited by: user10388414 on 2008-okt-31 02:22
  • 88. Re: Export Excel instead of .csv
    628828 Newbie
    Currently Being Moderated
    Thank you and many thanks Denes and Dario!
    I got it working replacing that code.

    Any ideas if the code that needs to be modified to get it working for "Function Returning SQL type of report" is accesible as complexity?

    Thanks a lot!
    Adela

    Edited by: adela on Oct 31, 2008 2:40 AM
  • 89. Re: Export Excel instead of .csv
    663231 Journeyer
    Currently Being Moderated
    Adela and Andy,

    I’m facing the same problem. I’ve installed Denes’ package and I’m getting error:

    <pre>Report Values Error: ORA-06550: line 1, column 169: PL/SQL: ORA-00936: missing expression ORA-06550: line 1, column 161: PL/SQL: SQL Statement ignored / -6550 / select from (select id, code, display_value from type.number_type)</pre>

    My SQL query is very simple:

    <pre>select id, code, display_value from type.number_type</pre>

    Report is generated fine. I use APEX 3.0 with commercial DB.
    How have you solve this problem?

    Regards,
    Przemek
1 4 5 6 7 8 10 Previous Next