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:

Have you found the answer to your question? If not, you can discuss it with me in the comments section below or join my Q&A community OrclQA.com for developers and ask your question. It is FREE.

Vinish Kapoor

Follow

Hi, 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.

guest
19 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
shahid
shahid
8 months ago

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

Debraj
Debraj
7 months ago

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.

Debraj
Debraj
Reply to  Vinish Kapoor
7 months ago

Hi Vinish

I got the point of error.

Thanks
Debraj

Mary
Mary
6 months ago

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

Mary
Mary
Reply to  Vinish Kapoor
6 months ago

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?

Pedro
Pedro
Reply to  Vinish Kapoor
4 months ago

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!

Mathias Maciel
Mathias Maciel
4 months ago

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

Mathias Maciel
Mathias Maciel
Reply to  Vinish Kapoor
4 months ago

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

Patty
Patty
3 months ago

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.

Shubham Suryakant Rendalkar
Shubham Suryakant Rendalkar
3 months ago

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

Suriya
Suriya
Reply to  Shubham Suryakant Rendalkar
2 months ago

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

Gaurav Caprihan
Gaurav Caprihan
2 months ago

Great work Vinish…Thanks!

Natalia Müller
Natalia Müller
2 months ago

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.

Natalia Müller
Natalia Müller
Reply to  Vinish Kapoor
2 months ago

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