Программирование Oracle в среде UNIX

© Ян Крейг (Ian Craig)
Статья была опубликована в журнале Oracle Magazine
Опубликовано с любезного разрешения Oracle Magazine

Взаимодействие с Oracle с использованием Korn-оболочки UNIX

Инструментальная среда

Для понимания содержимого данной статьи требуется некоторый свод знаний по SQL, PL/SQL и построению скриптов в Korn-оболочке (Korn shell) ОС UNIX. Приведенные примеры протестированы в следующей среде:

• Oracle Enterprise Edition 8.1.5 для Linux,
• Linux версии 2.2.7,
• общедоступная Korn-оболочка "pdksh" серсии 5.2.13.

Если вы недостаточно знакомы с постороением скриптов в Korn-оболочке, советую обратиться к моей любимой книге по данной теме "The new Korn Shell" by Morris Bolsky and David Korn [Prentice-Hall PTR, ISBN: 0-13-182700-6].

Для администраторов баз данных, а также для разработчиков UNIX является наиболее благоприятной средой для размещения баз данных Oracle. Эти обе технологии в течение длительного времени настолько доказали свою отличную гибкость и повышенную устойчивость, что большинство из нас забыли об осторожности. Я все же считаю, что эти две "старые собаки" (two 'old dogs') еще могут выкинуть какой-нибудь новый трюк, поэтому сохраняю предельную внимательность.

Мне представляется, что замечательная Korn-оболочка, входящая в имеющий силу стандарта набор инструментария UNIX, способна предоставить самые лучшие возможности для любого программиста. Несмотря на то, что многие клиенты Oracle уклоняются от активного применения и поддержки разработок в среде UNIX, большинство же вполне согласно признать право на существование "скрипта оболочки" ('shell script'), а также то, что ' скрипт оболочки ' является всем, в чем мы нуждаемся для многих конечных приложений. В этой статье демонстрируется, как утилита SQL*Plus может использоваться также, как любое инструментальное средство UNIX. И вы поймете, что взаимодействие с базой данных Oracle посредством программ, представляющих собой 'скрипты оболочки', просто, гибко и динамично. Это и есть - путь UNIX.

Краткий обзор

SQL*Plus удачно вписывается в стандартные UNIX-потоки ввода/вывода (IO stream; stream - абстрактный последовательный файл - ред.). Самый простой способ продемонстрировать это положение - надо выполнить следующую UNIX-команду:

print "select * from dual;" | sqlplus -s scott/tiger

которая продуцирует следующий выход:

D
-
X

Замечание: флажок '-s' подавляет стандартный заголовок (banner) Oracle.

Как только вы осознали это, возможности использования SQL*Plus в UNIX могут стать весьма захватывающими. Вы можете без труда конструировать и запускать предложения динамического SQL и PL/SQL (Dynamic SQL or PL/SQL). Имеется также механизм, доступный программисту-разработчику скриптов оболочки, который предотвращает фиксирование (commit) и откат (rollback) при выполнении транзакций SQL*Plus.

Используя каналы (pipes) для передачи к SQL*Plus SQL- и PL/SQL- предложений, можно избежать использования временных файлов и конструировать SQL- и PL/SQL- предложения "на лету" ('on-the-fly'). Каналы можно также использовать, чтобы собрать выходной листинг, генерируемый SQL*Plus (уходя, тем самым, еще раз от временных файлов), а также интерпретировать этот выходной листинг инструментальными средствами UNIX. В этой статье для демонстрации технологии коммуникации UNIX/Oracle используется простой UNIX-файл, обеспечивающий интерфейс с таблицей Oracle.

Работает ли Oracle должным образом?

Одной из первых задач, требуемых от интерфейса, является проверка, что целевая база данных Oracle реально готова к бизнес-деятельности. Я знаю, и использовал много приемов, как сделать это, но мое сегодняшнее предпочтение отдано запуску небольшого запроса с предсказанным результатом и проверке этого результата. Если запрос исполняется, как ожидается, то, вероятно, что с Oracle все OK. Следующая функция Is_Oracle_OK выполняет эту работу:

#!/usr/bin/ksh
typeset -irx TRUE=0
typeset -irx FALSE=1

function Is_Oracle_OK
{
     if print "
          select dummy||'OK' from dual;
          " | sqlplus -s scott/tiger | grep -q XOK
     then return ${TRUE}
     fi
     return ${FALSE}
}

if Is_Oracle_OK
then print "Oracle is up"

Этот скрипт запрашивает таблицу "dual", а затем просматривает ожидаемый строчный выход. Если все работает хорошо, то "XOK". Обратите внимание, что заложенная предосторожность гарантирует, что если Oracle отвергает представленный SQL-запрос, то любая часть сгенерированного Oracle сообщения об ошибке не будет отвечать приемному набору критериев команды grep.

Динамически генерируемый SQL

Теперь, когда, использовав функцию Is_Oracle_OK, установлено, что база данных находится в операционном состоянии, мы хотим вставить часть содержимого файла /etc/passwd в таблицу. В этом примере мы хотим загрузить первое и пятое поля (разграниченные символом '|') каждой строки файла, которая начинается с символа 'r'. Для того чтобы немного заострить ситуацию, в коде следующего примера тестовая таблица создается, заполняется, запрашивается и удаляется. Мы запрашиваем фиксацию (commit) после вставки в таблицу каждых очередных 10 записей, а также после последней вставки. Обратите внимание на использование переменных UNIX-среды в SQL-предложениях.

{
typeset TABLE='example' # Name the table to load

          print "WHENEVER SQLERROR EXIT"
          print "WHENEVER OSERROR EXIT"
          print "set autocommit off"

print "create table ${TABLE}
     (
          user_name varchar2(16),
          user_description varchar2(64)
     );"

     typeset -i COUNT=0 # Count records inserted
     typeset FILE=/etc/passwd # The file to load
     typeset Item1 # Holds user name from passwd file
     typeset Item5 # Holds user description

                    grep -E '^r' ${FILE} | while read LINE
     do
          let COUNT=COUNT+1
          Item1=$(print ${LINE} | cut -d'|' -f1)
          Item5=$(print ${LINE} | cut -d'|' -f5)

          print "
                    insert into ${TABLE}
                    (
                              user_name,
                              user_description
                    )
                    values
                    (
                         '${Item1}',
                         '${Item5}'
                    );"

                         # Commit every 10 records - COUNT ends with a '0'
                    [[ ${COUNT} == +([0-9])0 ]] && print "commit;"
          done
          print "commit;" # Issue a commit after the last insert

          print "select * from ${TABLE};"
          print "drop table ${TABLE};"
} | sqlplus -s scott/tiger

В своих скриптах оболочки я обычно предпочитаю отключать автоматическую фиксацию (autocommit) и брать на себя ответственность за фиксирование, тем самым я могу управлять частотой. Обратите внимание, что переменные оболочки Item1 и Item5 заключены в одиночные кавычки в SQL-предложениях вставки, поскольку они являются строками. Не заключайте в кавычки числа!

Остерегайтесь символов одиночных кавычек, содержащихся в исходных данных. Они станут причиной сбоев SQL-предложений, если только вы не отключите (escape) или удалите их.

Сохранение трассы процесса

Хотя я уже весьма доволен, реализовав приведенный выше код одноразового интерфейса, это всего лишь начало. Основная проблема состоит в том, что этот код не проверяет, что Oracle сказал обо всех тех SQL-предложениях, которые мы ему вбросили (threw). Однако это не трудно преодолеть, поскольку выходной листинг SQL*Plus может быть по каналу направлен некоему модулю, который и выполнит необходимые проверки.

Изящная хитрость, применяемая в некоторых ситуациях (например, в отладке), состоит в том, что весь SQL*Plus-овский вход (input) и выход (output) помещать в файл. Попробуйте заменить команду "} | sqlplus -s scott/tiger" (последняя строка в коде предыдущего примера) на "} 2&1 | tee -a /tmp/ora.txt | sqlplus -s scott/tiger 2&1 | tee -a /tmp/ora.txt".

Поскольку это довольно тонкая работа, я предпочитаю заменить все строки 'sqlplus -s scott/tiger' в теле моего кода на псевдоним (alias), что позволит мне использовать механизм 'debug oracle' (oracle-отладка), который я могу активизировать, чтобы записать ввод/вывод (IO) Oracle. Для того чтобы сделать это, надо в начало кода моего Oracle-скрипта оболочки включить следующее:

#!/usr/bin/ksh
typeset -irx TRUE=0 # мое булево 'true' - код возврата
typeset -irx FALSE=1 # мое булево 'false' - код возврата
typeset -r DEBUG=${TRUE} # Если DEBUG ON, можно поменять на FALSE

if (( ${DEBUG} == ${TRUE} ))
then # Режим отладки, весь ввод/вывод Oracle сохраняется в файле
          alias To_Oracle="tee -a /tmp/ora.txt |
                    sqlplus -s scott/tiger 2&1 |
                    tee -a /tmp/ora.txt "
else # Обычный режим, ввод/вывод Oracle не записывается
          alias To_Oracle="sqlplus -s scott/tiger"
fi

Коль скоро псевдоним был определен, я могу использовать алиас To_Oracle вместо строки 'sqlplus -s scott/tiger' в теле моего кода; например:

print "select * from dual;" | To_Oracle

Быстрый контроль ошибок

Если SQL*Plus сталкивается с ошибкой, он обычно выдает сообщение о ее причине, которое начинается с префикса ORA, ERROR или (в последнее время) SP2 и кода ошибки. Вооруженный этим знанием, я могу проверить, все ли взаимодействия Oracle корректно работают, просто просматривая выходной листинг на предмет наличия префиксов сообщений об ошибках, например:

if print "
          create table ${TABLE}
          (
                    user_name varchar2(16),
                    user_description varchar2(64)
          );" |
     sqlplus -s scott/tiger |
     ! grep -qiE 'ora|error|sp2'
then print "Table ${TABLE} created"
else print "An error was detected when creating table ${TABLE}"
fi

Заметьте, что в вышеприведенном примере команда grep возвратит true (истина), если в листинге будут найдены один или большее количество префиксов ошибочных сообщений (в любом сочетании). Используя знак восклицания (!), я отрицаю в обращениях с UNIX философию 'innocent until proven guilty' ('невиновен, пока не доказана вина'). Необходима гарантия, что в ожидаемом листинге нет префиксов сообщений об ошибках, иначе впереди вас ожидает шторм.
Еще раз повторю, что я предпочитаю определять псевдоним для запроса через SQL*Plus, но, чтобы сделать это даже более симпатичным, может использоваться функция, которая берет SQL в качестве аргумента:

alias To_Oracle_OK="sqlplus -s scott/tiger 2&1 | ! grep -qiE 'ora|error|sp2'"

Код теперь выглядит следующим образом:

if print "
          create table ${TABLE}
          (
                    user_name varchar2(16),
                    user_description varchar2(64)
          );" | To_Oracle_OK
then print "Table ${TABLE} created"
else print "An error was detected when creating table ${TABLE}"
fi

Проблема БОЛЬШОГО 'commit'

Когда заканчивается (terminate) сессия SQL*Plus, все незафиксированные (uncommitted) транзакции или теряются (lost), или фиксируются (committed) - в зависимости от вашей установки. При использовании способа, который я только что привел, это вызывает некоторые трудности в случае, когда вы хотите без завершения текущей сессии SQL*Plus проверить, как протекают события. [Прим.редактора: автор, вероятно, имеет в виду терминальную (экранную) UNIX-сессию SQL*Plus, которая действительно закрывается, когда пользователь разрывает соединение с ОС.] Для противодействия этой проблеме я порождаю сессию SQL*Plus как сопроцесс (co-process) и взаимодействую с ним, используя команды оболочки print -p и read -p. Например, инициируйте сессию SQL*Plus следующей командой:

sqlplus -s scott/tiger |&

Теперь направим две команды к сопроцессу:

print -p "select * from dual;"
print -p "exit"

Кажется, что ничего особенного не случилось, но следующий код:

while read -p LINE
do
          print - ${LINE}
done

возвратит SQL*Plus-листинг:

D
-
X

Замечание: UNIX также сообщит, что сопроцесс завершен.

К сожалению, команда read -p будет висеть (hang), если нет никакого выходного листинга для выдачи, или же пока не завершится вся выдача выходного листинга. Это может также случиться, если вы слишком быстро пытаетесь схватить (grab) выходной листинг, в то время как Oracle все еще размышляет. Однако этот метод дает награду программисту, поскольку способен реализовать полную коммуникацию с Oracle через единственную уцелевшую сессию SQL*Plus.

Для того, чтобы уберечь мои программы от зависания команды read, я помещаю в поток выходного листинга маркер (например, используя команду SQL*Plus 'prompt') как требование некоторой работы. Тем самым я гарантирован, что код не проходит за маркер. Размещение маркера также дает системе время, нужное для предотвращения 'слишком быстрого' ('too quick') зависания, о котором упоминалось ранее.

sqlplus -s scott/tiger |&                               # Старт сопроцесса sqlplus

print -p "select * from dual;"                     # SQL-обращение к сопросессу (sqlplus)
print -p "prompt marker1"                          # Установка маркера в листинге

while read -p LINE                                         # Чтение всех строк из сопроцесса
do
[[ "${LINE}" == "marker1" ]] && break      # Разрыв цикла, если прочитан маркер
print - ${LINE}                                                   # Выдача листинга, сгенерированного SQL
done
[[ "${LINE}" == "marker1" ]] && break      # Разрыв цикла, если прочитан маркер
print - ${LINE}                                                   # Выдача листинга, сгенерированного SQL
done

В более новых версиях Korn-оболочки команда read имеет аргумент time-out.

PL/SQL

В приемах, которые описанны в этой статье, PL/SQL может использоваться наравне с SQL. Например:

sqlplus -s scott/tiger |&      # Старт sqlplus как сопроцесса

print -p "begin
               null;
               end;"                          # Определение анонимного блока PL/SQL
print -p '/'                                 # Выполнение блокаPL/SQL
print -p "exit"                          # Завершение сопроцесса

while read -p LINE               # Получение листинга сопроцесса do
               print - ${LINE}
done
produces the output:

PL/SQL procedure successfully completed.

Заключение

Преимущество использования оболочных Oracle-скриптов состоит в том, что можно применить полный спектр UNIX-инструментов с данными Oracle. Учитывая, что Oracle-интерфейс достаточно прост, повышение производительности, полученное разработчиком скриптов за счет использования UNIX для управления данными, может быть довольно существенным. И всегда следует помнить, что имеется более чем один способ выполнить задуманное.

Дополнительная информация

• Статьи по Oracle

Дополнительную информацию Вы можете получить в компании Interface Ltd.

Обсудить на форуме Oracle
Отправить ссылку на страницу по e-mail