Create application process in Oracle Apex

Oracle Apex - Download CSV Using PL/SQL Procedure

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.

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:

This Post Has 22 Comments

  1. shahid August 5, 2020

    this helps a lot. just facing one issue only, i have to download multiple csv from same page. How can i control it by passing some parameter in application on demand process

    1. Vinish Kapoor August 5, 2020

      The download is happening due to the Ajax process created in the 2nd step above. So if you need to download the multiple files then you have to repeat the PL/SQL code inside the BEGIN and END section in the Ajax process from where we calling the emp_csv database procedure.

  2. Debraj September 1, 2020

    Hi Vinish

    I implemented your plugin into one of my application and it works great. But when the data from the table is huge, it generates a plsql value or numeric error.

    1. Vinish Kapoor September 2, 2020

      You have to debug and tell me where the exactly error is occurring, in the db procedure or in the Ajax process. Then I would be able to suggest you.

      To debug, you can remove the exception part of the PL/SQL block from the DB procedure and then call it from database only by an anonymous PL/SQL block.

    2. Debraj September 2, 2020

      Hi Vinish

      I got the point of error.

      Thanks
      Debraj

  3. Mary September 30, 2020

    Hello. can we do it the same with a dropdown button? Each element in dropdown will download different files.

    1. Vinish Kapoor September 30, 2020

      Yes, you can do it.

      Create a dynamic action on change event for the drop-down.

      Set action type to execute JavaScript code. Then call the Ajax process using the URL from JavaScript code:

      javascript:window.open('f?p=&APP_ID.:0:&SESSION.:APPLICATION_PROCESS=download_emp_csv:NO', '_self');
    2. Mary October 8, 2020

      Thank you for your response. 

      I created an application process but I didn't used your code in above. I created the dynamic action with the javascript code you said. But the csv file is downloaded when the page loads and not by the button change or click event. In the dynamic action, I used the DOM Object in the selection type. 
      In DOM Object, I added the id of element.

      Am I doing something wrong?

    3. Pedro November 27, 2020

      Thanks for that. Strangely enough, the Redirect to URL method presented in the post is giving me a checksum error, while using it like this with JS works perfectly. (APEX 20.1)
      Thanks, great post!

  4. Mathias Maciel December 19, 2020

    Hi Vinish,

    I try to aproach same logic to download from OS directory file but I get ORA-06502: PL/SQL: error : invalid LOB locator specified: ORA-22275 numeric or value error.

    Here's the code:

    DECLARE
  L_BFILE          BFILE;
  V_MIME_TYPE      VARCHAR2(50) DEFAULT 'text/plain';
  L_BLOB           BLOB;
  L_DEST_OFFSET    INTEGER := 1;
  L_SRC_OFFSET     INTEGER := 1;
  L_LENGTH         INTEGER;
BEGIN
  L_BFILE := BFileName('DIR', 'demo.txt');


  Dbms_Lob.FILEOPEN(L_BFILE, DBMS_LOB.file_readonly);
  
  
  Dbms_Lob.CREATETEMPORARY(LOB_LOC => L_BLOB,
                           CACHE => FALSE);


  Dbms_Lob.LOADBLOBFROMFILE(DEST_LOB    => L_BLOB,
                            SRC_BFILE   => L_BFILE,
                            AMOUNT      => Dbms_Lob.GETLENGTH(L_BLOB),--Dbms_Lob.LOBMAXSIZE,
                            DEST_OFFSET => L_DEST_OFFSET,
                            SRC_OFFSET  => L_SRC_OFFSET);


  Dbms_Lob.FILECLOSE(L_BFILE);

  L_LENGTH := Dbms_Lob.GETLENGTH(L_BLOB);  


  Htp.FLUSH;
  Htp.INIT;


  OWA_UTIL.MIME_HEADER(V_MIME_TYPE, FALSE);


  Htp.P('Content-length: ' || L_LENGTH);
  Htp.P('Content-Disposition: attachment; filename="' || P_NOMBRE_ARCHIVO || '"');
  Htp.P('Set-Cookie: fileDownload=true; path=/');


  OWA_UTIL.HTTP_HEADER_CLOSE;


  WPG_DOCLOAD.DOWNLOAD_FILE(L_BLOB);


  --APEX_APPLICATION.STOP_APEX_ENGINE;
  
  EXCEPTION
    WHEN OTHERS THEN
      Dbms_Lob.FREETEMPORARY(L_BLOB);
      RAISE;
END;

    Thanks in advance,

    Mathias

    1. Vinish Kapoor December 20, 2020

      For DEST_LOB use CLOB type data to get the file contents, then convert it to BLOB using the DBMS_LOB.CONVERTTOBLOB method. The example is already given in the above post.

    2. Mathias Maciel December 21, 2020

      I updated code with following and now I get ORA-22994: source offset is beyond the end of the source lob:

        Dbms_Lob.LOADCLOBFROMFILE(DEST_LOB     => L_CLOB,
                            SRC_BFILE    => L_BFILE,
                            AMOUNT       => Dbms_Lob.LOBMAXSIZE,
                            DEST_OFFSET  => L_DEST_OFFSET,
                            SRC_OFFSET   => L_SRC_OFFSET,
                            BFILE_CSID   => Nls_Charset_Id('WE8MSWIN1252'),
                            LANG_CONTEXT => L_LANG_CONTEXT,
                            WARNING      => L_WARNING);
  Dbms_Lob.FILECLOSE(L_BFILE);
  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);    

      Something wrong with CONVERTTOBLOB procedure or previous lines?

      Mathias

  5. Patty January 14, 2021

    Thanks so much! This is a good starter for some process that I'm trying to implement. In our case we require the file to be downloaded to an specific location in the operating system. We also are required to pass input parameters for the select query.

  6. Shubham Suryakant Rendalkar January 20, 2021

    After clicking on button it just redirecting to that page bt csv in not downloading

    1. Suriya January 25, 2021

      Yes very useful to us. how to download multiple view/table data in one excel.

  7. Gaurav Caprihan January 30, 2021

    Great work Vinish...Thanks!

  8. Natalia Müller February 9, 2021

    Hello Vinish
    I hope you are doing well and thank you very much for sharing your knowledge.

    I was looking for information on how to implement sending an email with an html file from an IR report (APEX).

    There is already a functionality that does this IR → Actions → Download → Email, but it sends all records and I need to send only the records that are selected in the IR .
     
    So fortunately I ended up here on your website.

    My question is if you know of any way to modify this functionality that already exists, or set it up to do this, or if you have already developed something that can help me, etc.

    Thanks in advance.

    1. Vinish Kapoor February 9, 2021

      Hi Natalia,

      Here we usually discuss small topics, for major ones, please use our forum https://orclqa.com.

      So that other member or I would be able to help you.

    2. Natalia Müller February 9, 2021

      Ok Vinish. Will do. Thanks for the quick response.

  9. Anderson June 8, 2021

    Hello Vinish hope you're doing great, I have one question

    I'm using your method to download csv files, but when it comes to special characters (cyrillic alphabet) the csv looks wrong

    Any tip when it comes to special characters?

    Thanks in advance!

    1. Vinish Kapoor June 9, 2021

      Try to get those columns in double-quotes:

      SELECT empno||',' ||chr(34)||ename||chr(34)||...
  10. mos August 8, 2021

    thanks, it was helpful

Comments are closed.