Pulling Down BLOBs and CLOBs with a PL/SQL Function

(Revised)

The functions below will allow the user to reach out to any website or web server and grab any binary or text file.  The cool thing is that it is executed straight from the database.  The functions can be used to populated BLOB  or CLOB columns in a table (great for grabbing images and storing them ... mugshots .. personel photos etc.).  Here are the function:

CREATE OR REPLACE FUNCTION getWebBLOB(p_url VARCHAR2 := NULL) RETURN BLOB
IS

   v_data       UTL_HTTP.html_pieces;
   v_chunk      VARCHAR2(32000) := NULL;
   v_blob BLOB;

BEGIN

   v_data := utl_http.request_pieces(p_url);

   DBMS_LOB.createTemporary(v_blob,FALSE,DBMS_LOB.CALL);

   FOR piece_cnt IN 1 .. v_data.count LOOP

      v_chunk := v_data(piece_cnt);

      DBMS_LOB.writeappend(v_blob,length(v_chunk),utl_raw.cast_to_raw(v_chunk));

   END LOOP;

   RETURN v_blob;

END;
/

CREATE OR REPLACE FUNCTION getWebCLOB(p_url VARCHAR2 := NULL) RETURN CLOB
IS

   v_data       UTL_HTTP.html_pieces;
   v_chunk      VARCHAR2(32000) := NULL;
   v_clob       CLOB;

BEGIN

   v_data := utl_http.request_pieces(p_url);

   DBMS_LOB.createTemporary(v_clob,FALSE,DBMS_LOB.CALL);

   FOR piece_cnt IN 1 .. v_data.count LOOP

      v_chunk := v_data(piece_cnt);

      DBMS_LOB.writeappend(v_clob,length(v_chunk),v_chunk);

   END LOOP;

   RETURN v_clob;

END;
/

If you have any questions, shoot me an email.

7:15:45 PM