DBA Hub

📋Steps in this guide1/5

Download Files Using PL/SQL and a Gateway (ORDS, mod_plsql, EPG)

This article gives some examples of the PL/SQL needed to download files using a PL/SQL gateway, such as ORDS, mod_plsql or the embedded PL/SQL gateway (EPG).

oracle miscconfigurationintermediate
by OracleDba
15 views
1

Background

Since Oracle 8i there have been a number of ways to download files using PL/SQL and a gateway. The way you call these will depend on the gateway used and will also vary slightly if you are using APEX to make the call. The following articles explain the gateway part of the puzzle. The rest of this article is focused on the PL/SQL code required to initiate the download of a file, based on various sources.
2

BFILE

In this example we source the file from the database file system using a directory object. With the directory object in place, we can now use a to read the file and push it out over the gateway using the package.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE OR REPLACE DIRECTORY file_dir AS '/path/to/my/images/';
GRANT READ ON DIRECTORY file_dir TO apex_ws_user;

CREATE OR REPLACE PROCEDURE get_file (p_dir        IN  VARCHAR2,
                                      p_file_name  IN  VARCHAR2,
                                      p_mime_type  IN  VARCHAR2) IS
  l_bfile BFILE;
BEGIN
  l_bfile := BFILENAME(p_dir, p_file_name);
  sys.OWA_UTIL.mime_header(p_mime_type, FALSE);
  sys.HTP.p('Content-Length: ' || DBMS_LOB.getlength(l_bfile));
  sys.HTP.p('Content-Disposition: filename="' || p_file_name || '"');
  sys.OWA_UTIL.http_header_close;

  sys.WPG_DOCLOAD.download_file(l_bfile);
EXCEPTION
  WHEN OTHERS THEN
    HTP.p('Whoops');
END;
/
3

BLOB

In this example the media is stored in a column of a table, which also include a column.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE OR REPLACE PROCEDURE get_file (p_file_name  IN VARCHAR2) IS
  l_blob_content  documents.blob_content%TYPE;
  l_mime_type     documents.mime_type%TYPE;
BEGIN
  SELECT blob_content,
         mime_type
  INTO   l_blob_content,
         l_mime_type
  FROM   documents
  WHERE  file_name = p_file_name;

  sys.OWA_UTIL.mime_header(l_mime_type, FALSE);
  sys.HTP.p('Content-Length: ' || DBMS_LOB.getlength(l_blob_content));
  sys.HTP.p('Content-Disposition: filename="' || p_file_name || '"');
  sys.OWA_UTIL.http_header_close;

  sys.WPG_DOCLOAD.download_file(l_blob_content);
EXCEPTION
  WHEN OTHERS THEN
    HTP.p('Whoops');
END;
/
4

Programatically

In some cases, you want to create the contents of a file programatically and just push it through the gateway using the package. This example pushes out some columns of the table in CSV format.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE OR REPLACE PROCEDURE get_bfile (p_file_name  IN  VARCHAR2,
                                       p_mime_type  IN  VARCHAR2) IS
  l_clob CLOB     := '';                                       
  l_offset NUMBER := 1;
  l_chunk  NUMBER := 3000;
BEGIN
  FOR cur_rec IN (SELECT empno || ',"' || ename || '"' as data FROM emp) LOOP
    l_clob := l_clob || cur_rec.data || CHR(10);
  END LOOP;

  sys.OWA_UTIL.mime_header(p_mime_type, FALSE);
  sys.HTP.p('Content-Length: ' || DBMS_LOB.getlength(l_bfile));
  sys.HTP.p('Content-Disposition: filename="' || p_file_name || '"');
  sys.OWA_UTIL.http_header_close;

  -- Handle CLOBS larger than 32K.
  LOOP
    EXIT WHEN l_offset > LENGTH(p_clob);
    HTP.prn(SUBSTR(p_clob, l_offset, l_chunk));
    l_offset := l_offset + l_chunk;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    HTP.p('Whoops');
END;
/
5

APEX

The previous examples all work when called from APEX, but you will often see two extra lines included. The example below is a repeat of the example, but includes the extra APEX related lines. These could be added to the other examples if required. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE OR REPLACE PROCEDURE get_file (p_file_name  IN VARCHAR2) IS
  l_blob_content  documents.blob_content%TYPE;
  l_mime_type     documents.mime_type%TYPE;
BEGIN
  SELECT blob_content,
         mime_type
  INTO   l_blob_content,
         l_mime_type
  FROM   documents
  WHERE  file_name = p_file_name;
sys.HTP.init;
sys.OWA_UTIL.mime_header(l_mime_type, FALSE);
  sys.HTP.p('Content-Length: ' || DBMS_LOB.getlength(l_blob_content));
  sys.HTP.p('Content-Disposition: filename="' || p_file_name || '"');
  sys.OWA_UTIL.http_header_close;

  sys.WPG_DOCLOAD.download_file(l_blob_content);
apex_application.stop_apex_engine;
EXCEPTION
  WHEN apex_application.e_stop_apex_engine
    THEN RAISE;
  WHEN OTHERS THEN
    HTP.p('Whoops');
END;
/

Comments (0)

Please to add comments

No comments yet. Be the first to comment!