Проблемы в использовании FTP-клиента на базе UTL_TCP

Источник: oraclemaniacsblogspot

Сегодня хотелось бы рассказать немного про проблемы в использовании пакета ftp с www.oracle-base.com. Я знаю многих, кто его использует, сам не являюсь исключением. Что сказать, удобно, прям с базы собрал данные, запихнул их в нужные таблицы и не нужно заморачиваться с отдельным внешним сборщиком. Но в четверг мы наткнулись на проблему, решили которую только сегодня (итого 4 рабочих дня и 1 выходной). Итак...

Кому не интересна история, а нужен конкретный совет, топают в Решение.

С четверга начали появляться сессии, активные по многу часов, вернее пока не грохнешь, а грохнуть через ALTER SYSTEM KILL SESSION не удавалось, так и оставались висеть со статусом KILLED. В пятницу пользователи начали звонить и говорить, что при попытке выполнить функцию "Бла-бла-бла" получают зависшее приложение. Ну делать нечего, надо решать быстро, благо пользователей немного, сделал STARTUP FORCE и вроде все заработало. Но это костыль, который пользовать часто нельзя, а лучше вообще не пользовать.
Небольшое лирическое отступление... Система построена для выполнения данной функции примерно так: сервер приложений (IIS) <-> основной инстанс <-> вспомогательный <-> масса ftp-серверов. Вспомогательный инстанс заведен на сервере, расположенном в двух сетках и используется исключительно для передачи данных между сетками (так уж была придумана секьюрность еще до моего прихода). На вспомогательном инстансе используется указанный импровизированный ftp-клиент c Oracle-Base. Написана пара функций, которые возвращают содержимое необходимых файлов. Доступ к этим функциям выполняется через DB Link с основного инстанса.
Так вот, работало оно работало, работало оно работало, и тут бац! и перестало работать. Бывает. Начали искать проблему у себя, где может быть зацикливание, deadlocks и т.п. (в логах то было ORA-02080). В общем пришел к тому (откопал на SQL.RU), что эта ошибка выдается нам, если очень долго читаются/ожидаются данные через линку. Полезли вручную на FTP, оказалось реально проблема с ftp-сервером. Однако было непонятно, почему сессия вешается в статусе ACTIVE, а не отваливается по таймауту. Ограничивали даже время жизни соединения в приложении, но все равно сессии на сервере оставались даже после того как приложение завершало свою работу по таймауту.
Тут как назло починили FTP... Воспроизвести ошибку нереально...
Тут полез по до сих пор непонятной мне причине в пакет ftp, в функцию login и обнаружил, что при создании соединения в UTL_TCP.OPEN_CONNECTION не передается таймаут. По умолчанию он установлен в NULL, что означает, что он бесконечен и ошибка по таймауту не вывалится никогда.

Решение

Вот решил я подправить этот пакет для возможности установки таймаута, чего и вам советую.
В спецификации пакета FTP делаем следующие изменения:

FUNCTION login (p_host    IN  VARCHAR2,
          p_port    IN  VARCHAR2,
          p_user    IN  VARCHAR2,
          p_pass    IN  VARCHAR2,
          p_timeout IN NUMBER := NULL)
RETURN UTL_TCP.connection;

В теле пакета делаем так:

FUNCTION login (p_host    IN  VARCHAR2,
          p_port    IN  VARCHAR2,
          p_user    IN  VARCHAR2,
          p_pass    IN  VARCHAR2,
          p_timeout IN NUMBER := NULL)
RETURN UTL_TCP.connection IS
-- --------------------------------------------------------------------------
l_conn  UTL_TCP.connection;
BEGIN
g_reply.delete;l_conn := UTL_TCP.open_connection(p_host, p_port, tx_timeout => p_timeout);
get_reply (l_conn);
send_command(l_conn, 'USER ' // p_user);
send_command(l_conn, 'PASS ' // p_pass);
RETURN l_conn;
END;

Соответственно, в своих приложениях, где нужно все же установить таймаут, вызываете ftp.login с указанием p_timeout => 60 (для таймаута = 1 минуте).