Create application process in Oracle Apex

Oracle Apex - Download CSV Using PL/SQL Procedure

by

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:

22 thoughts on “Oracle Apex - Download CSV Using PL/SQL Procedure”

  1. 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

    Reply

    • 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. 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.

    Reply

    • 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.

    • 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');

    • 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?

    • 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. 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

    Reply

    • 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.

    • 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. 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.

    Reply

  8. 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.

    Reply

  9. 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!

    Reply

    • Try to get those columns in double-quotes:

      SELECT empno||',' ||chr(34)||ename||chr(34)||...

Leave a Comment