Что такое pls ошибки oracle

The meaning of the various error message prefixes doesn’t seem to be clearly explained in the documentation. They are grouped in the error message manual but it doesn’t really say what each group relates to — though you can usually work it out from the messages themselves.

But you’re broadly right; at least that PLS errors are related to PL/SQL. ORA errors cover a wide spectrum and many of them can be because of ‘programming’ errors, depending on how you define that.

The error messages introducton says:

All messages displayed are prefixed by text that indicates which
program issued the message. For example, the prefix «ORA» shows that
the message was generated by the Oracle products.

Since this is the Oracle documentation, saying the errors are generated by Oracle products doesn’t add much, and the non-ORA codes come from Oracle products too.

The SQL*Plus user guide includes a slightly more relevant summary:

If the error is a numbered error beginning with the letters «ORA»,
look up the Oracle Database message in the Oracle Database Error
Messages guide or in the platform-specific Oracle documentation
provided for your operating system.

If the error is a numbered error beginning with the letters «PLS»,
look up the Oracle Database message in the Oracle Database PL/SQL
Language Reference.

… although the PLS errors are not in a separate document so that looks like a doc bug. The main error documentation includes both core ORA errors and many other prefixes from specific products, including PLS errors, and also notes that ‘messages are specific to one product and are documented in manuals for that product’.

If you go back to the Oracle 9i error messages you can see a description of that the prefixes mean — PLS is ‘PL/SQL and FIPS Messages’ according to that. It isn’t in later releases — maybe the lines between products were too blurred for it to be meaningful. I suspect it’s largely down to which group has responsibility for assigning the messages to the codes for a product, and to some extent maybe has historic significance where products were acquired and adapted into the Oracle portfolio (as I believe PL/SQL itself was originally).

The oerr tool’s usage statement calls the prefix a ‘facility’, which doesn’t help much either.

If you see a PLS error then it doesn’t even have to come from PL/SQL code you’ve written, or from a PL/SQL block. You can call a function from SQL that can get a PLS error, since they are (usually) implemented in PL/SQL. And you can get an ORA error while compiling PL/SQL.

So I’m not sure the distinction is entirely clear, but it’s also not very useful. Whichever prefix your error has, you can look it up in the error messages book and see what it means and how to resolve it

Предупреждения во время компиляции способны существенно упростить сопровождение вашего кода и снизить вероятность ошибок. Не путайте предупреждения компилятора с ошибками; с предупреждениями ваша программа все равно будет компилироваться и работать. Тем не менее при выполнения кода, для которого выдавались предупреждения, возможно неожиданное поведение или снижение производительности.

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

Пример

Очень полезное предупреждением компилятора PLW-06002 сообщает о наличии недостижимого кода. Рассмотрим следующую программу. Так как переменная salary инициализируется значением 10 000, условная команда всегда будет отправлять меня на строку 9. Строка 7 выполняться не будет:

1      PROCEDURE cant_go_there
2      AS
3         l_salary NUMBER := 10000;
4       BEGIN
5         IF l_salary > 20000
6         THEN
7           DBMS_OUTPUT.put_line ('Executive');
8         ELSE
9           DBMS_OUTPUT.put_line ('Rest of Us');
10         END IF;
11      END cant_go_there;

Если откомпилировать этот код в любой версии до Oracle Database 10g, компилятор просто сообщит о том, что процедура создана. Но если включить предупреждения компиляции в сеансе этой или более поздней версии, то при попытке откомпилировать процедуру будет получен следующий ответ от компилятора:

SP2-0804: Procedure created with compilation warnings

SQL> SHOW err
Errors for PROCEDURE CANT GO THERE:

LINE/COL   ERROR
--------   -----------------------
7/7        PLW-06002: Unreachable code

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

Включение предупреждений компилятора PL/SQL

Oracle позволяет включать и отключать предупреждения компилятора, а также указывать, какие виды предупреждений представляют интерес. Предупреждения делятся на три категории:

• Критичные — ситуации, которые могут привести к неожиданному поведению или получению неверных результатов (как, например, проблемы с псевдонимами параметров).
• Производительные — ситуации, способные вызвать проблемы с производительностью (например, указание значения VARCHAR2 для столбца NUMBER в команде UPDATE).
•  Информационные — ситуации, не влияющие на производительность или правильность выполнения кода, но которые стоит изменить ради того, чтобы упростить сопровождение.

Oracle позволяет включать и отключать предупреждения конкретной категории, всех категорий и даже конкретные предупреждения. Для этого используется команда ALTER DDL или встроенный пакет DBMS_WARNING.

Следующая команда включает предупреждения компиляции для системы в целом:

ALTER SYSTEM SET PLSQL_WARNINGS='string'

А следующая команда, например, включает предупреждения в вашей системе для всех категорий:

ALTER SYSTEM SET PLSQL_WARNINGS='ENABLE:ALL';

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

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

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE';

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

ALTER PROCEDURE hello COMPILE PLSQL_WARNINGS='ENABLE:ALL' REUSE SETTINGS;

Обязательно включите секцию REUSE SETTINGS, чтобы команда ALTER не влияла на все остальные настройки (например, уровень оптимизации).

Объединяя разные параметры, можно уточнять настройки с очень высоким уровнем детализации. Допустим, я хочу знать обо всех проблемах, относящихся к производительности, на данный момент не желаю отвлекаться на серверные проблемы, а предупреждение PLW-05005 (выход из функции без RETURN) должно рассматриваться как ошибка компиляции. Для этого вводится следующая команда:

ALTER SESSION SET PLSQL_WARNINGS=
   'DISABLE:SEVERE'
      ,'ENABLE:PERFORMANCE'
      ,'ERROR:05005';

Особенно полезна возможность интерпретации предупреждений как ошибок. Возьмем предупреждение PLW-05005; если оставить его без внимания при компиляции функции no_return (см. ниже), программа откомпилируется, и я смогу использовать ее в приложении:

SQL> CREATE OR REPLACE FUNCTION no_return
2      RETURN VARCHAR2
3   AS
4   BEGIN
5      DBMS_OUTPUT.PUT_LINE (
6         'Here I am, here I stay');
7   END no_return;
8   /
SP2-0806: Function created with compilation warnings 

SQL> SHOW ERR
Errors for FUNCTION NO_RETURN:

LINE/COL ERROR
-------------------------------------------------------
1/1      PLW-05005: function NO_RETURN returns without value at line 7

Если теперь изменить интерпретацию ошибки приведенной выше командой ALTER SESSION и перекомпилировать no_return, компилятор немедленно остановит попытку:

Warning: Procedure altered with compilation errors

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

ALTER PROCEDURE no_return COMPILE PLSQL_WARNINGS = 'error:6002' REUSE SETTINGS
/

Во всех этих разновидностях команды ALTER ключевое слово ALL может использоваться как простое и удобное обозначение всех категорий предупреждений:

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

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

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

Некоторые полезные предупреждения PL/SQL

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

Чтобы просмотреть полный список предупреждений для любой конкретной версии Oracle, найдите раздел PLW в книге «Error Messages» документации Oracle.

PLW-05000: несовпадение в NOCOPY между спецификацией и телом

Рекомендация NOCOPY сообщает базе данных Oracle, что вы, если это возможно, предпочли бы не создавать копии аргументов IN OUT. Отказ от копирования может повысить производительность программ, передающих большие структуры данных — например, коллекции или CLOB.

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

Пример кода, генерирующего это предупреждение:

PACKAGE plw5000 
IS
   TYPE collection_t IS
      TABLE OF VARCHAR2 (100);

   PROCEDURE proc (
      collection_in IN OUT NOCOPY 
         collection_t);
END plw5000;

PACKAGE BODY plw5000 
IS
   PROCEDURE proc (
      collection_in IN OUT
         collection_t)
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE ('Hello!');
   END proc;
END plw5000;

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

SQL> SHOW ERRORS PACKAGE BODY plw5000
Errors for PACKAGE BODY PLW5000:

LINE/COL  ERROR
-------- ---------------------------------------------------------------
3/20      PLW-05000: mismatch in NOCOPY qualification between specification
          and body

3/20      PLW-07203: parameter 'COLLECTION_IN' may benefit from use of the
          NOCOPY compiler hint

PLW-05001: предыдущее использование строки противоречит этому использованию

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

Возможно, вы скажете: «Да, я видел эту ошибку, но это именно ошибка компиляции, а не предупреждение». Собственно, вы правы — следующая программа не откомпилируется:

PROCEDURE plw5001
IS
   a BOOLEAN; 
   a PLS_INTEGER;
BEGIN
   a := 1;
   DBMS_OUTPUT.put_line ('Will not compile');
END plw5001;

Компилятор выдает ошибку PLS-00371 (в разделе объявлений разрешено не более одного объявления ‘A’).

Почему же для этой ситуации создано предупреждение? Попробуем удалить присваивание переменной с именем a:

SQL> CREATE OR REPLACE PROCEDURE plw5001
   2   IS
   3      a BOOLEAN;
   4      a PLS_INTEGER;
   5   BEGIN
   6      DBMS_OUTPUT.put_line	('Will not compile?');
   7   END plw5001;
   8   /
Procedure created.

Программа откомпилируется! База данных не выдает ошибку PLS-00371, потому что я не использую ни одну из переменных в своем коде. Предупреждение PLW-05001 устраняет этот недостаток, сообщая о том, что я объявляю одноименные переменные без использования:

SQL> ALTER PROCEDURE plw5001 COMPILE plsql_warnings = 'enable:all';
SP2-0805: Procedure altered with compilation warnings

SQL> SHOW ERRORS
Errors for PROCEDURE PLW5001:

LINE/COL   ERROR
--------------------------------------------------------------------------
4/4        PLW-05001: previous use of 'A' (at line 3) conflicts with this use

PLW-05003: фактический параметр с IN и NOCOPY может иметь побочные эффекты

Используя NOCOPY с параметром IN OUT, вы приказываете PL/SQL передавать аргумент по ссылке, а не по значению. Это означает, что любые изменения в аргументе вносятся непосредственно в переменную во внешней области действия. С другой стороны, при передаче «по значению» (ключевое слово NOCOPY отсутствует, или компилятор игнорирует рекомендацию NOCOPY) изменения вносятся в локальную копию параметра IN OUT. Когда программа завершается, изменения копируются в фактический параметр. (Если произойдет ошибка, измененные значения не копируются в фактический параметр.) Рекомендация NOCOPY повышает вероятность совмещения имен аргументов, то есть ссылки на один блок памяти по двум разным именам.

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

Возьмем следующую программу:

PROCEDURE	very_confusing	(
   arg1	       IN            VARCHAR2
   , arg2	IN   OUT         VARCHAR2
   , arg3	IN   OUT NOCOPY  VARCHAR2
)                                                               продолжение #
IS
BEGIN
   arg2 := 'Second value';
   DBMS_OUTPUT.put_line ('arg2 assigned, arg1 = ' || arg1); 
   arg3 := 'Third value';
   DBMS_OUTPUT.put_line ('arg3 assigned, argl = ' || argl);
END;

Программа достаточно проста: передаются три строки, две из которых объявлены как IN OUT; аргументам IN OUT присваиваются значения; после каждого присваивания выводится значение первого аргумента IN. Теперь я запускаю процедуру и передаю одну локальную переменную во всех трех параметрах:

SQL> DECLARE
   2      str VARCHAR2 (100) := 'First value';
   3   BEGIN
   4      DBMS_OUTPUT.put_line ('str before = ' || str);
   5      ery_confusing (str, str, str);
   6      DBMS_OUTPUT.put_line ('str after = ' || str);
   7   END;
   8   /
str before = First value 
arg2 assigned, arg1 = First value 
arg3 assigned, arg1 = Third value 
str after = Second value

Хотя процедура very_confusing продолжает выполняться, присваивание arg2 не отражается на значении аргумента arg1. Однако когда значение присваивается arg3, значение arg1 (аргумент IN) заменяется на «Third value»! Более того, при завершении very_confusing присваивание arg2 было применено к переменной str. Таким образом, при возврате управления во внешний блок переменной str присваивается значение «Second value», фактически заменяющее результат присваивания «Third value».

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

SQL> CREATE OR REPLACE PROCEDURE plw5003
   2   IS
   3      str VARCHAR2 (100) := 'First value';
   4   BEGIN
   5      DBMS_OUTPUT.put_line ('str before = ' || str);
   6      very_confusing (str, str, str);
   7      DBMS_OUTPUT.put_line ('str after = ' || str);
   8   END plw5003;
   9   /
SP2-0804: Procedure created with compilation warnings

SQL> SHOW ERR

Errors for PROCEDURE PLW5003:
LINE/COL   ERROR
--------   -----------------------------------------------------------------
6/4        PLW-05003: same actual parameter(STR and STR) at IN and NOCOPY
           may have side effects
6/4        PLW-05003: same actual parameter(STR and STR) at IN and NOCOPY
           may have side effects

PLW-05004: идентификатор также объявлен в пакете STANDARD или является встроенным в SQL

Многие разработчики PL/SQL не знают о пакете STANDARD и его влиянии на код PL/ SQL. Например, многие программисты считают, что такие имена, как INTEGER и TO_CHAR, являются зарезервированными словами языка PL/SQL. Однако на самом деле это тип данных и функция, объявленные в пакете STANDARD.

standard — один из двух пакетов по умолчанию в PL/SQL (другой — DBMS_STANDARD). Поскольку STANDARD является пакетом по умолчанию, вам не нужно уточнять ссылки на такие типы данных, как INTEGER, NUMBER, PLS_INTEGER и т. д., именем STANDARD — хотя при желании это можно сделать.

Предупреждение PLW-5004 сообщает об объявлении идентификатора с таким же именем, как у элемента STANDARD (или встроенным именем SQL; многие встроенные имена, хотя и не все, объявляются в STANDARD).

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

 
1   PROCEDURE plw5004
2   IS
3      INTEGER NUMBER;
4
4      PROCEDURE TO_CHAR
5      IS
6      BEGIN
7         INTEGER := 10;
8      END TO_CHAR;
9   BEGIN
10      TO_CHAR;
11   END plw5004;

Для этой процедуры компилятор выводит следующие предупреждения:

LINE/COL   ERROR
--------   ------------------------------------------------------------
3/4        PLW-05004: identifier INTEGER is also declared in STANDARD
           or is a SQL builtin
5/14       PLW-05004: identifier TO_CHAR is also declared in STANDARD
           or is a SQL builtin

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

PLW-05005: функция возвращает управление без значения

Очень полезное предупреждение — функция, не возвращающая значение, явно очень плохо спроектирована. Это одно из предупреждений, которые я бы рекомендовал интерпретировать как ошибку (синтаксис «ERROR:5005») в настройках PLSQL_WARNINGS. Мы уже рассматривали один пример такой функции: no_return. Тот код был тривиальным; во всем исполняемом разделе не было ни одной команды RETURN. Конечно, код может быть и более сложным. Тот факт, что команда RETURN не выполняется, может быть скрыт за завесой сложной условной логики.

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

1   FUNCTION no_return (
2      check_in IN	BOOLEAN)
3      RETURN VARCHAR2
4   AS
5   BEGIN
6      IF check_in
7      THEN
8         RETURN 'abc';
9      ELSE
10         DBMS_OUTPUT.put_line (
11         'Here I am, here I stay');
12      END IF;
13   END no_return;

База данных обнаружила логическую ветвь, не приводящую к выполнению RETURN, поэтому для программы выдается предупреждение. Файл plw5005.sql на сайте github содержит более сложную условную логику, которая демонстрирует, что предупреждение выдается и в более сложных программных структурах.

PLW-06002: недостижимый код

База данных Oracle теперь умеет проводить статический анализ программы для выявления строк кода, которые ни при каких условиях не получат управление во время выполнения. Это исключительно ценная информация, но иногда компилятор предупреждает о наличии проблемы в строках, которые на первый взгляд недостижимыми вовсе не являются. Более того, в описании действий, предпринимаемых для этой ошибки, говорится, что «предупреждение следует отключить, если большой объем кода был сделан недостижимым намеренно, а предупреждение приносит больше раздражения, чем пользы».

Пример такого предупреждения приводился ранее в разделе «Пример». Теперь рассмотрим следующий код:

1       PROCEDURE plw6002
2       AS
3         l_checking BOOLEAN := FALSE;
4      BEGIN
5         IF l_checking
6         THEN
7            DBMS_OUTPUT.put_line ('Never here...');
8         ELSE
9            DBMS_OUTPUT.put_line ('Always here...');
10            GOTO end_of_function;
11         END IF;
12         <<end_of_function>>
13         NULL;
14   END plw6002;

В Oracle Database 10g и выше для этой программы выдаются следующие предупреждения:

LINE/COL   ERROR
---------  ---------------------------------
5/7        PLW-06002:	Unreachable	code
7/7        PLW-06002:	Unreachable	code
13/4       PLW-06002:	Unreachable	code

Понятно, почему строка 7 помечена как недостижимая: l_checking присваивается значение FALSE, поэтому строка 7 выполняться не будет. Но почему строка 5 помечена как недостижимая? На первый взгляд этот код будет выполняться всегда! Более того, строка 13 тоже должна выполняться всегда, потому что GOTO передает управление этой строке по метке. И все же эта строка тоже помечена как недостижимая.

Такое поведение объясняется тем, что до выхода Oracle Database 11g предупреждение о недостижимости кода генерируется после его оптимизации. В Oracle Database 11g и выше анализ недостижимого кода стал намного более понятным и полезным.

Компилятор не вводит вас в заблуждение; говоря, что строка N недостижима, он сообщает, что она никогда не будет выполняться в соответствии со структурой оптимизированного кода.

Некоторые ситуации с недостижимым кодом не обнаруживаются компилятором. Пример:

FUNCTION plw6002 RETURN VARCHAR2 
AS
BEGIN
   RETURN NULL;
   DBMS_OUTPUT.put_line ('Never here...');
END plw6002;

Разумеется, вызов DBMS_OUTPUT.PUT_LINE недостижим, но в настоящее время компилятор не обнаруживает это обстоятельство — до версии 12.1.

PLW-07203: рекомендация NOCOPY может принести пользу в объявлении параметра

Как упоминалось ранее в отношении PLW-05005, использование NOCOPY для сложных, больших параметров IN OUT может улучшить производительность программ в некоторых условиях. Это предупреждение выдается для программ, у которых включение NOCOPY для параметров IN OUT может повысить эффективность выполнения. Пример такой программы:

PACKAGE plw7203 
IS
   TYPE collection_t IS TABLE OF VARCHAR2 (100);
   PROCEDURE proc (collection_in IN OUT collection_t);
END plw7203;

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

PLW-07204: преобразование типа столбца может привести к построению неоптимального плана запроса

Предупреждение выдается при вызове команд SQL из PL/SQL, при котором происходят неявные преобразования. Пример:

FUNCTION plw7204
   RETURN PLS_INTEGER 
AS
   l_count PLS_INTEGER;
BEGIN
   SELECT COUNT(*) INTO l_count 
      FROM employees 
   WHERE salary = '10000';
   RETURN l_count;
END plw7204;

С этим предупреждением тесно связано предупреждение PLW-7202 (тип передаваемого параметра приводит к преобразованию типа столбца).

PLW-06009: обработчик OTHERS не завершается вызовом RAISE или RAISE_APPLICATION_ERROR

Это предупреждение (добавленное в Oracle Database 11g) выводится тогда, когда в обработчике исключений OTHERS не выполняется та или иная форма RAISE (повторное инициирование того же исключения или инициирование другого исключения), и не вызывается RAISE_APPLICATI0N_ERR0R. Другими словами, существует большая вероятность того, что программа «поглощает» исключение и игнорирует его. Ситуации, в которых ошибки действительно должны игнорироваться, встречаются довольно редко. Чаще исключение должно передаваться во внешний блок:

FUNCTION plw6009
   RETURN PLS_INTEGER 
AS
   l_count PLS_INTEGER;
BEGIN
   SELECT COUNT ( * ) INTO l_count 
      FROM dual WHERE 1 = 2;

   RETURN l_count;
EXCEPTION
   WHEN OTHERS 
   THEN
      DBMS_OUTPUT.put_line ('Error!');
      RETURN 0;
END plw6009;

PLS-00103: Encountered the symbol “CREATE” error occurs while running a CREATE block using script, you have not used / (front slash) to let SQLPlus know when the block completes, as a PL/SQL block may contain many instances of ; (semicolon). The CREATE block in the script should be terminated with a front slash (/) to inform Oracle that the code block has been completed.

The error occurs if you try to create two or more PL/SQL objects (procedure, function, package, type, etc.) at the same time. In a single sql session, the PL/SQL objects cannot be created together. You attempt to create several PL/SQL objects at the same time. The error PLS-00103: Encountered the symbol “CREATE” occurs in oracle

The PL/SQL objects such as procedure, functions, package, type etc are container objects that can be used in another objects. A procedure might be called from another procedure. A function can be used within a process. If you try to create two PL/SQL container objects, you’ll get an error PLS-00103: Encountered the symbol “CREATE”.

The two or more ddl, dml, dcl commands can execute in a single session. If two or more PL/SQL objects can not be created in a single command. The oracle sql commands are independent that can not be used in another sql command. The PL/SQL commands can be stored in local variable and can be used for the manipulation.

The Problem

The PL/SQL objects can be used in another PL/SQL objects. For example, the function can be called within the procedure. A procedure can be used within the function. If you try to create PL/SQL objects together, all the object are stored in buffer that can be rolled back. Oracle will not allow to create a PL/SQL object that refers to an another object in the buffer. Oracle forces the object to be persisted.

create type type_student;
create type type_course;

Error

Type TYPE_STUDENT compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
2/1       PLS-00103: Encountered the symbol "CREATE" 
Errors: check compiler log

Solution 1

When creating two or more PL/SQL objects, such as a procedure, function, package, or type, the PL/SQL objects should be terminated with a “/.” If Oracle detects a “/” terminator in the PL/SQL code, the objects in the oracle buffer are persisted in the database before going to the next code. Each PL/SQL object will be executed in a separate Oracle buffer, one after the other. PL/SQL objects are saved as soon as they are created.

The ; terminates a SQL query, whereas the / executes whatever is currently in the “buffer.” As a result, when you use a ; and a /, the statement is executed twice.

create type type_student;
/
create type type_course;

Output

Type TYPE_STUDENT compiled

Type TYPE_COURSE compiled

Solution 2

If you try to create a few PL/SQL objects in the PL/SQL code, the PL/SQL objects can be created separately. By executing each PL/SQL object individually, the objects may be created one at a time. Run each command in sequence, ensuring that the objects are created before executing the next PL/SQL code.

Code

create type type_student;

Output

Type TYPE_STUDENT compiled

Code

create type type_course;

Output

Type TYPE_COURSE compiled

A blog post about Oracle error codes? Curiosities about them, even?

If you doubt that this might be possible or of interest, then answer these two questions:

Can an error in Oracle have more than one error code?

Are error codes positive or negative?

If you answered «yes» for the first and «yes and no» for the second, you probably don’t need to read this post.

Oracle Errors With More Than One Error Code?

Well that wouldn’t be very normalized, would it?

But it is true that there at least one error that has two different error codes associated with it, and it’s one of the most common «errors» you’ll encounter in your code: 

The NO_DATA_FOUND exception

When I execute a SELECT-INTO statement, Oracle will raise NO_DATA_FOUND if no row is found for the query. It will raise TOO_MANY_ROWS if more than one row is found.

So what error code is associated with NO_DATA_FOUND?

The following code demonstrates this curiosity. I create a table with no data. My SELECT-INTO, therefore, finds no rows and the error message displayed (there was no exception handler) shows that the error code is -1403 (or is it 1403? I explore that curiosity later).

CREATE TABLE t (n NUMBER)
/

DECLARE
   l_n   NUMBER;
BEGIN
   SELECT n INTO l_n FROM t;
END;
/

ORA-01403: no data found 

Now I will handle the exception, first with WHEN OTHERS then with WHEN NO_DATA_FOUND, and display the value returned by SQLCODE.

DECLARE
   l_n   NUMBER;
BEGIN
   SELECT n INTO l_n FROM t;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error code = ' || SQLCODE);
END;
/

Error code = 100

DECLARE
   l_n   NUMBER;
BEGIN
   SELECT n INTO l_n FROM t;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line ('Error code = ' || SQLCODE);
END;
/

Error code = 100

100, not -1403!

That’s certainly very odd. How can this be? Well, you what they say: go to the source for the best answer, and the source in this case is the code of the STANDARD package, which defines many datatypes and exceptions in the PL/SQL language. In STANDARD, you will find the following:

NO_DATA_FOUND exception;
    pragma EXCEPTION_INIT(NO_DATA_FOUND, 100);

    ....

  TOO_MANY_ROWS exception;
    pragma EXCEPTION_INIT(TOO_MANY_ROWS, '-1422');

That pragma is used to associate an error code with a named exception (explore this pragma on LiveSQL here). We can do that in our code as well (which I show you below, thereby introducing another oddity for NO_DATA_FOUND). In this case, the STANDARD package pre-defines a number of named exceptions that are commonly used. And as you can see, the association is made to 100 and not -1403.

I must confess I have not tracked down any official documentation on this, but it is pretty clear that 100 is the ANSI-standard error code for «no rows found». In fact, Intersystems elaborates a bit further as follow:

SQLCODE=100 indicates that the SQL operation was successful, but found no data to act upon. This can occur for a number of reasons. For a SELECT these include: the specified table contains no data; the table contains no data that satisfies the query criteria; or row retrieval has reached the final row of the table. For an UPDATE or DELETE these include: the specified table contains no data; or the table contains no row of data that satisfies the WHERE clause criteria. In these cases %ROWCOUNT=0.

Notice that it states that 100 indicates the «SQL operation was successful.» I like this because it helps explain why the error code is not, say, -100. It also reinforces the point that just because a query does not return any rows does not mean that there is an error. It’s just a data condition.

More NO_DATA_FOUND Oddities

Remember I said you could use the EXCEPTION_INIT pragma to associate a code with your own named exception? Here’s an example:

DECLARE  
   e_bad_date_format   EXCEPTION;  
   PRAGMA EXCEPTION_INIT (e_bad_date_format, -1830);  
BEGIN  
   DBMS_OUTPUT.put_line (TO_DATE ('2010 10 10 44:55:66', 'YYYSS'));  
EXCEPTION  
   WHEN e_bad_date_format  
   THEN  
      DBMS_OUTPUT.put_line ('Bad date format');  
END; 

I can even use this pragma to assign an error code already assigned a pre-defined exception name, like TOO_MANY_ROWS:

DECLARE  
   my_exception   EXCEPTION;  
   PRAGMA EXCEPTION_INIT (my_exception, -1422);  
BEGIN  
   RAISE my_exception;  
END;
/

ORA-01422: exact fetch returns more than requested number of rows 

And what about -1403? A big, fat no way!

DECLARE  
   my_exception   EXCEPTION;  
   PRAGMA EXCEPTION_INIT (my_exception, -1403);  
BEGIN  
   RAISE my_exception;  
END;
/

PLS-00701: illegal ORACLE error number -1403 for PRAGMA EXCEPTION_INIT 

DECLARE  
   my_exception   EXCEPTION;  
   PRAGMA EXCEPTION_INIT (my_exception, -1403);  
BEGIN  
   RAISE my_exception;  
END;
/

PLS-00701: illegal ORACLE error number -1403 for PRAGMA EXCEPTION_INIT 

But 100 works just fine.

DECLARE  
   my_exception   EXCEPTION;  
   PRAGMA EXCEPTION_INIT (my_exception, 100);  
BEGIN  
   RAISE my_exception;  
END;
/

ORA-01403: no data found 

Notice also that the inability to use EXCEPTION_INIT with -1403 manifests as a compile-time error («PLS») not a runtime Oracle error («ORA»).

Error Codes Negative or Positive?

I bet that most of you believe that error codes are (mostly) negative. That view would certainly be reinforced with code like this:

DECLARE  
   my_exception   EXCEPTION;  
   PRAGMA EXCEPTION_INIT (my_exception, -1422);  
BEGIN  
   RAISE my_exception;  
EXCEPTION
   WHEN OTHERS 
   THEN
       IF SQLCODE = -1422
       THEN
          DBMS_OUTPUT.PUT_LINE ('Negative!');
       END IF;
END; 
/

Negative!

Sure looks negative to me. And if I try to use that pragma with a positive number that is not 100 or 1 (the error code associated with a user-defined exception that has not been essociated with an error code by EXCEPTION_INIT), I get an error:

DECLARE  
   my_exception   EXCEPTION;  
   PRAGMA EXCEPTION_INIT (my_exception, 1422);  
BEGIN  
   RAISE my_exception;  
END; 
/

PLS-00701: illegal ORACLE error number 1422 for PRAGMA EXCEPTION_INIT

So, it’s OK, so error codes are generally negative. Everyone agreed on that? Well, maybe everyone but a few people who work(ed) at Oracle and maybe everything but a few features in Oracle Database.

It turns out that sometimes error codes are stored without that pesky «-«. Which is understandable, because there are clearly two ways to interpret the hyphen in this text:

ORA-01422

1. A negative sign
2. A hyphen

Let’s first consider the SQLERRM function. Most people use it to obtain the error message of the current error (though you would be better off using DBMS_UTILITY.FORMAT_ERROR_STACK or the UTL_CALL_STACK API).

Relatively few developers know that you can also pass an error code to SQLERRM and it will return the generic message associated with that code. Here’s an example:

BEGIN 
   DBMS_OUTPUT.put_line (SQLERRM (-1422)); 
END; 

ORA-01422: exact fetch returns more than requested number of rows

That’s nice. But what if I leave off the «-«?

BEGIN 
   DBMS_OUTPUT.put_line (SQLERRM (1422)); 
END; 

-1422: non-ORACLE exception 

Gee, that’s telling it like it is.

But why didn’t SQLERRM talk to SQL%BULK_EXCEPTIONS and get their story straight?

SQL%BULK_EXCEPTIONS is a pseudo-collection or records that is populated with any errors in the execution of FORALL statements. Check out my LiveSQL tutorial on bulk processing for lots more details.

Each record contains the index into the collection for each statement that failed along with the error code of the failure. Guess what? The error code is recorded without what that person clearly thought was a hyphen.

Notice in the code below (an excerpt from my LiveSQL script on SAVE EXCEPTIONS) I must multiply the error code value by -1 so that I can retrieve the error message.

EXCEPTION  
   WHEN std_errs.failure_in_forall  
   THEN  
      FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT  
      LOOP  
         DBMS_OUTPUT.put_line (  
              'Oracle error is '  
            || SQLERRM ( -1 * SQL%BULK_EXCEPTIONS (indx).ERROR_CODE));  
      END LOOP;  

      ROLLBACK;  
END; 

No big deal, once you aware of it. But….kind of odd, eh?

Well, there is some consistency in our inconsistency. If you use the LOG ERRORS feature for non-query DML (which allows you to suppress errors at the row level), then Oracle will automatically record the error code, message and more in an error logging table. And in this table, error codes are stored as unsigned integers, as you can see below in the output from this LiveSQL script on LOG ERRORS:

So those are my little discoveries on the nuances of error codes in Oracle Database.

Do you have your own story about Oracle error codes you’d like to share?

There is nothing more exhilarating than to be shot at without result. —Winston Churchill

Run-time errors arise from design faults, coding mistakes, hardware failures, and many other sources. Although you cannot anticipate all possible errors, you can plan to handle certain kinds of errors meaningful to your PL/SQL program.

With many programming languages, unless you disable error checking, a run-time error such as stack overflow or division by zero stops normal processing and returns control to the operating system. With PL/SQL, a mechanism called exception handling lets you «bulletproof» your program so that it can continue operating in the presence of errors.

This chapter contains these topics:

• Overview of PL/SQL Runtime Error Handling

• Advantages of PL/SQL Exceptions

• Summary of Predefined PL/SQL Exceptions

• Defining Your Own PL/SQL Exceptions

• How PL/SQL Exceptions Are Raised

• How PL/SQL Exceptions Propagate

• Reraising a PL/SQL Exception

• Handling Raised PL/SQL Exceptions

• Tips for Handling PL/SQL Errors

• Overview of PL/SQL Compile-Time Warnings

SET SERVEROUTPUT ON;

DECLARE
   stock_price NUMBER := 9.73;
   net_earnings NUMBER := 0;
   pe_ratio NUMBER;
BEGIN
-- Calculation might cause division-by-zero error.
   pe_ratio := stock_price / net_earnings;
   dbms_output.put_line('Price/earnings ratio = ' || pe_ratio);

EXCEPTION  -- exception handlers begin

-- Only one of the WHEN blocks is executed.

   WHEN ZERO_DIVIDE THEN  -- handles 'division by zero' error
      dbms_output.put_line('Company must have had zero earnings.');
      pe_ratio := null;

   WHEN OTHERS THEN  -- handles all other errors
      dbms_output.put_line('Some other kind of error occurred.');
      pe_ratio := null;

END;  -- exception handlers and block end here
/

DECLARE
   stock_price NUMBER := 9.73;
   net_earnings NUMBER := 0;
   pe_ratio NUMBER;
BEGIN
   pe_ratio :=
      case net_earnings
         when 0 then null
         else stock_price / net_earnings
      end;
END;
/

BEGIN
   SELECT ...
   SELECT ...
   procedure_that_performs_select();
   ...
EXCEPTION
   WHEN NO_DATA_FOUND THEN  -- catches all 'no data found' errors

DECLARE
   past_due EXCEPTION;

block_label.exception_name

DECLARE
   past_due EXCEPTION;
   acct_num NUMBER;
BEGIN
   DECLARE  ---------- sub-block begins
      past_due EXCEPTION;  -- this declaration prevails
      acct_num NUMBER;
     due_date DATE := SYSDATE - 1;
     todays_date DATE := SYSDATE;
   BEGIN
      IF due_date < todays_date THEN
         RAISE past_due;  -- this is not handled
      END IF;
   END;  ------------- sub-block ends
EXCEPTION
   WHEN past_due THEN  -- does not handle RAISEd exception
      dbms_output.put_line('Handling PAST_DUE exception.');
   WHEN OTHERS THEN
     dbms_output.put_line('Could not recognize PAST_DUE_EXCEPTION in this scope.');
END;
/

PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number);

DECLARE
   deadlock_detected EXCEPTION;
   PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
BEGIN
   null; -- Some operation that causes an ORA-00060 error
EXCEPTION
   WHEN deadlock_detected THEN
      null; -- handle the error
END;
/

raise_application_error(error_number, message[, {TRUE | FALSE}]);

DECLARE
   num_tables NUMBER;
BEGIN
   SELECT COUNT(*) INTO num_tables FROM USER_TABLES;
   IF num_tables < 1000 THEN
      /* Issue your own error code (ORA-20101) with your own error message. */
      raise_application_error(-20101, 'Expecting at least 1000 tables');
   ELSE
      NULL; -- Do the rest of the processing (for the non-error case).
   END IF;
END;
/

EXEC SQL EXECUTE
   /* Execute embedded PL/SQL block using host 
      variables my_emp_id and my_amount, which were
      assigned values in the host environment. */
   DECLARE
      null_salary EXCEPTION;
      /* Map error number returned by raise_application_error
         to user-defined exception. */
      PRAGMA EXCEPTION_INIT(null_salary, -20101);
   BEGIN
      raise_salary(:my_emp_id, :my_amount);
   EXCEPTION
      WHEN null_salary THEN
         INSERT INTO emp_audit VALUES (:my_emp_id, ...);
   END;
END-EXEC;

EXCEPTION
   WHEN invalid_number OR STANDARD.INVALID_NUMBER THEN 
      -- handle the error
END;

DECLARE
   out_of_stock   EXCEPTION;
   number_on_hand NUMBER := 0;
BEGIN
   IF number_on_hand < 1 THEN
      RAISE out_of_stock; -- raise an exception that we defined
   END IF;
EXCEPTION
   WHEN out_of_stock THEN
      -- handle the error
      dbms_output.put_line('Encountered out-of-stock error.');
END;
/

DECLARE
   acct_type INTEGER := 7;
BEGIN
   IF acct_type NOT IN (1, 2, 3) THEN
      RAISE INVALID_NUMBER;  -- raise predefined exception
   END IF;
EXCEPTION
   WHEN INVALID_NUMBER THEN
      dbms_output.put_line('Handling invalid input by rolling back.');
      ROLLBACK;
END;
/

BEGIN
   DECLARE  ---------- sub-block begins
     past_due EXCEPTION;
     due_date DATE := trunc(SYSDATE) - 1;
     todays_date DATE := trunc(SYSDATE);
   BEGIN
     IF due_date < todays_date THEN
        RAISE past_due;
     END IF;
   END;  ------------- sub-block ends
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
END;
/

ORA-06510: PL/SQL: unhandled user-defined exception

DECLARE
   salary_too_high  EXCEPTION;
   current_salary NUMBER := 20000;
   max_salary NUMBER := 10000;
   erroneous_salary NUMBER;
BEGIN
   BEGIN  ---------- sub-block begins
      IF current_salary > max_salary THEN
         RAISE salary_too_high;  -- raise the exception
      END IF;
   EXCEPTION
      WHEN salary_too_high THEN
         -- first step in handling the error
        dbms_output.put_line('Salary ' || erroneous_salary ||
          ' is out of range.');
        dbms_output.put_line('Maximum salary is ' || max_salary || '.');
         RAISE;  -- reraise the current exception
   END;  ------------ sub-block ends
EXCEPTION
   WHEN salary_too_high THEN
      -- handle the error more thoroughly
      erroneous_salary := current_salary;
      current_salary := max_salary;
     dbms_output.put_line('Revising salary from ' || erroneous_salary ||
       'to ' || current_salary || '.');
END;
/

EXCEPTION
   WHEN exception_name1 THEN  -- handler
      sequence_of_statements1
   WHEN exception_name2 THEN  -- another handler
      sequence_of_statements2
   ...
   WHEN OTHERS THEN           -- optional handler
      sequence_of_statements3
END;

EXCEPTION
   WHEN ... THEN
      -- handle the error
   WHEN ... THEN
      -- handle the error
   WHEN OTHERS THEN
      -- handle all other errors
END;

EXCEPTION
   WHEN over_limit OR under_limit OR VALUE_ERROR THEN
      -- handle the error

DECLARE
   credit_limit CONSTANT NUMBER(3) := 5000;  -- raises an exception
BEGIN
   NULL;
EXCEPTION
   WHEN OTHERS THEN
      -- Cannot catch the exception. This handler is never called.
      dbms_output.put_line('Can''t handle an exception in a declaration.');
END;
/

EXCEPTION
   WHEN INVALID_NUMBER THEN
      INSERT INTO ...  -- might raise DUP_VAL_ON_INDEX
   WHEN DUP_VAL_ON_INDEX THEN ...  -- cannot catch the exception
END;

DECLARE
   err_msg VARCHAR2(100);
BEGIN
   /* Get a few Oracle error messages. */
   FOR err_num IN 1..3 LOOP
     err_msg := SUBSTR(SQLERRM(-err_num),1,100);
     dbms_output.put_line('Error number = ' || err_num);
     dbms_output.put_line('Error message = ' || err_msg);
   END LOOP;
END;
/

DECLARE
   pe_ratio NUMBER(3,1);
BEGIN
   DELETE FROM stats WHERE symbol = 'XYZ';
   SELECT price / NVL(earnings, 0) INTO pe_ratio FROM stocks
      WHERE symbol = 'XYZ';
   INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio);
EXCEPTION
   WHEN ZERO_DIVIDE THEN
      NULL;
END;
/

DECLARE
   pe_ratio NUMBER(3,1);
BEGIN
   DELETE FROM stats WHERE symbol = 'XYZ';
   BEGIN  ---------- sub-block begins
      SELECT price / NVL(earnings, 0) INTO pe_ratio FROM stocks
         WHERE symbol = 'XYZ';
   EXCEPTION
      WHEN ZERO_DIVIDE THEN
         pe_ratio := 0;
   END;  ---------- sub-block ends
   INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio);
EXCEPTION
   WHEN OTHERS THEN
      NULL;
END;
/

DECLARE
   name   VARCHAR2(20);
   ans1   VARCHAR2(3);
   ans2   VARCHAR2(3);
   ans3   VARCHAR2(3);
   suffix NUMBER := 1;
BEGIN
   FOR i IN 1..10 LOOP  -- try 10 times
      BEGIN  -- sub-block begins
         SAVEPOINT start_transaction;  -- mark a savepoint
         /* Remove rows from a table of survey results. */
         DELETE FROM results WHERE answer1 = 'NO';
         /* Add a survey respondent's name and answers. */
         INSERT INTO results VALUES (name, ans1, ans2, ans3);
 -- raises DUP_VAL_ON_INDEX if two respondents have the same name
         COMMIT;
         EXIT;
      EXCEPTION
         WHEN DUP_VAL_ON_INDEX THEN
            ROLLBACK TO start_transaction;  -- undo changes
            suffix := suffix + 1;           -- try to fix problem
            name := name || TO_CHAR(suffix);
      END;  -- sub-block ends
   END LOOP;
END;
/

DECLARE
   stmt INTEGER;
   name VARCHAR2(100);
BEGIN
   stmt := 1;  -- designates 1st SELECT statement
   SELECT table_name INTO name FROM user_tables WHERE table_name LIKE 'ABC%';
   stmt := 2;  -- designates 2nd SELECT statement
   SELECT table_name INTO name FROM user_tables WHERE table_name LIKE 'XYZ%';
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      dbms_output.put_line('Table name not found in query ' || stmt);
END;
/

ALTER SYSTEM SET PLSQL_WARNINGS='ENABLE:ALL'; -- For debugging during development.
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:PERFORMANCE'; -- To focus on one aspect.
ALTER PROCEDURE hello COMPILE PLSQL_WARNINGS='ENABLE:PERFORMANCE'; -- Recompile with extra checking.
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL'; -- To turn off all warnings.
-- We want to hear about 'severe' warnings, don't want to hear about 'performance'
-- warnings, and want PLW-06002 warnings to produce errors that halt compilation.
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE','DISABLE:PERFORMANCE','ERROR:06002';

CREATE OR REPLACE PROCEDURE dead_code
AS
  x number := 10;
BEGIN
  if x = 10 then
      x := 20;
  else
    x := 100; -- dead code (never reached)
  end if;
END dead_code;/
-- By default, the preceding procedure compiles with no errors or warnings.

-- Now enable all warning messages, just for this session.
CALL DBMS_WARNING.SET_WARNING_SETTING_STRING('ENABLE:ALL' ,'SESSION');

-- Check the current warning setting.
select dbms_warning.get_warning_setting_string() from dual;

-- When we recompile the procedure, we will see a warning about the dead code.
ALTER PROCEDURE dead_code COMPILE;