2 Replies Latest reply: Sep 19, 2011 9:59 AM by rdarlin2 RSS

    Export SQL query from link or button

    rdarlin2
      I want to list several 'canned' SQL queries on one page as buttons or links; and have the result of selection be an immediate extract/download to excel of the data.

      How can I best do this?

      I suppose I could create separate hidden regions with their own reports that could somehow be referenced in the link. I would like to avoid doing all that, if I can create a package or process that includes the SQL query and that can be called in the link.

      Thanks in advance,

      Rich

      Edit:
      Found 'Report Query' functionality in Apex v3.2.1: However I am unable to get this to work; tells me my printer is not set up. I don't want to print the detail, I want it to be downloaded into excel... I don't have a printer configured - & don't want to. The URL provided for download as attachment is:
      f?p=&APP_ID.:0:&SESSION.:PRINT_REPORT=reportname

      I also have no page 0.

      Help?

      Edited by: rdarlin2 on Sep 16, 2011 11:14 AM
        • 1. Re: Export SQL query from link or button
          fac586
          rdarlin2 wrote:
          I want to list several 'canned' SQL queries on one page as buttons or links; and have the result of selection be an immediate extract/download to excel of the data.

          How can I best do this?

          I suppose I could create separate hidden regions with their own reports that could somehow be referenced in the link. I would like to avoid doing all that, if I can create a package or process that includes the SQL query and that can be called in the link.
          10,000ft overview of one way to do this:

          <li>Create a package with private functions that return the SQL for each "canned query", and a public switch function that returns a query function based on an input parameter:
          create or replace package qry
          is
          
            function selector (p_req_qry in varchar2) return varchar2;
          
          end qry;
          /
          
          create or replace package body qry
          is
          
            function emp_qry return varchar2
            is
            begin
              return 'select * from emp';
            end emp_qry;
          
            function dept_qry return varchar2
            is
            begin
              return 'select * from dept';
            end dept_qry;
          
            function selector (p_req_qry in varchar2) return varchar2
            is
            begin
              return
                case p_req_qry
                  when 'EMP' then emp_qry()
                  when 'DEPT' then dept_qry()
                end;
            end selector;
          
          end qry;
          /
          <li>Create 2 pages
          <li>On page 2 create a standard SQL report region of type SQL Query (PL/SQL function body returning SQL query):

          Region Source
          return qry.selector(:request);
          *(o) Use Generic Column Names (parse query at runtime only)*

          Maximum number of generic report columns
          number of columns in your biggest query

          Report Template
          export: CSV

          <li>On page 1 create an HTML region (or use an APEX list) with a link for each query, where the REQUEST component of the URL is the parameter used to select a query in the <tt>qry.selector</tt> function:
          <ul>
            <li>&lt;a href="f?p=&APP_ID.:2:&SESSION.:EMP"&gt;Emp</a></li>
            <li>&lt;a href="f?p=&APP_ID.:2:&SESSION.:DEPT"&gt;Dept</a></li>
          </ul>
          However this begs the question: Why bother? What's Excel got that APEX hasn't?
          • 2. Re: Export SQL query from link or button
            rdarlin2
            fac586 -

            Thanks for the clear explaination and example - this will handle my needs just fine.

            In re-reading the Apex documentation regarding Report Query functionality, it is clear there is a requirement to have a print server in order to process the data into an attachment for download... similar to the built in ability to download the dataset from an Interactive Report. I may add the print server functionality as we move forward.

            The reason for the excercise is that we have a requirement to submit monthly reports of data as a snapshot in time, these need to be forwarded onward to users without access to the DB/app... so data must be extracted, and excel is a common tool for all users.

            I
            Rich