Допустим, вы хотите загрузить данные с сайта своего бизнес-партнера. Существует много способов выборки веб-страниц.
- «Ручная выборка», то есть ввод нужного адреса в браузере.
- Использование сценарного языка — например,
Perl
. Кстати говоря, этот язык содержит много всевозможных вспомогательных средств, упрощающих интерпретацию загруженных данных. - Использование утилиты командной строки — например,
GNU wget
. - Использование встроенного пакета
Oracle UTL_HTTP
.
Так как мой блог посвящен PL/SQL
, то мы будем рассматривать последний метод, конечно.
В Oracle11g Release 2
и выше вам для этого придется создать сетевой список ACL
для настройки исходящих подключений к нужным удаленным хостам (см. вот этот пост).
Начнем с относительно простого способа программирования выборки данных с веб-страницы — «нарезки» веб-страницы на фрагменты, сохраняемые в элементах массива. Этот способ использовался в Oracle
до поддержки CLOB
.
Фрагментная загрузка страницы
Одна из первых процедур пакета UTL_HTTP
загружала веб-страницу в последовательные элементы ассоциативного массива:
DECLARE
page_pieces UTL_HTTP.html_pieces; -- array of VARCHAR2(2000)
BEGIN
page_pieces := UTL_HTTP.request_pieces(url => 'http://www.oreilly.com/');
END;
Работать с данными в таком формате было неудобно, потому что границы 2000-байтовых фрагментов совершенно не совпадали с текстом страницы. Таким образом, если алгоритм разбора данных базировался на построчной обработке, строки приходилось читать из массива и собирать заново. Кроме того, по данным Oracle
, не все (не завершающие) фрагменты дополняются до 200 байт; алгоритм Oracle
не использует границы строк как точки разбиения; и максимальное количество фрагментов равно 32 767.
Впрочем, даже если этот алгоритм удовлетворял потребностям программиста, с некоторыми сайтами приведенный код не работал. Например, некоторые сайты отказывались предоставлять данные такому сценарию, потому что используемый по умолчанию Oracle
заголовок HTTP
был неизвестен веб-серверу. В частности, заголовок «User-Agent
» содержит текстовую строку с информацией о браузере, используемом (или эмулируемом) клиентом, а многие сайты предоставляют контент, ориентированный на конкретные браузеры. По умолчанию программы Oracle
не передавали заголовок «User-Agent
», который мог выглядеть примерно так:
User-Agent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)
Дополнительная передача этого заголовка увеличивает сложность кода, потому что программисту приходилось работать на более низком уровне: он должен был инициировать «запрос», передать заголовок, получить «ответ» и загружать страницу в цикле:
DECLARE
req UTL_HTTP.req; -- a "request object" (actually a PL/SQL record)
resp UTL_HTTP.resp; -- a "response object" (also a PL/SQL record)
buf VARCHAR2(32767); -- buffer to hold data from web page
BEGIN
req := UTL_HTTP.begin_request('http://www.oreilly.com/',
http_version => UTL_HTTP.http_version_1_1);
UTL_HTTP.set_header(req, 'User-Agent'
, 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)');
resp := UTL_HTTP.get_response(req);
BEGIN
LOOP
UTL_HTTP.read_text(resp, buf);
-- process buf here; e.g., store in array
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body
THEN
NULL;
END;
UTL_HTTP.end_response(resp);
END;
Центральное место в этом коде занимает следующая встроенная процедура:
PROCEDURE UTL_HTTP.read_text(
r IN OUT NOCOPY UTL_HTTP.resp,
data OUT NOCOPY VARCHAR2 CHARACTER SET ANY_CS,
len IN PLS_INTEGER DEFAULT NULL);
Если параметр len
содержит NULL
, Oracle
заполняет буфер до максимального размера вплоть до достижения конца страницы, после чего операция чтения выдает исключение UTL_HTTP.end_of_body
(да, это противоречит нормальной практике программирования, в соответствии с которой обычные операции не должны инициировать исключения). При каждой итерации обрабатывается содержимое очередного прочитанного фрагмента — например, оно присоединяется к объекту LOB
.
Данные также можно выбирать построчно — для этого вместо READ_TEXT
используется процедура READ_LINE
:
PROCEDURE UTL_HTTP.read_line(r IN OUT NOCOPY UTL_HTTP.resp,
data OUT NOCOPY VARCHAR2 CHARACTER SET ANY_CS,
remove_crlf IN BOOLEAN DEFAULT FALSE);
Эта встроенная процедура читает одну строку исходного текста (по желанию программиста — с отсечением символов-завершителей). Недостаток READ_LINE
заключается в том, что каждая строка, прочитанная с сервера HTTP
, должна иметь длину менее 32 767 байт. Используйте процедуру READ_LINE
только в том случае, если вы твердо уверены, что это ограничение не создаст проблем.
Загрузка страницы в объект LOB
Чтение данных по фрагментам или по строкам может привести к различным конфликтам ограничения длины, поэтому чтение данных в объекты LOB
выглядит более логично. И снова Oracle предоставляет очень простую процедуру, которой во многих случаях оказывается достаточно. Вся страница загружается в простую структуру данных при помощи встроенного объектного типа HTTPURITYPE
:
DECLARE
text CLOB;
BEGIN
text := HTTPURITYPE('http://www.oreilly.com').getclob;
END;
Загрузка двоичного файла в объект BLOB
осуществляется методом getblob
():
DECLARE
image BLOB;
BEGIN
image :=
HTTPURITYPE('www.oreilly.com/catalog/covers/oraclep4.s.gif').getblob;
END;
Конструктор HTTPURITYPE
предполагает, что при передаче данных используется транспортный протокол HTTP
, поэтому префикс «http://
» можно не указывать. К сожалению, эта встроенная функция не поддерживает HTTPS
и не позволяет передавать пользовательские заголовки «User-Agent
».
Загрузка данных в LOB
средствами UTL_HTTP
осуществляется так:
/* File on web: url_to_clob.sql */
DECLARE
req UTL_HTTP.req;
resp UTL_HTTP.resp;
buf VARCHAR2(32767);
pagelob CLOB;
BEGIN
req := UTL_HTTP.begin_request('http://www.oreilly.com/',
http_version => UTL_HTTP.http_version_1_1);
UTL_HTTP.set_header(req, 'User-Agent', 'Mozilla/4.0 (compatible;
MSIE 6.0; Windows NT 5.1)');
resp := UTL_HTTP.get_response(req);
DBMS_LOB.createtemporary(pagelob, TRUE);
BEGIN
LOOP
UTL_HTTP.read_text(resp, buf);
DBMS_LOB.writeappend(pagelob, LENGTH(buf), buf);
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body
THEN
NULL;
END;
UTL_HTTP.end_response(resp);
...here is where you parse, store, or otherwise process the LOB...
DBMS_LOB.freetemporary(pagelob);
END;
Аутентификация HTTP
Хотя многие сайты (например, Amazon или Ebay) выполняют вход и аутентификацию с применением нестандартных форм HTML
, остается еще немало сайтов, использующих аутентификацию HTTP
, также называемую базовой аутентификацией. Такие сайты легко узнать по поведению браузера, в котором открывается модальное диалоговое окно с предложением ввести имя пользователя и пароль.
Иногда ввод данных в окне удается обойти — для этого имя пользователя и пароль включаются в URL-адрес в следующей форме (хотя официальные стандарты не рекомендуют использовать этот метод): http://username:Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в вашем браузере должен быть включен Javascript.
Этот синтаксис поддерживается как пакетом UTL_HTTP
, так и объектным типом HTTPURITYPE
(по крайней мере с версии 9.2.0.4). Простой пример:
DECLARE
webtext clob;
user_pass VARCHAR2(64) := 'bob:swordfish'; -- replace with your own
url VARCHAR2(128) := 'www.encryptedsite.com/cgi-bin/login';
BEGIN
webtext := HTTPURITYPE(user_pass || '@' || url).getclob;
END;
/
Если шифрование имени пользователя и пароля в URL
-адресе не работает, можно попробовать другой способ:
...
req := UTL_HTTP.begin_request('http://some.site.com/');
UTL_HTTP.set_authentication(req, 'bob', 'swordfish');
resp := UTL_HTTP.get_response(req);
...
Он сработает в том случае, если сайт не шифрует страницу входа.
Загрузка зашифрованной страницы (HTTPS)
Хотя тип HTTPURITYPE
не поддерживает загрузку даных через протокол SSL
, UTL_HTTP
решит эту задачу при наличии электронного бумажника Oracle
— файла, содержащего сертификаты безопасности (а также, возможно, пары открытых и закрытых ключей). Для загрузки данных HTTPS
понадобится первое, то есть сертификаты. Вы можете сохранить один или несколько электронных бумажников в файловой системе сервера базы данных или в службе каталогов LDAP
.
Чтобы создать электронный бумажник, необходимо запустить графическую программу Oracle
под названием Oracle Wallet Manager
; на компьютерах Unix/ Linux
она обычно называется owm
, а в Microsoft Windows
вызывается из меню Пуск > Oracle
. Создав бумажник, попробуйте выполнить следующий фрагмент:
DECLARE
req UTL_HTTP.req;
resp UTL_HTTP.resp;
BEGIN
UTL_HTTP.set_wallet('file:/oracle/wallets', 'password1');
req := UTL_HTTP.begin_request('https://www.entrust.com/');
UTL_HTTP.set_header(req, 'User-Agent', 'Mozilla/4.0');
resp := UTL_HTTP.get_response(req);
UTL_HTTP.end_response(resp);
END;
Если вы не получите сообщение об ошибке, радуйтесь — такое решение должно работать, потому что Entrust
относится к числу немногих организаций, сертификаты которых включаются по умолчанию при создании электронного бумажника.
Если вы хотите загрузить данные из другого узла HTTPS
, открытый сертификат которого не входит в список Oracle
, снова запустите Oracle Wallet Manager
, импортируйте сертификат в файл и снова разместите его на сервере. Чтобы загрузить сертификат в формате, пригодном к использованию, запустите Microsoft Internet Explorer
и выполните следующие действия:
- Откройте в браузере (
Microsoft IE
) сайт - Сделайте двойной щелчок на желтом значке с изображением замка в правом нижнем углу окна.
- Выберите команду
Details
>Copy to File
. - Выполните инструкции по экспортированию сертификата в кодировке
base64
. Или, если на вашем компьютере установлен пакетOpenSSL
(как правило, в системахUnix
/Linux
), выполните следующую команду:
echo '' | openssl s_client -connect host:port
Команда выдает обширную информацию в stdout; просто сохраните текст между строками BEGIN CERTIFICATE
и END CERTIFICATE
(включительно) в файле. Кстати говоря, HTTPS
обычно использует порт 443.
При наличии сертификата вы можете выполнить следующие действия:
- Откройте
Oracle Wallet Manager
. - Откройте файл электронного бумажника.
- Импортируйте сертификат из только что созданного файла.
- Сохраните файл электронного бумажника и отправьте его на сервер базы данных.
Помните, что сертификаты окажутся в электронном бумажнике Oracle
только после их импортирования через Oracle Wallet Manager
. Разумеется, электронный бумажник может содержать несколько сертификатов, а в каталоге могут храниться несколько электронных бумажников.
Передача данных методами GET и POST
Еще одна распространенная задача — получение данных от сайта так, словно вы заполнили форму в браузере и нажали кнопку Submit
. В этом разделе приведены некоторые примеры использования UTL_HTTP
для решения этой задачи, но многие сайты весьма капризны, и чтобы передача данных работала правильно, придется изрядно потрудиться. Некоторые навыки и инструменты, которые пригодятся вам в ходе анализа поведения сайтов:
- Хорошее знание исходного кода
HTML
(особенно в области формHTML
) и, возможно,JavaScript
. - Режим просмотра исходного кода в браузере.
- Программы типа
GNU wget
, позволяющие легко опробовать разныеURL
-адреса с возможностью просмотра обычно скрытых взаимодействий между клиентом и сервером (ключ -d). - Подключаемые модули для браузера — такие, как
Web Developer
Криса Педерика (Chris Pederick)
илиTamper Data
Адама Джадсона (Adam Judson) для браузеров на базеMozilla
.
Начнем с простого кода, который может использоваться для запросов к Google.
На главной странице Google используется одна форма HTML
:
<form action=/search name=f>
Поскольку тег method
отсутствует, по умолчанию используется метод GET
. Единственное текстовое поле q на форме обладает следующими свойствами (среди прочих):
<input autocomplete="off" maxLength=2048 size=55 name=q value="">
Запрос GET
можно закодировать непосредственно в URL
-адресе:
http://www.google.com/search?q=query
Располагая такой информацией, мы можем написать следующий программный эквивалент поиска строки "oracle pl/sql programming
" (включая двойные кавычки) в Google
:
DECLARE
url VARCHAR2(64)
:= 'http://www.google.com/search?q=';
qry VARCHAR2(128) := UTL_URL.escape('"oracle pl/sql programming"', TRUE);
result CLOB;
BEGIN
result := HTTPURITYPE(url || qry).getclob;
END;
Удобная функция Oracle UTL_URL.ESCAPE
заменяет специальные символы в строке запроса их шестнадцатеричными эквивалентами. Преобразованный текст в данном примере принимает следующий вид:
%22oracle%20pl%2Fsql%20programming%22
Давайте рассмотрим пример использования POST
в чуть менее тривиальной ситуации. Анализ исходного кода HTML
сайта http://www.apache.org
показал, что в качестве «действия» формы поиска задано обращение к http://search.apache.org
, что форма использует метод POST
, а полю поиска присвоено имя query. При использовании метода POST
данные не удастся присоединить к URL
; вместо этого они должны пересылаться веб-серверу в определенной форме. Следующий блок выдает запрос POST
на поиск строки Oracle pl/ sql
(основные изменения выделены жирным шрифтом):
DECLARE
req UTL_HTTP.req;
resp UTL_HTTP.resp;
qry VARCHAR2(512) := UTL_URL.escape('query=oracle pl/sql');
BEGIN
req :=
UTL_HTTP.begin_request('http://search.apache.org/', 'POST', 'HTTP /1.0');
UTL_HTTP.set_header(req, 'User-Agent', 'Mozilla/4.0');
UTL_HTTP.set_header(req, 'Host', 'search.apache.org');
UTL_HTTP.set_header(req, 'Content-Type', 'application/x-www-form-urlencoded');
UTL_HTTP.set_header(req, 'Content-Length', TO_CHAR(LENGTH(qry)));
UTL_HTTP.write_text(req, qry);
resp := UTL_HTTP.get_response(req);
...Построчная обработка результатов...
UTL_HTTP.end_response(resp);
END;
В двух словах: в BEGIN_REQUEST
включается директива POST
, а метод write_text
используется для передачи данных формы. Хотя POST
не позволяет присоединять пары «имя/ значение» в конец URL
-адреса (в отличие от запросов GET
), данный сайт поддерживает тип контента x-www-form-urlencoded
с включением пар «имя/значение» в отправляемую серверу переменную qry
.
В этом примере встречается один дополнительный заголовок, который не используется в других примерах:
UTL_HTTP.set_header(req, 'Host', 'search.apache.org');
Без этого заголовка сайт Apache
выдает свою главную страницу вместо поисковой. Заголовок «Host
» необходим для сайтов с «виртуальными хостами» (то есть двумя и более именами хостов, представленным одним IP-адресом), чтобы веб-сервер знал, какую информацию вы запрашиваете. К счастью, передача заголовка «Host
» не создает никакого риска даже в том случае, если удаленный сайт не поддерживает виртуальные хосты. Кстати говоря, если форма содержит несколько заполняемых элементов, по правилам кодирования URL
пары «имя/значение» должны разделяться символами &:
name1=value1&name2=value2&name3= ...
Итак, GET
и POST
успешно работают, можно переходить к получению данных... верно? Возможно. Скорее всего, ваш код рано или поздно столкнется с механизмом «перенаправления» HTTP
. Это специальный код, который возвращается веб-сервером и означает: «Извините, но для этого вам нужно перейти в другое место». Все мы привыкли, что браузеры выполняют перенаправление автоматически и без лишних хлопот, однако на самом деле реализация может быть весьма непростой: существует минимум пять разных видов перенаправления, для которых действуют несколько различающиеся правила относительно того, что «можно» делать браузеру. Перенаправление может встретиться на любой веб-странице, но во многих случаях можно воспользоваться функциональностью отслеживания перенаправлений utl_http
:
UTL_HTTP.set_follow_redirect (max_redirects IN PLS_INTEGER DEFAULT 3);
К сожалению, в процессе тестирования кода для загрузки страницы с прогнозом погоды с сайта Национальной метеорологической службы США я обнаружил, что сервер реагирует на POST
кодом 302 — странный особый случай в стандарте HTTP
, который означает, что клиент не должен следовать за перенаправлением... и пакет utl_http
соблюдает «букву» стандарта — по крайней мере в этом случае.
Рис. 1. Получение сводки погоды с сайта NOAA
требует
Итак, чтобы получить полезную информацию о погоде, пришлось проигнорировать стандарт. Итоговая версия моей программы для получения погодной сводки в Севастополе (штат Калифорния) выглядит так:
/* File on web: orawx.sp */
PROCEDURE orawx
AS
req UTL_HTTP.req;
resp UTL_HTTP.resp;
line VARCHAR2(32767);
formdata VARCHAR2(512) := 'inputstring=95472'; -- zip code
newlocation VARCHAR2(1024);
BEGIN
req := UTL_HTTP.begin_request('http://www.srh.noaa.gov/zipcity.php',
'POST', UTL_HTTP.http_version_1_0);
UTL_HTTP.set_header(req, 'User-Agent', 'Mozilla/4.0');
UTL_HTTP.set_header(req, 'Content-Type', 'application/x-www-form-urlencoded');
UTL_HTTP.set_header(req, 'Content-Length', TO_CHAR(LENGTH(formdata)));
UTL_HTTP.write_text(req, formdata);
resp := UTL_HTTP.get_response(req);
IF resp.status_code = UTL_HTTP.http_found
THEN
UTL_HTTP.get_header_by_name(resp, 'Location', newlocation);
req := UTL_HTTP.begin_request(newlocation);
resp := UTL_HTTP.get_response(req); END IF;
...Обработка страницы, как и прежде...
UTL_HTTP.end_response(resp);
END;
На рис. 1 представлена схема взаимодействий между кодом и сервером. Не знаю, насколько часто встречается эта проблема. Моя «заплатка» не является универсальным решением для любых перенаправлений, но она дает представление о странностях, с которыми вы можете столкнуться при написании такого кода.
Запрет и долгосрочное хранение cookie
Плохо ли, хорошо ли, но поддержка cookie
на уровне сеанса включена по умолчанию в последних версиях UTL_HTTP
. Oracle по умолчанию разрешает до 20 cookie
на сайт и до 300 cookie
на сеанс. Чтобы проверить, относятся ли эти ограничения к вашей версии UTL_HTTP
, используйте следующий фрагмент:
DECLARE
enabled BOOLEAN;
max_total PLS_INTEGER;
max_per_site PLS_INTEGER;
BEGIN
UTL_HTTP.get_cookie_support(enabled, max_total, max_per_site);
IF enabled
THEN
DBMS_OUTPUT.PUT('Allowing ' || max_per_site || ' per site');
DBMS_OUTPUT.PUT_LINE(' for total of ' || max_total || ' cookies. ');
ELSE
DBMS_OUTPUT.PUT_LINE('Cookie support currently disabled.');
END IF;
END;
Поддержка cookie
прозрачна; Oracle
автоматически сохраняет cookie в памяти и отправляет их серверу по запросу.
Cookie
исчезают при завершении сеанса. Если вы предпочитаете продлить срок их существования, сохраните их в таблицах Oracle
и восстановите в начале нового сеанса. Для этого обратитесь к примерам кода, предоставляемого Oracle
в разделе UTL_HTTP
руководства Packages and Types
.
Чтобы полностью отключить поддержку cookie для всех запросов UTL_HTTP
для всех последующих запросов сеанса, используйте следующий код: UTL_HTTP.set_cookie_support
(FALSE
);
Запрет cookie для конкретного запроса реализуется следующим образом:
UTL_HTTP.set_cookie_support (FALSE);
Запрет cookie для конкретного запроса реализуется следующим образом:
UTL_HTTP.set_cookie_support(req, FALSE);
Чтобы изменить количество cookie
в значениях по умолчанию, выполните команду:
UTL_HTTP.set_cookie_support(TRUE,
max_cookies => n,
max_cookies_per_site => m);
загрузка данных с сервера FTP
Oracle не предоставляет встроенной поддержки загрузки данных с сайтов FTP
из кода PL/SQL
. Впрочем, если вам потребуется отправить или загрузить файлы по протоколу FTP
, в Интернете можно найти несколько готовых решений. Я видел как минимум три разных пакета, авторами которых были Барри Чейз (Barry Chase), Тим Холл (Tim Hall) и Крис Пул (Chris Poole). Эти реализации обычно используют пакеты UTL_TCP
и UTL_FILE
(и возможно, Java
) и поддерживают большую часть стандартных операций FTP
. Ссылки на некоторые реализации размещены на сайте PLNet.org
.
Кроме того, некоторые прокси-серверы поддерживают загрузку FTP
на базе запросов HTTP
от клиента; возможно, это позволит вам обойтись без полноценного пакета FTP
.
Использование прокси-сервера
По разным причинам в корпоративных сетях весь трафик часто передается через прокси-сервер. К счастью, в Oracle такой сценарий предусмотрен в UTL_HTTP
. Например, если прокси-сервер использует порт 8888 по адресу 10.2.1.250, вы можете использовать следующий код:
DECLARE
req UTL_HTTP.req;
resp UTL_HTTP.resp;
BEGIN
UTL_HTTP.set_proxy(proxy => '10.2.1.250:8888',
no_proxy_domains => 'mycompany.com, hr.mycompany.com');
req := UTL_HTTP.begin_request('http://some-remote-site.com');
/* If your proxy requires authentication, use this: */
UTL_HTTP.set_authentication(r => req,
username => 'username',
password => 'password',
for_proxy => TRUE);
resp := UTL_HTTP.get_response(req);... etc.
Я тестировал этот код на прокси-сервере, использующем аутентификацию на базе Microsoft NTLM
. После многочисленных проб и ошибок выяснилось, что перед именем пользователя следует поставить префикс из доменного имени сервера Microsoft
и обратной косой черты. Другими словами, если обычно я подключаюсь к домену NTLM
«mis
» как пользователь bill
с паролем swordfish
, параметры должны выглядеть так:
username => 'mis\bill', password => 'swordfish'