PL/SQL: работа с данными в Интернете на примере (Oracle UTL_HTTP)

PL/SQL обработка Интернет-данных с использованием Oracle UTL_HTTPДопустим, вы хотите загрузить данные с сайта своего бизнес-партнера. Существует много способов выборки веб-страниц.

  •  «Ручная выборка», то есть ввод нужного адреса в браузере.
  •  Использование сценарного языка — например, 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 и выполните следующие действия:

  1. Откройте в браузере (Microsoft IE) сайт
  2. Сделайте двойной щелчок на желтом значке с изображением замка в правом нижнем углу окна.
  3. Выберите команду Details > Copy to File.
  4. Выполните инструкции по экспортированию сертификата в кодировке base64. Или, если на вашем компьютере установлен пакет OpenSSL (как правило, в системах Unix/Linux), выполните следующую команду:
echo '' | openssl s_client -connect host:port

Команда выдает обширную информацию в stdout; просто сохраните текст между строками BEGIN CERTIFICATE и END CERTIFICATE (включительно) в файле. Кстати говоря, HTTPS обычно использует порт 443.

При наличии сертификата вы можете выполнить следующие действия:

  1. Откройте Oracle Wallet Manager.
  2. Откройте файл электронного бумажника.
  3. Импортируйте сертификат из только что созданного файла.
  4. Сохраните файл электронного бумажника и отправьте его на сервер базы данных.

Помните, что сертификаты окажутся в электронном бумажнике 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 соблюдает «букву» стандарта — по крайней мере в этом случае.

Получение сводки погоды с сайта NOAA требует

 

Рис. 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 на уровне сеанса включена по умолчанию в последних версиях 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'

  

Вас заинтересует / Intresting for you:

Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 4634 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48
Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 14738 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Тип данных RAW в PL/SQL
Тип данных RAW в PL/SQL 12239 просмотров Doctor Thu, 12 Jul 2018, 08:41:33
Символьные функции и аргументы...
Символьные функции и аргументы... 18546 просмотров Анатолий Wed, 23 May 2018, 18:54:01
Войдите чтобы комментировать

admin аватар
admin ответил в теме #10283 2 года 3 мес. назад

Благодарю за статью!
Поддерживаю! Отличные примеры!
apv аватар
apv ответил в теме #10279 2 года 3 мес. назад
Благодарю за статью!