О программе DBA STAT веб приложение на основе APEX для сбора статистики по базам Oracle
Необходимые привилегии
grant CREATE DATABASE LINK to TEST; grant CREATE PUBLIC DATABASE LINK to TEST; grant DROP PUBLIC DATABASE LINK to TEST; grant select on V_$DBLINK to TEST; grant select on DBA_DB_LINKS to TEST; grant select on APEX_040200.WWV_FLOW_WORKSHEETS to TEST; grant select, update on APEX_040200.WWV_FLOW_WORKSHEET_COLUMNS to TEST; grant select on V_$SESSION to TEST; grant ALTER SYSTEM to TEST; grant execute on DBMS_LOCK to TEST;
Права для удаленного пользователя: grant select on v_$instance to ATEST; grant select on v_$backup_set to ATEST; grant select on v_$backup_piece to ATEST; grant select on V_$ARCHIVED_LOG to ATEST; grant select on DBA_DATA_FILES to ATEST; grant select on V_$RECOVERY_FILE_DEST to ATEST; grant select on v_$parameter to ATEST; grant select on filext$ to ATEST; grant select on dba_tablespaces to ATEST; grant select on dba_free_space to ATEST; grant select on v_$tempfile to ATEST; grant select on v_$tablespace to ATEST; grant select on v_$sort_segment to ATEST; grant dba to ATEST;
Настройки
BNAME_LOCAL — Названия брать с локальной таблицы (TRUE/FALSE) Если FALSE — всегда будет получать название с удаленной ДБ. Если нет этой настройки по умолчанию TRUE
BCHECK_SYSDATE — Обновление с проверкой даты (TRUE/FALSE) FALSE — не проверять дату, будет всегда обновлять данные. Если нет этой настройки по умолчанию TRUE.
SQL_SERVER_NAME — Запрос получения названия бд После каждой таблицы дописывать $$$ для подстановки dblink. Например: SELECT short_name FROM table$$$
DYN_PROCEDURE — название процедуры для динамического вызова. Длина 30 символов. Обязательный входной параметр — имя ссылки. В значении настройки указать без входного параметра. Например можно указать существующую процедуру P_TOTAL_SYNC.
DYN_PROCESSES — название процедуры для динамического процесса. Длина 30 символов. Аналогична DYN_PROСEDURE, но работает с кнопки «Dyn processes» в разделе «Statistics tsolletstion».
Корректировка плана запроса без изменения кода через Stored Outlines (всего 4 способа: Stored Outlines, SQL Profiles, SQL patch, SQL plan baseline). Stored Outlines подходит для разных версий и редакций Oracle – 9, 10, 11, EE, SE, XE. В Oracle 11g функционал Stored Outlines официально объявлен устаревшим, рекомендуется использовать SQL Plan Management (SQL plan baseline). В некоторых случаях, когда хинт неэффективный, но заменить его оперативно в запросе не представляется возможным (например, чужая разработка), имеется возможность, не меняя рабочий запрос в программном модуле, заменить хинт (хинты) в запросе, а также в его подзапросах, на эффективный хинт (хинты). Это прием — подмена хинтов (который известен, как использование хранимых шаблонов Stored Outlines). Но такая подмена должна быть временным решением до момента корректировки запроса, поскольку постоянная подмена хинта может привести к некоторому снижению производительности запроса.
—Создание public outline create public outline FNC_COUNTER_NOZ_OUTLINE4 on SELECT…
—проверить UNUSED/USED select * from dba_outlines select * from dba_outline_hints where name = ‘FNC_COUNTER_NOZ_OUTLINE4′
— включить использование public outline, категория DEFAULT alter system set use_stored_outlines=true; alter session set use_stored_outlines = true; — выключить alter system set use_stored_outlines=false; alter session set use_stored_outlines = false;
— удалить все outline из категории DEFAULT BEGIN DBMS_OUTLN.drop_by_cat (cat => ‘DEFAULT’); END; — удалить по названию drop public outline FNC_COUNTER_NOZ_OUTLINE4;
—для редактирования хинтов нужно создать private outline. select * from ol$; select * from ol$hints; select * from ol$nodes;
Статья написана на основе официальной документации фирмы Oracle. Oracle® Database 2 Day + Application Express Developer's Guide Release 3.0 Part Number B32470-01
v:1.0 2007.11.13
Oracle Application Express (Apex) - это инструмент ускоренной разработки Web приложений для базы данных Oracle. С Apex Вы можете создавать профессиональные приложения, даже с небольшим опытом программирования, Вам необходим только Web-браузер.
Ускоренная разработка обеспечивается за счет встроенных в Apex средств:
темы пользовательского интерфейса;
управление навигацией;
управление формами;
гибкие отчеты;
Понимание архитектуры Oracle Application Express
Oracle Application Express полностью размещается в базе данных, он включает данные в таблицах и PL/SQL код. В не зависимости от того запускаете ли вы среду разработки или уже готовое приложение, созданное в Oracle Application Express, выполняются одни и те же процессы. Ваш браузер посылает URL-запрос, который преобразуется в соответствующий вызов Application Express PL/SQL. База данных обрабатывает PL/SQL запрос и отправляет результат обратно в браузер в виде HTML. Эти операции выполняются всегда при запросе или получении страницы. Состояние сессии приложения хранится в базе данных под управлением Application Express. Особое специальное соединение не используется. Вместо этого, каждый запрос делается в новой сессии базы данных, затрачивая минимум ресурсов CPU.
Apache и Embedded PL/SQL Gateway
В разных версиях базы данных Oracle URL преобразуется по разному.
Версии младше Oracle Database 11.1 требуют Oracle HTTP Server (Apache) с плагином mod_plsql. Следующий рисунок иллюстрирует эту архитектуру.
В версии новее Oracle Database 11.1 или Oracle Database 10g Express Edition вы можете заменить Apache на Embedded PL/SQL gateway. Следующий рисунок иллюстрирует эту идею.
Embedded PL/SQL gateway выполняет функции Web-сервера и обеспечивает инфраструктуру, необходимую для создания динамических приложений. Embedded PL/SQL gateway содержит основные функции mod_plsql и не требует Oracle HTTP Server. Использование embedded PL/SQL gateway упрощает архитектуру и полностью исключает промежуточное звено.
Ядро Application Express
Ядро Application Express обрабатывает страницы и выполняет следующие задачи:
управление состоянием сессии;
сервис идентификации;
сервис авторизации;
управление потоком страниц;
проверка достоверности;
Окружение Oracle Application Express
Все пользователи Oracle Application Express работают с одной общей базой данных. Для доступа к базе достаточно Web-браузера, дополнительное программное обеспечение не требуется.
Workspaces (рабочее пространство)
Область, где вы разрабатываете приложения называется workspace (рабочее пространство). Workspace - это виртуальная частная база данных, позволяющая множеству пользователей работать с одной инстанцией Oracle Application Express, скрывая при этом свои объекты, данные и приложения.
В типовой среде разработки вы можете создать одно рабочее пространство для совместного использования всеми разработчиками. Однако, Вы можете создать персональное рабочее пространство для каждого разработчика или проекта. Персональное рабочее пространство будет доступно только тем пользователям, которые к нему прикреплены. Следующий рисунок иллюстрирует взаимосвязь между пользователями/разработчиками, рабочими пространствами и схемами базы данных.
При создании рабочего пространства необходимо указать в какой схеме оно будет располагаться, можно выбрать существующую или создать новую. Схема - это логический контейнер для объектов базы данных, таких как таблицы, представления и хранимые процедуры. Одна схема может быть связана с одним или несколькими рабочими пространствами.
Роли пользователей в Application Express
При установке Application Express в большой организации каждому пользователю надо назначить роль и определить привилегии.
В Application Express существуют следующие роли:
Workspace administrator - это пользователи, которые выполняют административные задачи рабочих пространств. Например, управление пользовательскими учетными записями, мониторинг активности, просмотр лог-файлов.
Developers (разработчики) - это пользователи, которые создают и изменяют приложения.
End users (конечные пользователи) - это пользователи, которые имеют доступ к приложениям без использования внешних идентификационных схем.
Oracle Application Express administrators - это суперпользователи, которые управляют всей инстанцией, используя приложение Application Express Administration Services.
Следующий рисунок показывает, как множество пользователей с различными ролями работают со средой разработки, административными сервисами и пользовательскими приложениями.
Разработка PL/SQL WEB-приложений
Статья написана на основе официальной документации фирмы Oracle. Oracle® Database Advanced Application Developer's Guide 11g Release 1 (11.1) Part Number B28424-02
v:1.0 2007.10.16
Обзор PL/SQL Web-приложений.
Обычно, Web-приложение это набор хранимых PL/SQL процедур, которые взаимодействуют с Web-бразузерами через протокол HTTP. Обработка потока PL/SQL Web-приложения похожа на обработку CGI PERL скрипта. Разработчики часто используют CGI скрипты для создания динамических страниц, однако в большинстве случаев такие скрипты не оптимально работают с базой данных Oracle. Технология PL/SQL приложений предоставляет эффективный и гибкий механизм работы с данными. Например, Вы можете использовать DML, динамический SQL, и курсоры.
Рисунок иллюстрирует следующие шаги:
Пользователь открывает Web-страницу, следуя гиперссылке, или вводит данные в форму. Обрабатывая эти действия, браузер отправляет HTTP запрос серверу. Запрос содержит требуемый URL.
В соответствии с полученным URL, HTTP-сервер вызывает хранимую в базе данных процедуру. Данные из формы передаются в процедуру.
Хранимая процедура запускает подпрограмму из PL/SQL Web Toolkit. Обычно, подпрограммы, такие как HTP.Print, генерируют Web-страницы динамически. Генерация страницы зависит от данных базы данных и входных параметров.
Web-приложение может быть полностью написано на PL/SQL с использованием следующих компонентов базы данных Oracle:
PL/SQL Gateway
PL/SQL Web Toolkit
PL/SQL Gateway PL/SQL Gateway обеспечивает возможность браузера выполнять хранимые процедуры, используя HTTP listener (прослушиватель). Gateway - это платформа, на которой разработчик создает и развертывает приложение.
mod_plsql mod_plsql - это реализация PL/SQL gateway. Этот модуль является плагином (plug-in) для Oracle HTTP Server и позволяет Web-браузерам вызывать хранимые процедуры PL/SQL. Oracle HTTP Server входит в состав Oracle Application Server и Oracle database.
Модуль mod_plsql позволяет использовать процедуры на PL/SQL для обработки HTTP -запросов и генерации ответов. В этом контексте HTTP-запрос это URL, который содержит значение параметра, который должен быть передан процедуре. PL/SQL Gateway передает процедуре параметр из URL и возвращает клиенту полученный от процедуры ответ (чаще всего HTML).
Некоторые преимущества использования mod_plsql в сравнении с embedded (встроенным) PL/SQL gateway.
Вы можете использовать mod_plsql в следующей системе: Oracle HTTP Server и Oracle database запущены на разных хостах, разделенных брандмауэрами (firewall). В случае embedded PL/SQL gateway такая конфигурация не возможна.
Embedded PL/SQL gateway не поддерживает некоторые возможности mod_plsql, например, кэширование динамического HTML, системный мониторинг и логирование в Common Log Format.
Embedded PL/SQL Gateway Вы можете использовать встроенную версию PL/SQL gateway. Embedded PL/SQL gateway работает с XML DB HTTP Listener в Oracle Database, предоставляет основные возможности mod_plsql и не требует наличия Oracle HTTP сервера. Embedded PL/SQL gateway конфигурируется в пакедже DBMS_EPG, который входит в состав PL/SQL Web Toolkit.
Некоторые преимущества использования embedded gateway в сравнении с mod_plsql.
Вы можете использовать PL/SQL Web приложения, например, Application Express, без установки сервера Oracle HTTP, таким образом упрощается инсталляция, конфигурирование и использование PL/SQL Web-приложений.
Вы используете тот же подход конфигурирования, что и при передаче ответа Oracle XML DB на FTP или HTTP-запрос.
PL/SQL Web Toolkit PL/SQL Web Toolkit - это интерфейс, который позволяет выполнять хранимые PL/SQL процедуры, используя mod_plsql. В ответ на запрос браузера в соответствии с командой пользователя процедура PL/SQL обновляет или получает данные из базы. PL/SQL Web Toolkit формирует HTML-ответ для браузера, обычно в формате файла загрузки или HTML для отображения. Web Toolkit API позволяет хранимой процедуре выполнять следующие действия:
Получить информацию о HTTP-запросе.
Генерировать HTML-заголовки content-type и mime-type.
Устанавливать cookies браузера.
Генерировать HTML - страницы.
Как Embedded PL/SQL Gateway обрабатывает запросы клиентов. На рисунке показано как Embedded PL/SQL Gateway обрабатывает HTTP-запросы клиентов.
Последовательность шагов.
От клиентского браузера Oracle XML DB HTTP Listener получает запрос на выполнение PL/SQL процедуры. Процедура может быть написана непосредственно на PL/SQL или неявно сгенерирована во время загрузки PL/SQL Server Page в базу и последующей компиляции.
XML DB HTTP Listener перенаправляет запрос в embedded PL/SQL gateway. Этот процесс описывается в virtual-path mapping (маршрутизация виртуальных путей) конфигурации.
Используя информацию из запроса и в соответствии с конфигурацией, embedded gateway определяет какую пользовательскую запись базы данных использовать для идентификации.
Embedded gateway готовит входные параметры и запускает PL/SQL процедуру приложения.
Процедура PL/SQL генерирует HTML страницу и заполняет ее данными.
Приложение отправляет страницу в embedded gateway.
Embedded gateway пересылает страницу в XML DB HTTP Listener.
XML DB HTTP Listener пересылает страницу клиентскому браузеру.
В отличии от mod_plsql, для обработки HTTP-запроса embedded gateway требует наличия Oracle XML DB Listener. Listener (слушатель) - это серверный процесс, похожий на Oracle Net Listener, который поддерживает Oracle Net Services, HTTP и FTP. Для конфигурирования XML DB Listener можно использовать Oracle Enterprise Manager или просто файл xdbconfig.xml.
Ошибки, возникающие при работе с СУБД, можно разделить на следующие группы:
ошибки, генерируемые системой (например, нехватка памяти или повторяющееся значение индекса);
ошибки, генерируемые приложением (например, невыполнение каких-либо условий и проверок).
В языке PL/SQL ошибки всех видов интерпретируются как исключительные ситуации, или исключения.
Исключения могут быть системными и пользовательскими:
Системные исключения
Пользовательские исключения
Определены в СУБД.
- неименованныеисключения - имеют только номера (ORA-02292)
- именованныеисключения – имеют как номера, так и названия (например, ORA-01403: NO_DATA_FOUND)
Определяются программистом в приложении. Имеют номер в диапазоне от -20999 до -20000 и текстовое описание. Инициируются с помощью RAISE_APPLICATION_ERROR
Предупреждающие сообщения при компиляции
Исполняющую среду возможно сконфигурировать таким образом, чтобы при компиляции программных модулей происходила выдача сообщений, предупреждающих о моментах, на которые следует обратить внимание - например, при попытке использования в хранимой процедуре уже неподдерживаемых возможностей PL/SQL.
Категории предупреждающих сообщений:
Категория
Описание
Пример
SEVERE
Условия, которые могут привести к неожиданным последствиям или некорректным результатам
Использование INTO при объявлении курсора
PERFORMANCE
Код, приводящий к снижению производительности
Использование значения VARCHAR2 для поля с типом NUMBER в операторе INSERT
INFORMATIONAL
Условия, которые не влияют на производительность, но усложняют чтение кода
Код, который никогда не будет выполнен
Конфигурирование производится посредством установки значения параметра 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
Именованные системные исключения
Название
Код
Название
Код
ACCESS_INTO_NULL
-6530
PROGRAM_ERROR
-6501
CASE_NOT_FOUND
-6592
ROWTYPE_MISMATCH
-6504
COLLECTION_IS_NULL
-6531
SELF_IS_NULL
-30625
CURSOR_ALREADY_OPEN
-6511
STORAGE_ERROR
-6500
DUP_VAL_ON_INDEX
-1
SUBSCRIPT_BEYOND_COUNT
-6533
INVALID_CURSOR
-1001
SUBSCRIPT_OUTSIDE_LIMIT
-6532
INVALID_NUMBER
-1722
SYS_INVALID_ROWID
-1410
LOGIN_DENIED
-1017
TIMEOUT_ON_RESOURCE
-51
NO_DATA_FOUND
+100
TOO_MANY_ROWS
-1422
NO_DATA_NEEDED
-6548
VALUE_ERROR
-6502
NOT_LOGGED_ON
-1012
ZERO_DIVIDE
-1476
Инициирование исключений
Программно инициировать исключение можно посредством оператора RAISE или процедуры RAISE_АРРLICATIОN_ERROR.
Оператор RAISE
С помощью оператора RAISE можно инициировать как собственные, так и системные исключения.
Оператор имеет три формы:
RAISE имя_исключения
Инициирование исключения, определенного в текущем блоке, а также инициирование системных исключений, объявленных в пакете STANDARD
RAISE имя_пакета.имя_исключения
Если исключение объявлено в любом другом пакете, отличном от STANDARD, имя исключения нужно уточнять именем пакета
RAISE
Не требует указывать имя исключения, но используется только в предложении WHEN раздела исключений. Этой формой оператора следует пользоваться, когда в обработчике исключений нужно повторно инициировать то же самое исключение
Использование процедуры RAISE_APPLICATION_ERROR
Для инициирования исключений, специфических для приложения, в Oracle существует процедура RAISЕ_APPLICATION_ERROR. Ее преимущество перед оператором RAISЕ (который тоже может инициировать специфические для приложения явно объявленные исключения) заключается в том, что она позволяет связать с номером исключения некоторое текстовое сообщение об ошибке.
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.
На что стоит обратить внимание
Если при выполнении нескольких DML-операций в SQL-среде возникает исключительная ситуация, то все операции, предшествующие ошибочному оператору, считаются выполненными корректно и не откатываются.
Если те же самые DML-операции обернуть в блок BEGIN ... END - тогда при возникновении исключительной ситуации на очередном DML-операторе все предыдущие успешно (!) выполненные операции откатываются. Откат происходит к моменту начала выполнения блока. Т.е. блок либо выполняется целиком, либо не выполняется совсем.
Если обработчик завершается с повторной инициацией исключительной ситуации (напр., WHEN OTHERS then raise), то все изменения, проделанные в блоке, откатываются.
Если же выход из блока происходит через обработку исключительной ситуации и повторной инициации исключительной ситуации не происходит (напр., WHEN OTHERS then null), то блок считается исполненным успешно и отката изменений, которые внутри него произошли, не будет (!!!). То есть результат работы операторов, предшествующих ошибочному оператору, останется в БД. Поэтому, если по бизнес-логике такого не нужно, то в обработчике исключения надо явно делать ROLLBACK.
Динамический SQL и динамический PL/SQL
Статическими называются жестко закодированные инструкции и операторы, которые не изменяются с момента компиляции программы. Инструкции динамического 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 аргумент[, аргумент]…];
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 состоит, как правило, из следующих шагов:
Связывание текста динамического оператора с курсором и его синтаксический анализ и разбор;
Связывание входных аргументов с переменными, содержащими реальные значения;
Связывание выходных значений с переменными вызывающего блока;
Указание переменных, в которые будут сохраняться выходные значения;
Takes an OPENed, PARSEd, and EXECUTEd cursor and transforms/migrates it into a PL/SQL manageable REF CURSOR (a weakly-typed cursor) that can be consumed by PL/SQL native dynamic SQL switched to use native dynamic 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;
Если вызвать ее с параметром 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;
Результат мы получим не тот, что ожидалось – из-за того, что наш запрос теперь стал выглядеть так:
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.
