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.