Creating Download Utility using PLSQL

Let me start this article with an apology for not posting an article in such a long time. I have been involved in writing a book and it is eating most of my time so articles will be sparse in the next few months as well. Let’s however cut the negativity and enjoy the fun of creating a download utility using PLSQL

I have always been a fan of Jonathan Lewis and I found a place where I could download the scripts for one of his books. The number of scripts was huge and it would have been a boring and tiring job to download each script, individually. So I thought of writing this small procedure to download a batch of files together

I don’t know if the below website is authorized to distribute Jonathan Lewis’s work but I am going to use it to show the process of downloading a batch of files in a single go. This method of downloading is useful if you have a number of files to be downloaded and the size of each file is not huge.

http://www.vishalgupta.com/oracle/CostBased/book_cbo_scripts/

bring_file procedure (see the code at the end of the article) is used to download the files. It uses 2 file objects (f and f2). The 1^st file object (f) points to filename.txt which contains the names of the files which have to be downloaded. The 2^nd file object (f2) is a pointer to a file in which the data from the response object (resp) is written. The target of f2 changes in every iteration because we create a new file for every file name in filename.txt. We read filename.txt and for each line of filename.txt, we fire the URL to download the corresponding file. The file is 1^st stored in a response object called resp. The text in resp then read and put it in a new file. So a new file is created for every line in filename.txt and we get the entire set of files in the end of the execution of bring_file procedure.

Let’s say you wanted to download all the files in the below location

http://www.vishalgupta.com/oracle/CostBased/book_cbo_scripts/ch_02_tablescans/

You will first have to copy the file names and save these in a file called filename.txt. Your filename.txt will have the following

calc_mbrc.sql

cpu_costing.sql

hack_stats.sql

index_ffs.sql

parallel.sql

parallel_2.sql

partition.sql

partition_2.sql

partition_8.sql

plan_run81.sql

plan_run92.sql

pq_anomaly.sql

pq_anomaly.txt

set_system_stats.sql

setenv.sql

tablescan_01.sql

tablescan_01a.sql

tablescan_01b.sql

tablescan_02.sql

tablescan_03.sql

tablescan_04.sql

You then have to create bring_file procedure

CREATE OR REPLACE PROCEDURE bring_file

AS

   req          UTL_HTTP.req;

   resp         UTL_HTTP.resp;

   f            UTL_FILE.file_type;

   f2           UTL_FILE.file_type;

   l_tmp      VARCHAR (3000)     DEFAULT '';

   file_nam      VARCHAR (100) ;

   url          VARCHAR (3000)     DEFAULT '';

BEGIN

   f2 := UTL_FILE.fopen ('JL_SCRIPTS', 'filename.txt', 'r', 32767);

   LOOP

      BEGIN

         UTL_FILE.get_line (f2, file_nam);

         IF file_nam IS NULL

         THEN

            EXIT;

         END IF;

         url :=

               'http://www.vishalgupta.com/oracle/CostBased/book_cbo_scripts/ch_02_tablescans/'

            || file_nam;

            dbms_output.put_line(url);

         f := UTL_FILE.fopen ('JL_SCRIPTS', file_nam, 'w', 32767);

         req := UTL_HTTP.begin_request (url);

         resp := UTL_HTTP.get_response (req);

         LOOP

            BEGIN

               UTL_HTTP.read_text (resp, l_tmp);

            EXCEPTION

               WHEN OTHERS

               THEN

                  EXIT;

            END;

            UTL_FILE.put_line (f, l_tmp);

         END LOOP;

         UTL_FILE.fclose (f);

         UTL_HTTP.end_response (resp);

      EXCEPTION

         WHEN OTHERS

         THEN

         UTL_FILE.fclose (f);

         UTL_FILE.fclose (f2);

            EXIT;

      END;

   END LOOP;

   UTL_FILE.fclose (f);

   UTL_FILE.fclose (f2);

EXCEPTION

   WHEN UTL_HTTP.end_of_body

   THEN

      UTL_HTTP.end_response (resp);

      UTL_FILE.fclose (f);

      UTL_FILE.fclose (f2);

END;

/

Now you have to create an Oracle Directory and point it to the directory which holds filename.txt. I created the following directory

create directory JL_SCRIPTS as 'C:\';

Note that my filename.txt is also in C:\

Finally, open SQL* PLUS and run the following

exec bring_file;

This will download all the files, mentioned in filename.txt, in C:\

If you wish to download the files in the other directories in

http://www.vishalgupta.com/oracle/CostBased/book_cbo_scripts/

then you have to copy the name of the files in that directory and put them in filename.txt. You also have to change the value in URL variable and recompile bring_file procedure. Then you just have to execute bring_file again and all the files will be downloaded.

Hope you like this. I pledge to put another article as soon as I find some time.

Updated: 28th - Oct - 2012

Another method to download can be using HTTPURI

declare
p_url VARCHAR2 (2000) := 'http://atlanticadaptation.ca/sites/discoveryspace.upei.ca.acasa/files/pdftest.pdf';
l_blob blob;
begin
DBMS_LOB.createtemporary(l_blob, FALSE);
l_blob := HTTPURITYPE.createuri(p_url).getblob();
owa_util.mime_header('application/pdf',true);
htp.p('Content-Disposition: attachment; filename=abc.pdf');
htp.p('Content-length: ' || dbms_lob.getlength(l_blob));
owa_util.http_header_close;
wpg_docload.download_file(l_blob);
end;