Sqlplus

Итак - sqlplus. Это command-line утилита, позволяющая выполнять команды как в интерактивном режиме, так и заранее созданные sql файлы. Последнее свойство сделало sqlplus практически стандартом для программ установки схемы данных. Встроенные средства форматирования позволяют получать приличные отчеты и складывать их результаты в файл. Что также часто и успешно используется. 

Какие у него есть достоинства ? Во первых, он есть на всех платформах и версиях, и везде работает одинаково. Т.е. можно не беспокоиться будет ли ваш скрипт работать. Будет !.  Во вторых, он умеет выполнять команды со стандартного входа, что позволяет писать, небольшие но очень эффективные программы на shell. 

Есть ли недостатки ? К наиболее явному можно отнести отсутствие возможности вызвать команду из history и возможность дополнять набираемые команды (так называемый complitition). Почему этого не сделано ? Увы, это не объяснимо. Но есть внешние оболочки, которые снимают эти ограничения.  Посмотрите, например, на Senora.

Какое из этого резюме. Знать основные возможности sqlplus - обязательно.  Это удобно. В очень многих случаях. Например, когда нужно что-то быстро сделать, а какое есть средство вы не знаете.  Для администраторов его знание, на мой взгляд - обязательно. Для разработчиков - крайне полезно.  Не даром, такие известные специалисты, как Steeve Adams или Tom Kyte все свои примеры приводят для sqlplus. 

Конечно, создавать пакеты в нем совсем не удобно, лучше взять свой любимый редактор:

• для Unix Emacs (там же смотри pl/sql моду ); 
• для Windows Quests SqlNavigator, Toad 

Старт:

При первом запуске вы можете получить сообщение "Error accessing PRODUCT_USER_PROFILE". Чтобы этого избежать установите  файл $ORACLE_HOME/sqlplus/admin/pupbld.sql.

Запускаем его:

/export/home/oracle>sqlplus sys/manager

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

sql> 

Как правило я сразу же делаю alias для sqlplus - sp. Маленькая, но полезная хитрость :))

Чтобы изменить малоинформативное sql во что-нибудь полезное нужно создать файл login.sql и поместить его в текущую директорию. См. пример файла. Если вы хотите изменить свое приглашение для всех пользователей поместите этот код в файл $ORACLE_HOME/sqlplus/admin/glogin.sql. Тогда наше приглашение станет таким:

sys@DVP8.1.7>

Управляем sqlplus:

Посмотрим как можно управлять sql c помощью shell скрипта . 

sqlplus /nolog << EOF

verify off;
feedback off;
prompt Connect as smap
conn $smap_owner/$smap_password@$connect_string

select * from emp;

EOF

Все что находиться между символами << EOF и EOF будем интерпретироваться как стандарный ввод, в котором можно использовать любые возможности текущего shell.

Диалоги:

Посмотрим на возможности диалога в sqlplus:

ACCEPT sys_passwd char format a16 prompt 'Enter Sys password:'

Далее вы может использовать эту переменную как &sys_passwd. 

Если Вам не хочется чтобы человеку непрерывно задавали вопросы, то можете создать конфигурационный скрипт:

-- Server Oracle Home 
DEFINE oracle_home = '/jet/oracle'

-- Connect string 
DEFINE smap_tns = 'dvp';

-- Sys password
DEFINE sys_passwd = 'manager';

Все что Вам нужно - перед использованием этих переменных вызовете свой конфигурационный скрипт.

Вызов подскриптов:

Скрипты вызываются друг из друга двумя способами: 

@script1

и 

@@script1

Методы эти отличаются следующим: при вызове через один символ @ скрипта из другой директории остается текущей директорией ваша,  через два символа @@ директория где располагается вызываемый скрипт. 

Можно даже вызвать скрипт сразу при вызове sqlplus: 

sqlplus scott/tiger  @show_emp king

где show_emp наш скрипт, king - параметр скрипта. Обратиться к параметру скрипта можно через переменые &1 и т.д. по количеству параметров.

Дополнительные возможности:

Что явно не хватает sqlplus - конструкций типа if - then -else. Но эта проблема решается. Возьмите скрипт yes_no.sql. Пользоваться им крайне просто:

DEFINE v_prompt = 'Do you want use partition option ?'
@@yes_no enable_partition.sql disable_partition.sql

 Если Вы ответите утвердительно (Y) вызовется первый скрипт, иначе вызовется второй. Что делать если хочется чтобы в случае отказа ничего не происходило вовсе - возьмите срипт dummy.sql

DEFINE v_prompt = 'Do you want create smap_dba schema ?' 
@@yes_no create-smap-dba.sql dummy.sql

Есть подвариант ifs.sql. Он не задает вопросов а принимает решение на основе переменной которую Вы ему передаете:: 

DEFINE use_smap_dba = 'Y'

@@ifs &use_smap_dba create-smap-dba.sql dummy.sql

Динамичный sqlplus:

Посмотрим как заполнить значение переменной sqlplus partition_num  значением переменной v_partition_num :v_partition_num:

var v_partition_num NUMBER;

COLUMN col1 NOPRINT NEW_VALUE partition_num

BEGIN
:v_partition_num := to_number (getoption ('last_partiton_num'));
END; 
/ 
SELECT to_char(:v_partition_num) col1 FROM dual;

В примере ниже, показывается как изменить значение установленной переменной sqlplus динамически:

COLUMN col1 NOPRINT NEW_VALUE var
SELECT decode ('&3', 'Y', '&2'||'&some_var', '&2') col1 FROM dual; 

DEFINE &1 = &var  

Более полный FAQ по sqlplus на английском.