8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Retrieving HTML and Binaries into Tables Over HTTP
This article gives two brief examples of how HTML pages and Binaries, such as images or documents, can be downloaded over HTTP and placed directly into tables. The code in this article uses two approaches. The first uses the UTL_HTTP and DBMS_LOB packages, while the second uses the HTTPURITYPE to achieve this.
Related articles.
- UTL_HTTP and SSL (HTTPS) using Oracle Wallets
- Transport Layer Security (TLS) Connections without a Client Wallet in Oracle Database 23c
HTML to CLOB
First we create a table to populate.
create table http_clob_test ( id number(10), url varchar2(255), data clob, constraint http_clob_test_pk primary key (id) ); create sequence http_clob_test_seq;
Next we create a procedure to retrieve and store the HTML data.
create or replace procedure load_html_from_url ( p_url in varchar2, p_username in varchar2 default null, p_password in varchar2 default null, p_wallet_path in varchar2 default null, p_wallet_password in varchar2 default null ) as l_http_request utl_http.req; l_http_response utl_http.resp; l_clob clob; l_text varchar2(32767); begin -- If using HTTPS, open a wallet containing the trusted root certificate. if p_wallet_path is not null and p_wallet_password is not null then utl_http.set_wallet('file:' || p_wallet_path, p_wallet_password); end if; -- Initialize the CLOB. dbms_lob.createtemporary(l_clob, FALSE); -- Make a HTTP request and get the response. l_http_request := utl_http.begin_request(p_url); -- Use basic authentication if required. if p_username is not null and p_password is not null then utl_http.set_authentication(l_http_request, p_username, p_password); end if; l_http_response := utl_http.get_response(l_http_request); -- Copy the response into the CLOB. begin loop utl_http.read_text(l_http_response, l_text, 32766); dbms_lob.writeappend (l_clob, length(l_text), l_text); end loop; exception when utl_http.end_of_body then utl_http.end_response(l_http_response); end; -- Insert the data into the table. insert into http_clob_test (id, url, data) values (http_clob_test_seq.nextval, p_url, l_clob); -- Relase the resources associated with the temporary LOB. dbms_lob.freetemporary(l_clob); exception when others then utl_http.end_response(l_http_response); dbms_lob.freetemporary(l_clob); raise; end load_html_from_url; /
Finally we run the procedure with a suitable URL.
exec load_html_from_url('http://localhost/'); PL/SQL procedure successfully completed. column url format a30 select id, url, dbms_lob.getlength(data) as length from http_clob_test; ID URL LENGTH ---------- ------------------------------ ---------- 1 http://localhost/ 1494 1 row selected. SQL>
Binary to BLOB
First we create a table to populate.
create table http_blob_test ( id number(10), url varchar2(255), data blob, constraint http_blob_test_pk primary key (id) ); create sequence http_blob_test_seq;
Next we create a procedure to retrieve and store the binary data.
create or replace procedure load_binary_from_url ( p_url in varchar2, p_username in varchar2 default null, p_password in varchar2 default null, p_wallet_path in varchar2 default null, p_wallet_password in varchar2 default null ) as l_http_request utl_http.req; l_http_response utl_http.resp; l_blob blob; l_raw raw(32767); begin -- If using HTTPS, open a wallet containing the trusted root certificate. if p_wallet_path is not null and p_wallet_password is not null then utl_http.set_wallet('file:' || p_wallet_path, p_wallet_password); end if; -- Initialize the BLOB. dbms_lob.createtemporary(l_blob, FALSE); -- Make a HTTP request and get the response. l_http_request := utl_http.begin_request(p_url); -- Use basic authentication if required. if p_username is not null and p_password is not null then utl_http.set_authentication(l_http_request, p_username, p_password); end if; l_http_response := UTL_HTTP.get_response(l_http_request); -- Copy the response into the BLOB. begin loop utl_http.read_raw(l_http_response, l_raw, 32766); dbms_lob.writeappend (l_blob, utl_raw.length(l_raw), l_raw); end loop; exception when utl_http.end_of_body then utl_http.end_response(l_http_response); end; -- Insert the data into the table. insert into http_blob_test (id, url, data) values (http_blob_test_seq.nextval, p_url, l_blob); -- Relase the resources associated with the temporary LOB. dbms_lob.freetemporary(l_blob); exception when others then utl_http.end_response(l_http_response); dbms_lob.freetemporary(l_blob); raise; end load_binary_from_url; /
Finally we run the procedure with a suitable URL.
exec load_binary_from_url('http://localhost/apache_pb.gif'); PL/SQL procedure successfully completed. column url format a30 select id, url, dbms_lob.getlength(data) as length from http_blob_test; ID URL LENGTH ---------- ------------------------------ ---------- 1 http://localhost/apache_pb.gif 2326 1 row selected. SQL>
HTTPURITYPE
The HTTPURITYPE does most of the hard work for us, as shown in these examples. First the CLOB.
create or replace procedure load_html_from_url (p_url in varchar2) as l_clob clob; begin l_clob := httpuritype.createuri(p_url).getclob(); -- Insert the data into the table. insert into http_clob_test (id, url, data) values (http_clob_test_seq.nextval, p_url, l_clob); end load_html_from_url; / exec load_html_from_url('http://localhost/');
Next the BLOB.
create or replace procedure load_binary_from_url (p_url in varchar2) as l_blob blob; begin l_blob := httpuritype.createuri(p_url).getblob(); -- Insert the data into the table. insert into http_blob_test (id, url, data) values (http_blob_test_seq.nextval, p_url, l_blob); end load_binary_from_url; / exec load_binary_from_url('http://localhost/apache_pb.gif');
HTTPS
To use a HTTPS URL we need to use create a wallet containing the root certificate of the URL, as described here.
This allows us to use the SET_WALLER procedure in the UTL_HTTP package to open the wallet for our session.
exec utl_http.set_wallet('file:/u01/wallet', null);
For more information see:
- UTL_HTTP
- DBMS_LOB
- HTTPURITYPE
- UTL_HTTP and SSL (HTTPS) using Oracle Wallets
- Transport Layer Security (TLS) Connections without a Client Wallet in Oracle Database 23c
Hope this helps. Regards Tim...