Example of downloading a pdf from web (https) and saving into disk using PL/SQL.

Some times before I have posted this example: http://saubbane.blogspot.co.uk/2010/12/example-of-downloading-pdf-from-web-and.html
But this will not work in case of https. So I have decided to post another example using UTL_HTTP. This can download any file with https also.

[oracle@localhost saubhik]$ pwd
/home/oracle/saubhik
[oracle@localhost saubhik]$ ls -l *.pdf
-rw-r--r-- 1 oracle oinstall 60055 Apr  6 14:03 TheLoveDare.pdf

[oracle@localhost saubhik]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 7 16:56:49 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> --My database version.
SQL> ----------------------
SQL> SELECT * FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> ed
Wrote file afiedt.buf

  1  /* This code is just a demo and can be wrriten more efficiently and in modularied way*/
  2  /*************************************************************************************/
  3  DECLARE
  4    lv_url VARCHAR2(500) := 'https://docs.oracle.com/cd/E11882_01/appdev.112/e25519.pdf';
  5    lc_return BLOB       := EMPTY_BLOB();
  6    ---Varriables declared for writing the LOB to pdf file --
  7    l_file UTL_FILE.FILE_TYPE;
  8    l_buffer RAW(32767);
  9    l_amount BINARY_INTEGER := 32767;
 10    l_pos INTEGER           := 1;
 11    l_blob BLOB             := EMPTY_BLOB();
 12    l_blob_len INTEGER;
 13    /* This procedure is used to get the file from web*/
 14    /*************************************************/
 15  PROCEDURE get_blob_from_https
 16    (
 17      pi_url  IN VARCHAR2,
 18      po_blob IN OUT NOCOPY BLOB)
 19              IS
 20    l_http_request UTL_HTTP.req;
 21    l_http_response UTL_HTTP.resp;
 22    l_http_buffer RAW(32767);
 23  BEGIN
 24    --Setting my wallet. I have downloaded the certificate and configured earlier.
 25    UTL_HTTP.SET_WALLET ('file:/u01/app/oracle/product/11.2.0/db_1/owm/wallets/oracle', 'oracle123');
 26    -- Make a HTTP request and get the response.
 27    l_http_request := UTL_HTTP.begin_request(pi_url);
 28    --UTL_HTTP.SET_HEADER(l_http_request, 'User-Agent', 'Mozilla/4.0');
 29    l_http_response := UTL_HTTP.get_response(l_http_request);
 30    BEGIN
 31      LOOP
 32        UTL_HTTP.read_raw(l_http_response, l_http_buffer, 32766);
 33        DBMS_LOB.writeappend (po_blob, utl_raw.length(l_http_buffer), l_http_buffer);
 34      END LOOP;
 35    EXCEPTION
 36    WHEN UTL_HTTP.end_of_body THEN
 37      UTL_HTTP.end_response(l_http_response);
 38    END;
 39  EXCEPTION
 40  WHEN UTL_HTTP.NETWORK_ACCESS_DENIED THEN
 41    dbms_output.put_line(' Check your ACL with DBMS_NETWORK_ACL_ADMIN or Your  Oracle wallet');
 42    RAISE;
 43  WHEN UTL_HTTP.TRANSFER_TIMEOUT THEN
 44    dbms_output.put_line('No data is read and a read timeout occurred');
 45    RAISE;
 46  WHEN OTHERS THEN
 47    UTL_HTTP.end_response(l_http_response);
 48    RAISE;
 49  END get_blob_from_https;
 50  -- End of the procedure.
 51  --- The main execution section begins.
 52  BEGIN
 53    dbms_lob.createtemporary(lc_return, true);
 54    --Get the pdf file from web.
 55    get_blob_from_https(lv_url, lc_return);
 56    l_buffer := NULL;
 57    /*****************************************************************************/
 58    -- Now lc_return is holding the file. You can insert in a table or whatever
 59    -- I am writing in a file using UTL_FILE.
 60    /*****************************************************************************/
 61    -- Open the destination file.
 62    l_file := UTL_FILE.FOPEN('SAUBHIK', 'e25519.pdf', 'wb');
 63    --Get the total length of the BLOB
 64    l_blob_len := DBMS_LOB.getlength(lc_return);
 65    -- Read chunks of the BLOB and write them to the file
 66    -- until complete.
 67    WHILE l_pos < l_blob_len
 68    LOOP
 69      DBMS_LOB.READ(lc_return, l_amount, l_pos, l_buffer);
 70      UTL_FILE.put_raw(l_file, l_buffer, FALSE);
 71      l_pos := l_pos + l_amount;
 72    END LOOP;
 73    -- Close the file.
 74    UTL_FILE.FCLOSE(l_file);
 75  EXCEPTION
 76  WHEN OTHERS THEN
 77    -- Close the file if something goes wrong.
 78    IF UTL_FILE.IS_OPEN(l_file) THEN
 79      UTL_FILE.FCLOSE(l_file);
 80    END IF;
 81    RAISE;
 82* END;
 83  /

PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@localhost saubhik]$ ls -l *.pdf
-rw-r--r-- 1 oracle oinstall 4916565 Apr  7 16:58 e25519.pdf
-rw-r--r-- 1 oracle oinstall   60055 Apr  6 14:03 TheLoveDare.pdf
[oracle@localhost saubhik]$