Material Type: Notes;
Class: Database Systems;
Subject: Engineering Computer Science;
University: University of California - Davis;
Term: Unknown 1989;
Download HTTP Callouts from SQL and PL - Database Systems | ECS 165B and more Study notes Deductive Database Systems in PDF only on Docsity!
HTTP Callouts from SQL and PL/SQL
UTL HTTP PL/SQL Package:
allows HTTP callouts from PL/SQL and SQL
takes a URL as parameter and returns corresponding Web page
data is returned in the form of packages, each package is 2000 characters (varchar2(2000), Request)
all packages can also be obtained at once as a table type (Table of Varchar2(2000), Request Pieces)
PL/SQL Package Declaration:
create or replace package UTL_HTTP is function Request (url in varchar2) return varchar2; pragma restrict_references (Request, wnds, rnds, wnps, rnps);
type Html_Pieces is table of varchar2(2000) index by binary_integer;
function Request_Pieces (url in varchar2, max_pieces natural default 32767) return html_pieces; pragma restrict_references (Request_Pieces, wnds, rnds, wnps, rnps);
Init_Failed exception; Request_Failed exception; end UTL_HTTP;
Usage Examples, 1 (Request): Requests the first 2000 characters from the given URL.
SQL> select utl_http.request(’http://www.oracle.com/’) from dual;
UTL_HTTP.REQUEST(’HTTP://WWW.ORACLE.COM/’)
Oracle Corporation ...
Usage Examples, 2 (Request Pieces): Read a maximum of 100 pieces, each 2000 characters from the given URL. Count the number of pieces and their total length.
set serveroutput on / declare pieces utl_http.html_pieces; begin pieces := utl_http.request_pieces(’http://www.oracle.com/’, 100); dbms_output.put_line(pieces.count || ’ pieces were retrieved.’); dbms_output.put_line(’with total length ’); if pieces.count < 1 then dbms_output.put_line(’0’); else dbms_output.put_line((2000 * (pieces.count - 1))
length(pieces(pieces.count))); end if; end; /
create or replace package body Queue is procedure enQueue(url in varchar2) is begin q(qend) := url; qend := qend + 1; if qend >= qsize then qend := 0; end if; if qend = qstart then raise Queue_Overflow; end if; end;
function deQueue return varchar2 is pos binary_integer; begin if (qstart = qend) then raise Queue_Underflow; end if; pos := qstart; qstart := qstart + 1; if (qstart >= qsize) then qstart := 0; end if; return q(pos); end;
function isEmpty return integer is begin if (qstart = qend) then return 1; else return 0; end if; end; end Queue;
Other useful procedures and functions:
SET RESPONSE ERROR CHECK Sets whether or not get response raises an exception when the Web server returns a status code that indicates an error–a status code in the 4xx or 5xx ranges
SET TRANSFER TIMEOUT Sets the timeout value for UTL HTTP to read the HTTP response from the Web server or proxy server Session Settings Subprograms (Cookies, Redirect, Proxies... ) HTTP Requests Subprograms (Header, Body, Writes... ) HTTP Responses Subprograms (Header, Body, Reads... ) HTTP Cookies Subprograms (Clear, add, get... ) HTTP Persistent Connections Subprograms Error Conditions Subprograms
Exceptions:
Init Failed is raised if call fails because of the current system environment (available memory, etc.)
Request Failed is raised when the call fails because of an invalid URL or unreachable pages transfer timeout, too many requests, protocol error, bad argument, bad url
