Pl sql oracle обработка ошибок

EXCEPTION блок

Обработка ошибок производится в блоке exception:

begin
	-- Код
exception
	-- Обработка ошибок
	when .... then .....;
	when .... then .....;
	when .... then .....;
end;

Ошибки отлавливаются в пределах блока begin-end. Работает это так:

1. Сначала выполняется код между begin и exception
2. Если ошибок не произошло, тогда секция между exception и end ингорируется
3. Если в процессе выполнения кода происходит ошибка, выполнение останавливается
   и переходит в блок exception.
4. Если в блоке находится обработчик для исключения, вызывается код после then
5. Если обработчик не найден, исключение выбрасывается за пределы блока begin-end

Пример блока с обработчиком исключений:

declare
    l_val number;
begin
    select 1 into l_var
    where 2 > 3;
exception
    when no_data_found then
        dbms_output.put_line('Нет данных');
    when dup_val_on_index then
        dbms_output.put_line('Такая строка уже есть');
end;

Предопределённые ошибки

Ошибки обрабатываются по их имени, поэтому часть наиболее частых ошибок в PL/SQL
уже предопределена, как например вышеуказанные no_data_found и dup_val_on_index.

Ниже показан их список и в каких случаях ошибка может возникнуть.

Объявление собственных ошибок

Можно объявлять собственные исключения, давая им
названия, которые полнее раскрывают их суть.

declare
    -- Объявление собственного исключения,
    -- которое мы выбрасываем, если значение заработной
    -- платы ниже дозволенного минимума.
    exc_too_low_salary exception;
    l_salary number := 100;
begin
    if l_salary < 200 then
        -- Бросаем ошибку.
        raise exc_too_low_salary;
    end if;

exception
    when exc_too_low_salary then
        dbms_output.put_line('Обработчик исключения');
end;

Область видимости собственного исключения в данном случае — блок, в котором оно
объявлено. Вне этого блока обработать исключение не получится.

Для более удобной работы с собственными исключениями их можно вынести в отдельный пакет:

create or replace pck_hr_errors is

-- Объявляем исключения в спецификации пакета.
-- Тела пакет не имеет, только спецификацию.

exc_wrong_name      exception;
exc_too_low_salary  exception;
exc_incorrect_pass  exception;

end;

Далее работать с этими исключениями можно подобным образом:

begin
    -- Какой-то код
    ...
exception
    when pck_hr_errors.exc_too_low_salary then
        -- Обработка исключения
        ...
end;

Обработка непредопределённых ошибок

Не все ошибки в Oracle являются предопределёнными. Когда возникает необходимость
их обрабатывать, нужно связать переменную типа exception с кодом ошибки, которую нужно обработать:

declare
    -- объявляем ошибку
    e_incorrect_date exception;

    -- связываем ошибку с кодом
    pragma exception_init(e_incorrect_date, -1830);
begin
    dbms_output.put_line(to_date('2022-02-01', 'dd.mm.yyyy'));
exception
    when e_incorrect_date then
        dbms_output.put_line ('Неправильный формат даты');
end;

Следует помнить, что коды ошибок являются отрицательными числами.

Ошибки и вложенные блоки

Если ошибка не обрабатывается в пределах блока begin ..end,
она выбрасывается за его пределы. Далее эта ошибка может быть
обработана блоком exception внешнего блока. Если и там ошибка
не обрабатывается, она выбрасывается и за его пределы, и так
далее.

declare
    a number;

-- Внешний блок
begin
    -- Вложенный блок
    begin
        a := 1 / 0;
        -- Важно помнить, что после возникновения ошибки
        -- выполнение кода в пределах блока прекращается.
        -- Следующий код не будет выполнен
        dbms_output.put_line('Этот код не будет выполнен');
    end;
exception
    when zero_divide:
        dbms_otuput.put_line('Ошибка обработана внешним блоком');
end;

raise_application_error

Если ошибка, брошенная Oracle, достигает клиентского приложения,
то она имеет примерно такой текст: ORA-01722 invalid number.

Процедура raise_application_error позволяет вызвать исключение
с заданным номером, связать его с сообщением и отправить его
вызывающему приложению.

begin
    raise_application_error(-20134, 'Неправильный номер паспорта');
end;

Диапазон возможных кодов ошибок [-20999, 20000]. Сообщение должно
помещаться в тип varchar2(2000).

Можно указать третий boolean параметр, который в случае
значения true добавит текущую ошибку в список предыдущих
ошибок, возникших в приложении. По умолчанию значение равно false,
что значит, про сообщение об ошибке заменяет все предыдущие ошибки
собой.

Мы можем объявить собственное исключение, связать его с номером
в диапазоне [-20999, 20000] и использовать для обработки исключений,
брошенных с помощью raise_application_error:

declare
    e_wrong_passport exception;
    
    -- связываем ошибку с кодом
    pragma exception_init(e_wrong_passport, -20999);
begin
    raise_application_error(-20999, 'Неправильный номер паспорта');
exception
    when e_wrong_password then
        dbms_output.put_line ('Неправильный номер паспорта');
end;

PL/SQL блок:

DECLARE
…  — объявляющая секция
BEGIN
…  — выполняющая секция
EXCEPTION
…  — секция обработки исключительных ситуаций
END;
/

При установлении исключительной ситуации управление программой сразу же передается
в секцию исключительных ситуаций блока.
Если такой секции в блоке нет, то исключение передается в объемлющий блок.

После передачи управления обработчику, вернуться в выполняющую секцию блока невозможно.

Исключения бывают:

— стандартные
— определенные пользователем

Стандартные исключительные ситуации инициируются автоматически при возникновении
соответствующей ошибки Oracle.

Исключительные ситуации, определяемые пользователем,
устанавливаются явно при помощи оператора RAISE.

Обрабатываются исключения так:

EXCEPTION
    WHEN имя_ex1 THEN
        …; — обработать
    WHEN имя_ex2 THEN
        …; — обработать
    WHEN OTHERS THEN
        …; — обработать
END;
/

Имена исключений не должны повторяться т.е. каждое исключение может
обрабатываться максимум только одним обработчиком в секции EXCEPTION

Один обработчик может обслуживать несколько исключительных ситуаций
и их нужно перечислить в условии WHEN через OR

EXCEPTION
    WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
        INSERT INTO log_table(info) VALUES (‘A select error occurred.’);
END;
/

Два исключения одновременно один обработчик обработать не может:

WHEN имя_ex1 AND имя_ex2  — > ERR

Пользовательское исключение должно быть определено:

DECLARE
    e_my_ex EXCEPTION;
    …
BEGIN
    IF (…) THEN
        RAISE e_my_ex;
    END IF;
    …

EXCEPTION
    WHEN e_my_ex THEN
    …
END;
/

После перехвата более специализированных исключений:

WHEN … THEN
…
WHEN … THEN

мы можем перехватить все остальные исключения с помощью:

WHEN OTHERS THEN
…

Обработчик OTHERS рекомендуется помещать на самом высоком уровне программы:
(В самом высшем блоке)
для обеспечения распознавания всех возможных ошибок.

Иначе ошибки будут распространяться в вызывающую среду и возможны
нежелательные последствия, такие как откат на сервере текущей транзакции.

Не используйте в промышленном коде такое:

WHEN OTHERS THEN NULL;

т.к. оно будет молчаливо перехватывать все неожиданные ошибки не сообщая,
что они произошли.

Обработчик OTHERS должен регистрировать ошибку и возможно предоставлять
дополнительную информацию для дальнейшего исследования.

WHEN OTHERS THEN
    INSERT INTO log_table(info) VALUES (‘Another error occurred.’);
END;
/

Информацию об ошибках можно получить при помощи двух встроенных функций:

— SQLCODE
— SQLERRM

первая возвращает код текущей ошибки а вторая текст сообщения об ошибке

Для исключений определенных пользователем:

SQLCODE возвращает 1
а
SQLERRM «User-defined Exception»

WHEN OTHERS THEN
    v_ErrorCode := SQLCODE;
    v_ErrorText := SUBSTR(SQLERRM, 1, 200);
    INSERT INTO log_tab(code, message, info) VALUES (v_ErrorCode, v_ErrorText, ‘Oracle error.’);
END;
/

В таблице log_tab поле message ограничено 200 символами
и чтобы не произошло ошибки при вставке, мы урезаем длину
сообщения до 200 символов с помощью SUBSTR
А то максимальная длина сообщения может достигать 512 символов.

Функция SQLERRM может принимать один числовой аргумент.
При этом она возвратит текст сообщения об ошибке, код которой равен заданному числу.

Аргумент должен быть всегда отрицательным числом.
Если аргумент равен 0, то будет возвращено сообщение:
ORA-0000: normal, succesful completion

При положительном аргументе не равном 100 будет возвращено сообщение:
non-ORACLE Exception

А при

SQLERRM(100) — > ORA-1403: no data found

Это исключение ANSI

Остальные коды ошибок Oracle все отрицательные.

Для получения информации об ошибке можно также использовать функцию
FORMAT_ERROR_STACK из пакета DBMS_UTILITY

Её можно непосредственно использовать в операторах SQL:

WHEN OTHERS THEN
    INSERT INTO log_tab(code, message, info) VALUES (NULL,
                                                     SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK, 1, 200),
                                                    ‘Oracle error occurred.’);
END;
/

Ещё одна функция.

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

она аналогична  FORMAT_ERROR_STACK
но не подвержена ограничению длины сообщения в 2000 байт.
Она возвращает полностью весь стек ошибок на момент инициирования исключительной ситуации.

Любое именованное исключение можно связать с конкретной ошибкой ORACLE.

Например, в ORACLE есть стандартная ошибка ORA-1400, которая возникает при пропуске значения
или вставке значения NULL в столбец с ограничением NOT NULL.

ORA-1400: mandatory NOT NULL column missing or NULL during insert.

Мы хотим создать свое пользовательское именованное исключение и связать его с этой стандартной ошибкой ORA-1400

DECLARE
e_my_ex EXCEPTION;
PRAGMA EXCEPTION_INIT(e_my_ex, -1400);

BEGIN
WHEN e_my_ex THEN
    INSERT INTO log_tab(info) VALUES (‘ORA-1400 occurred.’);
END;
/

Теперь мы перехватываем её по имени с помощъю WHEN или THEN

Все стандартные исключительные ситуации также ассоциируются с соответствующими им ошибками Oracle
при помощи прагмы EXCEPTION_INIT в пакете STANDARD

VALUE_ERROR  — > ORA-6501
TO_MANY_ROWS — > ORA-1422
ZERO_DIVIDE  — > ORA-1476
……….
и т.д.

Так что если вам не хватает некоего имени конкретной ошибки ORA-NNNN,
то придумайте свое имя и свяжите его с ошибкой с помощью прагмы : EXCEPTION_INIT

Для собственных пользовательских исключений можно придумать свои коды ошибок, которые разрешено брать из диапазона:
-20000 до -20999
и придумать свой текст сообщения

RAISE_APPLICATION_ERROR(номер, текст, [флаг]);

TRUE — пополнить список ранее произошедших ошибок
FALSE — новая ошибка заместит текущий список ошибок (по умолчанию)

set serveroutput on

variable a NUMBER;
variable b NUMBER;

exec :a := 0;
exec :b := 10;

DECLARE
    l_a NUMBER := :a;
    l_b NUMBER := :b;
    l_c NUMBER;

BEGIN
    IF l_a = 0 THEN
        raise_application_error(-20005, ‘Divizor is 0’);
    END IF;
    l_c := l_b / l_a;
    dbms_output.put_line(‘The result: ‘||l_c);
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line(SQLERRM);
END;
/

Поскольку у исключения нет имени, то его может обработать только обработчик OTHERS

Но такое исключение можно и поименовать
и с помощью прагмы связать с нашим кодом.

DECLARE

    my_ex EXCEPTION;
    …..
    …..
    PRAGMA EXCEPTION_INIT(my_ex, -20005);

BEGIN
    IF (…) THEN
        raise_application_error(-20005, ‘Divizor is 0’);
    …..
    …..

EXCEPTION
    WHEN my_ex THEN
        dbms_output.put_line(SQLERRM);
END;
/

Теперь это исключение можно обработать по имени с помощью:

WHEN my_ex THEN

EXCEPTION PROPAGATION

Оглавление

Исключения

Предупреждающие сообщения при компиляции

Обработка исключений в PL/SQL

Создание собственных исключений

Связываем исключение с кодом ошибки

Именованные системные исключения

Инициирование исключений

Оператор RAISE

Использование процедуры RAISE_APPLICATION_ERROR

Использование функций обработки ошибок

Продолжение работы после возникновения исключения

Эскалация необработанного исключения

На что стоит обратить внимание

Динамический SQL и динамический PL/SQL

Инструкции NDS

Инструкция EXECUTE IMMEDIATE

Инструкция OPEN FOR

Режимы использования параметров

Дублирование формальных параметров

Передача значений NULL

Использование пакета DBMS_SQL

Когда следует использовать DBMS_SQL

Новые возможности Oracle 11g

SQL Injection

Statement modification

Statement injection

Data Type Conversion

Методы защиты от SQL-инъекций

Использование внутреннего преобразования формата 

Исключения

Ошибки, возникающие при работе с СУБД, можно разделить на следующие группы:

• ошибки, генерируемые системой (например, нехватка памяти или повторяющееся значение индекса);

• ошибки, генерируемые приложением (например, невыполнение каких-либо условий и проверок).

В языке PL/SQL ошибки всех видов интерпретируются как исключительные ситуации, или исключения.

Исключения могут быть системными и пользовательскими:

Предупреждающие сообщения при компиляции

Исполняющую среду возможно сконфигурировать таким образом, чтобы при компиляции программных модулей происходила выдача сообщений, предупреждающих о моментах, на которые следует обратить внимание — например, при попытке использования в хранимой процедуре уже неподдерживаемых возможностей PL/SQL.

Категории предупреждающих сообщений:

Конфигурирование производится посредством установки значения параметра PLSQL_WARNINGS.

Посредством установки параметра PLSQL_WARNINGS можно:

• включать и отключать либо все предупреждающие сообщения, либо сообщения одной или нескольких категорий, либо конкретное сообщение;

• трактовать конкретные предупреждения как ошибки.

Значение этого параметра можно задавать для:

• всего экземпляра базы данных (ALTER SYSTEM);

• текущего сеанса (ALTER SESSION);

• хранимого PL/SQL-модуля (ALTER «PL/SQL block»).

Во всех ALTER-операторах значение параметра PLSQL_WARNINGS задается в следующем виде:

SET PLSQL_WARNINGS = ‘value_clause’ [, ‘value_clause’ ] …

, где

value_clause::=

{ ENABLE | DISABLE | ERROR }:

{ ALL | SEVERE | INFORMATIONAL | PERFORMANCE | { integer | (integer [, integer ] …) } }

Для отображения предупреждающих сообщений, сгенерированных в процессе компиляции, можно либо опрашивать представления *_ERRORS (DBA_,  USER_,  ALL_ ), либо использовать команду SHOW ERRORS.

Несколько примеров настройки режима выдачи предупреждений

Включение всех предупреждений внутри сессии (полезно при разработке):

ALTER SESSION SET PLSQL_WARNINGS=’ENABLE:ALL’;

Включение сообщений PERFORMANCE для сессии:

ALTER SESSION SET PLSQL_WARNINGS=’ENABLE:PERFORMANCE’;

Включение сообщений PERFORMANCE для процедуры loc_var:

ALTER PROCEDURE loc_var COMPILE PLSQL_WARNINGS=’ENABLE:PERFORMANCE’;

Включение сообщений SEVERE, отключение сообщений PERFORMANCE и трактования сообщения
PLW-06002 (unreachable code) как ошибки:

ALTER SESSION SET PLSQL_WARNINGS=’ENABLE:SEVERE’, ‘DISABLE:PERFORMANCE’, ‘ERROR:06002’;

Отключение всех предупреждающих сообщений для текущей сессии:

ALTER SESSION SET PLSQL_WARNINGS=’DISABLE:ALL’;

Для просмотра текущего значения PLSQL_WARNINGS следует обратиться к представлению ALL_PLSQL_OBJECT_SETTINGS.

Обработка исключений в PL/SQL

PL/SQL перехватывает ошибки и реагирует на них при помощи так называемых обработчиков исключений. Механизм функционирования обработчиков исключений позволяет четко отделить код обработки ошибок от исполняемых операторов, дает возможность реализовать обработку ошибок, управляемую событиями, отказавшись от устаревшей линейной модели программирования.

Независимо от того, как и по какой причине было инициировано конкретное исключение, оно обрабатывается одним и тем же обработчиком в разделе исключений.

Любая ошибка может быть обработана только одним обработчиком.

Для обработки исключений в блоке PL/SQL предназначается необязательный раздел EXCEPTION:

BEGIN

операторы

EXCEPTION

WHEN [исключение 1]THEN …..;

WHEN [исключение 2]THEN …..;

…

WHEN [исключение N]THEN …..;

WHEN OTHERS THEN …..;

END;

Если в исполняемом блоке PL/SQL инициируется исключение, то выполнение блока прерывается и управление передается в раздел обработки исключений (если таковой имеется). После обработки исключения возврат в исполняемый блок уже невозможен, поэтому управление передается в родительский блок.

Обработчик WHEN OTHERS должен быть последним обработчиком в блоке, иначе возникнет ошибка компиляции. Этот обработчик не является обязательным. Если он отсутствует, то все необработанные исключения передадутся в родительский блок, либо в вызывающую хост-систему.

В одном предложении WHEN, можно объединить несколько исключений, используя оператор OR:

WHEN invalid_company_id OR negative_balance THEN

Также в одном о6ра6отчике можно ком6инировать имена пользовательских и системных исключений:

WHEN balance_too_low OR zero_divide OR dbms_ldap.invalid_session THEN

Создание собственных исключений

Внутри приложения можно определять свои собственные (пользовательские) исключения.

Сделать это можно в разделе объявлений блока РL/SQL следующим образом:

DECLARE

   INVALID_COMPANY_ID  EXCEPTION;

Для того, чтобы инициировать исключение, необходимо воспользоваться оператором RAISE:

raise INVALID_COMPANY_ID;

После этого выполнение программы переходит в раздел EXCEPTION на соответствующий обработчик:

BEGIN

…..

raise INVALID_COMPANY_ID;

EXCEPTION

when DUP_VAL_ON_INDEX then

….

when INVALID_COMPANY_ID   then

….

END;

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

RAISE_APPLICATION_ERROR(-20000, ‘My error!’);

Связываем исключение с кодом ошибки

Предположим, у нас есть программа, при выполнении которой может сгенерироваться ошибка, связанная с данными, например ОRА-01843: not a valid month.

Для перехвата этой ошибки в код программы потребуется поместить такой обработчик:

EXCEPTION

       WHEN OTHERS THEN

   IF SQLCODE = -1843 ТНЕN /* not a valid month */

Но такой код малопонятен.

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

DECLARE

     invalid_month EXCEPTION;

     PRAGMA EXCEPTION_INIT(invalid_month, -1843);

BEGIN

     ……

EXCEPTION

     WHEN invalid_month THEN …..

END;

Теперь имя ошибки говорит само за себя и никакие литеральные номера ошибок, которые трудно запомнить, не понадобятся.

Установив такую связь, можно инициировать исключение по имени и использовать это имя в предложении WHEN обработчика ошибок.

Именованные системные исключения

B Oracle для некоторых системных исключений определены стандартные имена, которые заданы с помощью директивы компилятора EXCEPTION_INIT во встроенных пакетах.

Наиболее важные и широко применяемые из них определены в пакете STANDARD.

То обстоятельство, что этот пакет используется по умолчанию, означает, что на определенные в нем исключения можно ссылаться без указания в качестве префикса имени пакета.
Например, если необходимо обработать в программе исключение NO_DАТА_FOUND, то это можно сделать посредством любого из двух операторов:

WHEN   NO_DАТА_FOUND   THEN

WHEN   STANDARD.NO_DАТА_FOUND   THEN

Именованные системные исключения

Инициирование исключений

Программно инициировать исключение можно посредством оператора RAISE или процедуры RAISE_АРРLICATIОN_ERROR.

Оператор RAISE

С помощью оператора RAISE можно инициировать как собственные, так и системные исключения.

Оператор имеет три формы:

Использование процедуры RAISE_APPLICATION_ERROR

Для инициирования исключений, специфических для приложения, в Oracle существует процедура RAISЕ_APPLICATION_ERROR.
Ее преимущество перед оператором RAISЕ (который тоже может инициировать специфические для приложения явно объявленные исключения) заключается в том, что она позволяет связать с номером исключения некоторое текстовое сообщение об ошибке.

PROCEDURE RAISE_APPLICATION_ERROR(num BINARY_INTEGER,

      msg VARCHAR2,

      keeperrorstack boolean default false);

Здесь

num – это номер ошибки из диапазона от -20999 до -20000;

msg — это сообщение об ошибке, длина которого не должна превышать 2048 символов (символы, выходящие за эту границу, игнорируются);

keepеrrorstасk – параметр указывает, хотите вы добавить ошибку к тем, что уже имеются в стеке (true), или заменить существующую ошибку (значение по умолчанию – false).

Использование функций обработки ошибок

SQLCODE

Предложение WHEN OTHERS используется для перехвата исключений, не указанных в предложениях WHEN. Однако в этом обработчике тоже нужна информация о том, какая именно ошибка произошла. Для ее получения можно воспользоваться функцией SQLCODE, возвращающей номер возникшей ошибки (значение 0 указывает, что в стеке ошибок нет ни одной ошибки).

SQLERRM

Возвращает поясняющее сообщение для текущей или для указанной ошибки:

SQLERRM – возвратит описание для самой последней ошибки

SQLERRM(code NUMBER) – возвратит описание для ошибки с указанным кодом

DBMS_UTILITY.FORMAT_CALL_STACK

Функция возвращает отформатированную строку со стеком вызовов в приложении PL/SQL.

DBMS_UTILITY.FORMAT_ERROR_STACK

Эта функция, как и SQLERRM, возвращает сообщение, связанное с текущей ошибкой.
Ее отличия от SQLERRM:

• она возвращает до 2000 символов (SQLERRM возвращает 512 символов)

• этой функции нельзя в качестве аргумента передать код ошибки

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

Функция появилась в Oracle 10.

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

Продолжение работы после возникновения исключения

Если согласно бизнес-логики задачи необходимо обработать исключение и продолжить работу, начиная с того места, где одно произошло, то одним из вариантов решения может быть размещение каждой инструкции в собственном PL/SQL-блоке со своим обработчиком исключений. Тогда при возникновении исключения управление будет передано следующей инструкции.

Эскалация необработанного исключения

Инициированное исключение обрабатывается в соответствии с определенными правилами. Сначала PL/SQL ищет обработчик исключения в текущем блоке (анонимном блоке, процедуре или функции). Если такового не нашлось, исключение передается в родительский блок. Затем PL/SQL пытается обработать исключение, инициировав его еще раз в родительском блоке. И так в каждом внешнем по отношению к другому блоке до тех пор, пока все они не будут исчерпаны. После этого PL/SQL возвращает необработанное исключение в среду приложения, из которого был выполнен самый внешний блок PL/SQL.

На что стоит обратить внимание

1. Если при выполнении нескольких DML-операций в SQL-среде возникает исключительная ситуация, то все операции, предшествующие ошибочному оператору, считаются выполненными корректно и не откатываются.

2. Если те же самые DML-операции обернуть в блок BEGIN … END — тогда при возникновении исключительной ситуации на очередном DML-операторе все предыдущие успешно (!) выполненные операции откатываются. Откат происходит к моменту начала выполнения блока.
   Т.е. блок либо выполняется целиком, либо не выполняется совсем.

3. Если обработчик завершается с повторной инициацией исключительной ситуации (напр., WHEN OTHERS then raise), то все изменения, проделанные в блоке, откатываются.

4. Если же выход из блока происходит через обработку исключительной ситуации и повторной инициации исключительной ситуации не происходит (напр., WHEN OTHERS then null), то блок считается исполненным успешно и отката изменений, которые внутри него произошли, не будет (!!!). То есть результат работы операторов, предшествующих ошибочному оператору, останется в БД.
   Поэтому, если по бизнес-логике такого не нужно, то в обработчике исключения надо явно делать ROLLBACK.

Статическими называются жестко закодированные инструкции и операторы, которые не изменяются с момента компиляции программы.
Инструкции динамического SQL формируются, компилируются и вызываются непосредственно во время выполнения программы.
Следует отметить, что такая гибкость языка открывает перед программистами огромные возможности и позволяет писать универсальный код многократного использования.

Начиная с Огасlе7 поддержка динамического SQL осуществляется с помощью встроенного пакета DВМS_SQL.
В Оrасlе 8i для этого появилась еще одна возможность — встроенный динамический SQL (Native Dynamic SQL, NDS).
NDS интегрируется в язык PL/SQL; пользоваться им намного удобнее, чем DВМS_SQL.

На практике NDS в подавляющем большинстве является более предпочтительным решением.

Инструкции NDS

Главным достоинством NDS является его простота.

NDS представлен в языке РL/SQL единственной инструкцией EXECUTE IMMEDIATE, немедленно выполняющей заданную SQL инструкцию, а также расширением инструкции OPEN FOR, позволяющей выполнять сложные динамические запросы.

В отличие от пакета DBMS_SQL, для работы с которым требуется знание десятка процедур и множества правил их использования, при использовании NDS все очень просто.

Инструкция EXECUTE IMMEDIATE

Инструкция ЕХЕСUТЕ IMMEDIATE, используемая для выполнения необходимой SQL-инструкции, имеет следующий синтаксис:

EXECUTE IMMEDIATE строка_SQL
[INTO {переменная[, переменная]…| запись}]
[USING [ IN | OUT | IN OUT ] аргумент
         [. [ IN | OUT | IN OUT ] аргумент]…];

где

• строка_SQL — строковое выражение, содержащее SQL-инструкцию или блок РL/SQL;

• переменная — переменная, которой присваивается содержимое поля, возвращаемого запросом;

• запись — запись, основаниая на типе данных который определяется пользавателем или объявляется с помощью атрибута %ROWTYPE, и принимающая всю возвращаемую запросом строку;

• аргумент — выражение, значение которого передается SQL-инструкции или блоху РL/SQL, либо идентификатор, являющийся входной и/или выходной переменной для функции или процедуры, вызываемой из блока PL/SQL;

• INTO — предложение, используемое для однострочных запросов (для каждого возвращаемого запросом столбца в этом предложении должна быть задана отдельная переменная или же ему должно соответствовать поле записи совместимого типа);

• USING — предложение, определяющее параметры SQL-инструкции и используемое как в динамическом SQL, так и в динамическом РL/SQL (способ передачи параметра дается только в РL/SQL, причем по умолчанию для него установлен режим передачи IN).

Инструкция ЕХЕСUТЕ IMMEDIATE может использоваться для выполнения любой SQL-инструкции или PL/SQL-блока, за исключением многострочных запросов.

Если SQL-строка заканчивается точкой с запятой, она интерпретируется как блок РL/SQL. В противном случае воспринимается как DML- или DDL-инструкция.

Строка может содержать формальные параметры, но с их помощью не могут быть заданы имена объектов схемы, скажем, такие, как имена столбцов таблицы.

При выполнении инструкции исполняющее ядро заменяет в SQL-строке формальные параметры (идентификаторы, начинающиеся с двоеточия) фактическими значениями параметров подстановки в предложении USING.
В инструкции ЕХЕСUТЕ IMMEDIATE не разрешается передача литерального значения NULL — вместо него следует указывать переменную соответствующего типа, содержащую это значение.

Несколько примеров:

• Создание индекса:

EXECUTE IMMEDIATE ‘CREATE INDEX emp_u_l ON employee (last_name)’;

• Хранимую процедуру, выполняющую любую инструкцию DDL, можно создать так:
  CREATE OR REPLACE PROCEDURE execDDL(ddl_string in varchar2) is
  BEGIN
  EXECUTE IMMEDIATE ddl_string;
  END;

При наличии процедуры создание того же индекса выглядит так:
BEGIN
execDDL(‘CREATE INDEX emp_u_l ON employee (last_name)’);
END;

• DECLARE
     v_emp_last_name    VARCHAR2(50);
     v_emp_first_name    VARCHAR2(50);
     v_birth        DATE;
  BEGIN
     EXECUTE IMMEDIATE ‘select emp_last_name, emp_first_name, birth ‘ ||
                                             ’from EMPLOYEE where id = :id’
         INTO v_emp_last_name, v_emp_first_name, v_birth
         USING 178;
     dbms_output.put_line(v_emp_last_name);
     dbms_output.put_line(v_emp_first_name);
     dbms_output.put_line(to_char(v_birth, ‘dd.mm.yyyy’));
  END;

Инструкция OPEN FOR

Синтаксис инструкции OPEN FOR таков:

OPEN {переменная_курсор|:хост_переменная_курсор } FOR строка_SQL
[USING аргумент[, аргумент]…];

Здесь

• переменная_курсор – слаботипизированная переменная-курсор (SYS_REFCURSOR);

• :хост_переменная_курсор — переменная-курсор, объявленная в хост-среде PL/SQL;

• cтрока_SQL – инструкция SELECT, подлежащая динамическому выполнению;

• USING – такое же предложение, как в EXECUTE IMMEDIATE.

Режимы использования параметров

• При передаче значений параметров SQL-инструкции можно использовать один из трех режимов:

— IN (только чтение, задан по умолчанию);
— OUT (только запись);
— IN OUT (чтение и запись).

Когда выполняется динамический запрос, все параметры SQL-инструкции, за исключением параметра в предложении RETURNING, должны передаваться в режиме IN:

DECLARE
   v_emp_name1 VARCHAR2(50) := ‘Марина’;
   v_emp_name2 VARCHAR2(50) := ‘Иванова’;
   v_emp_name  VARCHAR2(50);
   v_id_emp  NUMBER := 1666;
BEGIN
   EXECUTE IMMEDIATE ‘update ADM.EMPLOYEE ‘ ||
                     ‘set    emp_name1 = :v_emp_name1, ‘ ||
                              ’emp_name2 = :v_emp_name2 ‘ ||
                     ‘where  id_emp = :v_id_emp ‘ ||
                     ‘returning emp_name1 into :val’
       USING IN v_emp_name1, IN v_emp_name2, IN v_id_emp, OUT v_emp_name;
   dbms_output.put_line(v_emp_name);
END;
/

Дублирование формальных параметров

При выполнении динамической SQL-инструкции связь между формальными и фактическими параметрами устанавливается в соответствии с их позициями. Однако интерпретация одноименных параметров зависит от того, какой код, SQL или PL/SQL, выполняется с помощью оператора EXECUTE IMMEDIATE:

При выполнении динамической SQL-инструкции (DML- или DDL-строки, не оканчивающейся точкой с запятой) параметр подстановки нужно задать для каждого формального параметра, даже если их имена повторяются.

Когда выполняется динамический блок PL/SQL (строки, оканчивающейся точкой с запятой), нужно указать параметр подстановки для каждого уникального формального параметра.

Передача значений NULL

При попытке передать NULL в качестве параметра подстановки:

EXECUTE IMMEDIATE ‘UPDATE employee SET salary = :newsal WHERE hire_date IS NULL’
USUNG NULL;

произойдет ошибка.

Дело в том, что NULL типа данных не имеет и поэтому не может являться значением одного из типов данных SQL.

Преодолеть это можно так:

1. Можно использовать неинициализированную переменную.

2. Можно преобразовать NULL в типизированное значение: USING TO_NUMBER(NULL);

Использование пакета DBMS_SQL

Пакет DBMS_SQL предоставляет возможность использования в PL/SQL динамического SQL для выполнения DML- или DDL-операций.

Выполнение одного динамического оператора с использованием пакета DBMS_SQL состоит, как правило, из следующих шагов:

1. Связывание текста динамического оператора с курсором и его синтаксический анализ и разбор;

2. Связывание входных аргументов с переменными, содержащими реальные значения;

3. Связывание выходных значений с переменными вызывающего блока;

4. Указание переменных, в которые будут сохраняться выходные значения;

5. Выполнение оператора;

6. Извлечение строк;

7. Получение значений переменных, извлеченных запросом;

8. Закрытие курсора.

Ниже приведен перечень функций и процедур пакета DBMS_SQL:

Когда следует использовать DBMS_SQL

Хотя встроенный динамический SQL гораздо проще применять, а программный код более короткий и понятный, но все же бывают случаи, когда приходится использовать пакет DBMS_SQL.
Это следующие случаи:

• Разбор очень длинных строк.
  Если строка длиннее 32К, то EXECUTE IMMEDIATE не сможет ее выполнить;

• Получение информации о столбцах запроса;

• Минимальный разбор динамических курсоров.
  При каждом выполнении EXECUTE IMMEDIATE динамическая строка разбирается заново (производится синтаксический анализ, оптимизация и построение плана выполнения запроса), поэтому в некоторых ситуациях это обходится слишком дорого, и тогда DBMS_SQL может оказаться эффективнее.

Новые возможности Oracle 11g

В Oracle 11g появились средства взаимодействия между встроенным динамическим SQL и DBMS_SQL: появилась возможность преобразования курсоров DBMS_SQL в курсорные переменные и наоборот.

• Функция DBMS_SQL.TO_REFCURSOR

Преобразует курсор, полученный вызовом DBMS_SQL.OPEN_CURSOR в курсорную переменную, объявленную с типом SYS_REFCURSOR.

• Функция DBMS_SQL.TO_CURSOR

Преобразует переменную REF CURSOR в курсор SQL, который затем может передаваться подпрограммам DBMS_SQL.

SQL Injection

SQL Injection – один из типов несанкционированного доступа к данным.

В результате выполнения SQL-инъекций становится возможным выполнять действия, которые не предполагались создателем процедуры.

Технику SQL Injection можно разделить на три группы:

— Statement modification;

— Statement injection;

— Data Type Conversion.

Statement modification

Statement modification – изменение динамического SQL-запроса таким образом, что он будет работать не так, как планировал разработчик.

Пусть имеется следующая функция:

   create or replace function SQL_INJECTION(p_ename in varchar2) return varchar2 is

       v_ret varchar2(200);

       v_qry varchar2(200);

   begin

       v_qry := ‘select job from scott.emp where ename = »’ || p_ename || »»;

       dbms_output.put_line(v_qry);

       execute immediate v_qry into v_ret;

       return v_ret;

   end SQL_INJECTION;

Если вызвать ее с параметром p_ename => »’ union select to_char(sal) from emp where ename = »KING‘, то получим доступ к зарплате сотрудника KING:

SQL> select sql_injection(p_ename => ‘» union select to_char(sal) from scott.emp where ename = »KING‘) king_salary from dual;

KING_SALARY

———————————————————————————

5000

Запрос при этом будет выполняться такой:

select job from scott.emp where ename = »
union
select to_char(sal) from scott.emp where ename = ‘KING’;

Statement injection

Statement injection — добавление еще одного DML- или DDL-оператора (или даже нескольких) к динамическому SQL-оператору.

Рассмотрим такую процедуру:

CREATE OR REPLACE PROCEDURE stmt_injection_demo(user_name    IN VARCHAR2) IS

   v_block VARCHAR2(4000);

BEGIN

   — Следующий динамический блок уязвим для техники statement injection

   — из-за использования конкатенации

   v_block := ‘BEGIN

     DBMS_OUTPUT.PUT_LINE(»user_name: ‘ || user_name || »’);

   END;’;

   dbms_output.put_line(‘PL/SQL Block: ‘ || v_block);

   EXECUTE IMMEDIATE v_block;

END stmt_injection_demo;

/

Если вызвать ее с параметром user_name => ‘Andy»); update emp set sal = 2500 where ename = upper(»SMITH’, то в результате ее работы будет не только выведено на печать «user_name: Andy», но и еще будет увеличено значение поля sal у сотрудника SMITH.

Data Type Conversion

Еще один малоизвестный способ SQL-инъекций связан с использованием NLS-параметров сессии.

Создадим функцию data_type_conversion, которая по дате приема на работу выдает имя сотрудника:

CREATE OR REPLACE FUNCTION data_type_conversion(p_hiredate IN DATE) RETURN VARCHAR2 IS

   v_ret VARCHAR2(200);

   v_qry VARCHAR2(200);

BEGIN

   v_qry := ‘select ename from scott.emp where hiredate = »’ || p_hiredate || »»;

   dbms_output.put_line(v_qry);

   EXECUTE IMMEDIATE v_qry INTO v_ret;

   RETURN v_ret;

END data_type_conversion;

Результат вызова этой функции:

SQL> select DATA_TYPE_CONVERSION(date ‘1982-01-23’) result from dual;

RESULT

———————————————————————————

MILLER

Если же задать формат даты, как указано ниже, и выполнить select-оператор:

SQL> ALTER SESSION SET NLS_DATE_FORMAT=’«» OR empno = »7499″‘;

SQL> select DATA_TYPE_CONVERSION(date ‘1982-01-23’) result from dual;

, то получим следующий результат:

RESULT

———————————————————————————

ALLEN

Результат мы получим не тот, что ожидалось  – из-за того, что наш запрос теперь стал выглядеть так:

select ename from scott.emp where hiredate = » OR empno = ‘7499’;

Методы защиты от SQL-инъекций

Если в приложении используется динамический SQL,то следует использовать следующие методы, которые не позволят злоумышленнику преодолеть наложенные ограничения:

Связывание переменных;
Если в функции SQL_INJECTION оператор для динамического выполнения конструировать не с помощью конкатенации, а с использоыванием связанной переменной, то это не позволит злоумышленнику изменить логику запроса:
v_qry := ‘select job from scott.emp where ename = :p_ename‘;

execute immediate v_qry into v_ret using p_ename;

Проверка на соответствие ожидаемым значениям
Если пользователь передал номер департамента для выполнения операции DELETE, то сначала можно проверить, что такой департамент существует.

Аналогично, если в качестве значения параметра передается имя таблицы для удаления, пригодится проверка существования такой таблицы в базе данных путем выполнения обращения к представлению ALL_TABLES.

Для безопасного использования  строковых литералов полезно использовать  функцию DBMS_ASSERT.ENQUOTE_LITERAL, которая к переданной строке добавляет лидирующий и завершающий апострофы, одновременно контролируя отсутствие апострофов внутри строки.

Использование внутреннего преобразования формата

Если в процедуре, использующей динамический SQL, нет возможности использовать связанные переменные, и формирование оператора выполняется с помощью конкатенации, то в таком случае необходимо параметры преобразовывать в текст, используя внутреннее преобразование формата, которое не будет зависеть от настроек NLS, заданных внутри сессии.

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

Преобразование в строковый формат следует использовать для переменных  с типом DATE и NUMBER.