DBA Hub

📋Steps in this guide1/4

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.

oracle miscconfigurationintermediate
by OracleDba
11 views
1

HTML to CLOB

First we create a table to populate. Next we create a procedure to retrieve and store the HTML data. Finally we run the procedure with a suitable URL.

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
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;

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;
/

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>
2

Binary to BLOB

First we create a table to populate. Next we create a procedure to retrieve and store the binary data. Finally we run the procedure with a suitable URL.

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
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;

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;
/

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>
3

HTTPURITYPE

The HTTPURITYPE does most of the hard work for us, as shown in these examples. First the CLOB. Next the BLOB.

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
27
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/');

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');
4

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. 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 23ai Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
exec utl_http.set_wallet('file:/u01/wallet', null);

Comments (0)

Please to add comments

No comments yet. Be the first to comment!