Sign Up

Hey, Dev!
Are you looking for a forum full of active developers to help you?
So if you want to:
➡️ Get answers for your development issues
➡️ Help others
➡️ Write an article
➡️ Get rewarded for your active participation
Then this place is just for you and it is 100% FREE.

Have an account? Sign In


Have an account? Sign In Now

Sign In

Forgot Password?

Don't have account, Sign Up Here

Forgot Password

Lost your password? Please enter your email address. You will receive a link and will create a new password via email.

Have an account? Sign In Now

Sorry, you do not have a permission to ask a question, You must login to ask question.

Forgot Password?

Need An Account, Sign Up Here
Sign InSign Up

OrclQA.Com

OrclQA.Com Logo OrclQA.Com Logo
Search
Ask A Question

Mobile menu

Close
Ask a Question
  • Home
  • Blog
  • New Questions
  • Tutorials
    • Oracle
    • Oracle Apex
    • Python
  • Tags
  • Users
  • Badges & Points
  • About
Home/Questions/Q 4710
In Process
suriya
suriya

suriya

  • 1 Question
  • 1 Answer
  • 0 Best Answers
  • 25 Points
View Profile
  • 0
suriyaContributor
Asked: January 25, 20212021-01-25T12:25:12+05:30 2021-01-25T12:25:12+05:30

how to download multiple view/table data in one excel

  • 0

how to download multiple view/table data in one excel from apex page by clicking button using application process(AJAX)

apex
  • 3
  • 77
  • 0
  • 0
  • Share
    • Share on Facebook
    • Share on Twitter
Answer

    3 Answers

    1. Vinish

      Vinish

      • 1 Question
      • 473 Answers
      • 46 Best Answers
      • 2k Points
      View Profile
      Vinish Legendary
      2021-01-25T12:54:57+05:30Added an answer on January 25, 2021 at 12:54 pm

      Below is an example to download the CSV file using the Ajax process. To download the file in XLSX format, you need a third party package to convert the query result into the Excel format. The CSV file will also open in Excel.
      Create a database procedure that will return the CLOB data type for the data return by the query having multiple tables:

      Create or replace PROCEDURE emp_Csv(o_Clobdata OUT CLOB) IS 
      l_Blob BLOB; 
      l_Clob CLOB; 
      
      BEGIN 
      
      Dbms_Lob.Createtemporary(Lob_Loc => l_Clob, 
      Cache => TRUE, 
      Dur => Dbms_Lob.Call); 
      SELECT Clob_Val 
      INTO l_Clob 
      FROM (SELECT Xmlcast(Xmlagg(Xmlelement(e, 
      Col_Value || Chr(13) || 
      Chr(10))) AS CLOB) AS Clob_Val, 
      COUNT(*) AS Number_Of_Rows 
      FROM (SELECT 'empno, ename, sal, mgrno, hiredate, deptno' AS Col_Value 
      FROM Dual 
      UNION ALL 
      SELECT empno||',' ||ename||','|| sal||','|| mgrno||','|| hiredate||','|| deptno AS Col_Value 
      FROM (SELECT empno, ename, sal, mgrno, hiredate, deptno from emp1
      union
      SELECT empno, ename, sal, mgrno, hiredate, deptno from emp2))); 
      
      o_Clobdata := l_Clob; 
      EXCEPTION 
      WHEN OTHERS THEN 
      NULL; 
      END;

      Then create an Ajax callback process in Oracle Apex to call the above procedure and convert CLOB to BLOB and download. Add the following code in the Ajax callback text area:

      DECLARE
      L_BLOB BLOB;
      L_CLOB CLOB;
      L_DEST_OFFSET INTEGER := 1;
      L_SRC_OFFSET INTEGER := 1;
      L_LANG_CONTEXT INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
      L_WARNING INTEGER;
      L_LENGTH INTEGER;
      BEGIN
      
      -- create new temporary BLOB
      DBMS_LOB.CREATETEMPORARY(L_BLOB, FALSE);
      
      --get CLOB
      emp_csv( L_CLOB);
      
      -- tranform the input CLOB into a BLOB of the desired charset
      DBMS_LOB.CONVERTTOBLOB( DEST_LOB => L_BLOB,
      SRC_CLOB => L_CLOB,
      AMOUNT => DBMS_LOB.LOBMAXSIZE,
      DEST_OFFSET => L_DEST_OFFSET,
      SRC_OFFSET => L_SRC_OFFSET,
      BLOB_CSID => NLS_CHARSET_ID('WE8MSWIN1252'),
      LANG_CONTEXT => L_LANG_CONTEXT,
      WARNING => L_WARNING
      );
      
      -- determine length for header
      L_LENGTH := DBMS_LOB.GETLENGTH(L_BLOB);
      
      -- first clear the header
      HTP.FLUSH;
      HTP.INIT;
      
      -- create response header
      OWA_UTIL.MIME_HEADER( 'text/csv', FALSE);
      
      HTP.P('Content-length: ' || L_LENGTH);
      HTP.P('Content-Disposition: attachment; filename="emp_data.csv"');
      HTP.P('Set-Cookie: fileDownload=true; path=/');
      
      OWA_UTIL.HTTP_HEADER_CLOSE;
      
      -- download the BLOB
      WPG_DOCLOAD.DOWNLOAD_FILE( L_BLOB );
      
      -- stop APEX
      -- APEX_APPLICATION.STOP_APEX_ENGINE;
      EXCEPTION
      WHEN OTHERS THEN
      DBMS_LOB.FREETEMPORARY(L_BLOB);
      RAISE;
      END;

      Now you can call the above Ajax callback process on the button click as follows:

      f?p=&APP_ID.:0:&SESSION.:APPLICATION_PROCESS=download_emp_csv:NO

      Where download_emp_csv is the Ajax process name.

      • 0
      • Share
        Share
        • Share on Facebook
        • Share on Twitter
      • suriya

        suriya

        • 1 Question
        • 1 Answer
        • 0 Best Answers
        • 25 Points
        View Profile
        suriya Contributor
        2021-01-28T17:37:56+05:30Replied to answer on January 28, 2021 at 5:37 pm

        Yes.. Thank you but how to add number of sheet in excel.. when downloading from apex on same format.

        • 0
        • Share
          Share
          • Share on Facebook
          • Share on Twitter
        • Vinish

          Vinish

          • 1 Question
          • 473 Answers
          • 46 Best Answers
          • 2k Points
          View Profile
          Vinish Legendary
          2021-01-28T17:42:01+05:30Replied to answer on January 28, 2021 at 5:42 pm

          To generate an Excel file with multiple sheets using a database procedure, you will have to use any third-party PL/SQL utility. An example is given in the following post:

          Export data into Excel from Oracle table using PL/SQL

          • 0
          • Share
            Share
            • Share on Facebook
            • Share on Twitter

    You must login to add an answer.

    Forgot Password?

    Sidebar

    Ask Question
    Write a Post
    • Recent
    • Answers
    • Bheem

      How to deploy oracle apex app in oracle SaaS

      • 0 Answers
    • Rameez Tariq

      How to create View with Blob column through DBLINK

      • 0 Answers
    • Huy Nguyen

      Dynamic action on column on Interactive Grid

      • 3 Answers
    • GudDud

      Load PDF into APEX, Edit it and Print it.

      • 0 Answers
    • Huy Nguyen
      Huy Nguyen added an answer Thank you for your good idea. March 5, 2021 at 12:09 pm
    • Vinish
      Vinish added an answer Oh, this will work for only new rows. For existing… March 5, 2021 at 11:22 am
    • Vinish
      Vinish added an answer I think you do not need to create the dynamic… March 5, 2021 at 11:14 am
    • Vinish
      Vinish added an answer Are you converting your CLOB to varchar2 in the report?… March 5, 2021 at 6:56 am
    • Vinish
      Vinish added an answer You can reduce the number of column span for the… March 4, 2021 at 7:53 am

    Recent Blog Posts

    • Vinish

      Oracle Apex - Display External File in Region

    • Vinish

      Oracle PL/SQL Download BLOB File to Disk

    • Vinish

      Oracle Convert External File to BLOB

    • Vinish

      Oracle Apex Calendar Example

    • Vinish

      Oracle Apex - Open Modal Dialog Page Using JavaScript

    Explore

    • Home
    • Blog
    • New Questions
    • Tutorials
      • Oracle
      • Oracle Apex
      • Python
    • Tags
    • Users
    • Badges & Points
    • About

    © 2021 OrclQA.Com. All Rights Reserved. Privacy Policy