Here I am giving an Oracle Apex example to download the CSV file on click of a button using PL/SQL procedure and Application Process. Follow these steps:
Download CSV File Using PL/SQL Procedure and Application Process in Oracle Apex
Create a database procedure which will return the CSV as the CLOB data, below is the example:
1. Create a PL/SQL Procedure
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 emp)));
o_Clobdata := l_Clob;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;2. Create an Application Process in Oracle Apex
In Oracle Apex, click on the Shared Components > Application Process and then click on the Create button. Then follow these steps:
Put the following PL/SQL code in the above code section:
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;After that click on the Next button and on the next screen click on the Create button to finish the wizard. Your application process has been created.
3. Create a Button on a Page in Oracle Apex
Now open a page in Page designer in Oracle Apex in which you want to add a button to download the CSV file.
Then do the right-click on the Region and click on the option Create Button.
Set the Action to Redirect to URL.
Paste the following URL in the URL target.
f?p=&APP_ID.:0:&SESSION.:APPLICATION_PROCESS=download_emp_csv:NO
Notice that we are calling the application process download_emp_csv, we just created in the second step.
Now save the changes and run the page. On click of the button, the CSV file will be download.
