Press "Enter" to skip to content

Oracle Apex – Download CSV Using PL/SQL Procedure

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:

Create application process in Oracle Apex

Application Process step -2

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.

See also:

Vinish Kapoor

I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me, it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub and get notifications for new posts.

You may also like:

Comments are closed.