Home/Questions/Q 4710
In Process
suriya
suriya

suriya

View Profile
  • 0
Contributor
Asked: 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 Answers

    1. Vinish

      Vinish

      View Profile
      Legendary

      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

    You must login to add an answer.