Sql error message oracle

7
Handling PL/SQL Errors

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 discusses the following topics:

Overview of PL/SQL Error Handling

In PL/SQL, a warning or error condition is called an exception. Exceptions can be internally defined (by the run-time system) or user defined. Examples of internally defined exceptions include division by zero and out of memory. Some common internal exceptions have predefined names, such as ZERO_DIVIDE and STORAGE_ERROR . The other internal exceptions can be given names.

You can define exceptions of your own in the declarative part of any PL/SQL block, subprogram, or package. For example, you might define an exception named insufficient_funds to flag overdrawn bank accounts. Unlike internal exceptions, user-defined exceptions must be given names.

When an error occurs, an exception is raised. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram. Internal exceptions are raised implicitly (automatically) by the run-time system. User-defined exceptions must be raised explicitly by RAISE statements, which can also raise predefined exceptions.

To handle raised exceptions, you write separate routines called exception handlers. After an exception handler runs, the current block stops executing and the enclosing block resumes with the next statement. If there is no enclosing block, control returns to the host environment.

In the example below, you calculate and store a price-to-earnings ratio for a company with ticker symbol XYZ. If the company has zero earnings, the predefined exception ZERO_DIVIDE is raised. This stops normal execution of the block and transfers control to the exception handlers. The optional OTHERS handler catches all exceptions that the block does not name specifically.

The last example illustrates exception handling, not the effective use of INSERT statements. For example, a better way to do the insert follows:

In this example, a subquery supplies values to the INSERT statement. If earnings are zero, the function DECODE returns a null. Otherwise, DECODE returns the price-to-earnings ratio.

Advantages of PL/SQL Exceptions

Using exceptions for error handling has several advantages. Without exception handling, every time you issue a command, you must check for execution errors:

Error processing is not clearly separated from normal processing; nor is it robust. If you neglect to code a check, the error goes undetected and is likely to cause other, seemingly unrelated errors.

With exceptions, you can handle errors conveniently without the need to code multiple checks, as follows:

Exceptions improve readability by letting you isolate error-handling routines. The primary algorithm is not obscured by error recovery algorithms. Exceptions also improve reliability. You need not worry about checking for an error at every point it might occur. Just add an exception handler to your PL/SQL block. If the exception is ever raised in that block (or any sub-block), you can be sure it will be handled.

Predefined PL/SQL Exceptions

An internal exception is raised implicitly whenever your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit. Every Oracle error has a number, but exceptions must be handled by name. So, PL/SQL predefines some common Oracle errors as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows.

To handle other Oracle errors, you can use the OTHERS handler. The functions SQLCODE and SQLERRM are especially useful in the OTHERS handler because they return the Oracle error code and message text. Alternatively, you can use the pragma EXCEPTION_INIT to associate exception names with Oracle error codes.

PL/SQL declares predefined exceptions globally in package STANDARD , which defines the PL/SQL environment. So, you need not declare them yourself. You can write handlers for predefined exceptions using the names in the following list:

Источник

8 Error Handling and Diagnostics

An application program must anticipate runtime errors and attempt to recover from them. This chapter provides an in-depth discussion of error reporting and recovery. You learn how to handle warnings and errors using the ANSI status variables SQLCODE and SQLSTATE, or the Oracle SQLCA (SQL Communications Area) structure. You also learn how to use the WHENEVER statement and how to diagnose problems using the Oracle ORACA (Oracle Communications Area) structure.

The following topics are discussed:

Why Error Handling is Needed

A significant part of every application program must be devoted to error handling. The main benefit of error handling is that it enables your program to continue operating in the presence of errors. Errors arise from design faults, coding mistakes, hardware failures, invalid user input, and many other sources

You cannot anticipate all possible errors, but you can plan to handle certain kinds of errors meaningful to your program. For Pro*COBOL, error handling means detecting and recovering from SQL statement execution errors. You must trap errors because the precompiler will continue regardless of the errors encountered unless you halt processing.

You can also prepare to handle warnings such as «value truncated» and status changes such as «end of data.» It is especially important to check for error and warning conditions after every data manipulation statement because an INSERT, UPDATE, or DELETE statement might fail before processing all eligible rows in a table.

Error Handling Alternatives

Pro*COBOL supports two general methods of error handling:

The Oracle-specific method with SQLCA and optional ORACA.

The SQL standard method with SQLSTATE status variable.

The precompiler MODE option governs compliance with the SQL standard. When MODE=, you declare the SQLSTATE status variable as PIC X(5). Additionally, the ANSI SQL-89 SQLCODE status variable is still supported, but it is not recommended for new programs because it has been removed from the SQL standard. When MODE=, you must include the SQLCA through an EXEC SQL INCLUDE statement. It is possible to use both methods in one program but usually not necessary.

For detailed information on mixing methods see «Status Variable Combinations».

SQLCA

The SQLCA is a record-like, host-language data structure which includes Oracle warnings, error numbers and error text. Oracle updates the SQLCA after every executable SQL or PL/SQL statement. (SQLCA values are undefined after a declarative statement.) By checking return codes stored in the SQLCA, your program can determine the outcome of a SQL statement. This can be done in two ways:

Implicit checking with the WHENEVER statement

Explicit checking of SQLCA variables

When you use the WHENEVER statement to implicitly check the status of your SQL statements, Pro*COBOL automatically inserts error checking code after each executable statement. Alternatively, you can explicitly write your own code to test the value of the SQLCODE member of the SQLCA structure. Include SQLCA by using the embedded SQL INCLUDE statement:

ORACA

When more information is needed about runtime errors than the SQLCA provides, you can use the ORACA, which contains cursor statistics, SQL statement text, certain option settings and system statistics. Include ORACA by using the embedded SQL INCLUDE statement:

The ORACA is optional and can be declared regardless of the MODE setting. For more information about the ORACA status variable, see «Using the Oracle Communications Area».

ANSI SQLSTATE Variable

When MODE=ANSI, you can declare the ANSI SQLSTATE variable inside the Declare Section for implicit or explicit error checking. If the option DECLARE_SECTION is set to NO, then you can also declare it outside of the Declare Section.

When MODE=ANSI, you can also declare the SQLCODE variable with a picture S9(9) COMP. While it can be used instead of or with the SQLSTATE variable, this is not recommended for new programs. You can also use the SQLCA with the SQLSTATE variable. When MODE=ANSI14, then SQLSTATE is not supported and you must declare either SQLCODE or include SQLCA. You cannot declare both SQLCODE and SQLCA for any setting of mode.

Declaring SQLSTATE

This section describes how to declare SQLSTATE. SQLSTATE must be declared as a five-character alphanumeric string as in the following example:

SQLSTATE Values

SQLSTATE status codes consist of a two-character class code followed by a three-character subclass code . Aside from class code 00 (successful completion), the class code denotes a category of exceptions. Aside from subclass code 000 (not applicable), the subclass code denotes a specific exception within that category. For example, the SQLSTATE value ‘22012’ consists of class code 22 (data exception) and subclass code 012 (division by zero).

Each of the five characters in a SQLSTATE value is a digit (0..9) or an uppercase Latin letter (A..Z). Class codes that begin with a digit in the range 0..4 or a letter in the range A..H are reserved for predefined conditions (those defined in the SQL standard). All other class codes are reserved for implementation-defined conditions. Within predefined classes, subclass codes that begin with a digit in the range 0..4 or a letter in the range A..H are reserved for predefined sub-conditions. All other subclass codes are reserved for implementation-defined sub-conditions. Figure 8-1 shows the coding scheme:

Figure 8-1 SQLSTATE Coding Scheme

Description of «Figure 8-1 SQLSTATE Coding Scheme»

Table 8-1 shows the classes predefined by the SQL standard.

Table 8-1 Predefined Classes

dynamic SQL error

triggered action exception

feature not supported

feature not supported

invalid target type specification

invalid schema name list specification

invalid SQL-invoked procedure reference

invalid role specification

invalid transform group name specification

target table disagrees with cursor specification

attempt to assign to non-updatable column

attempt to assign to ordering column

prohibited statement encountered during trigger execution

integrity constraint violation

invalid cursor state

invalid transaction state

invalid SQL statement name

triggered data change violation

invalid authorization specification

direct SQL syntax error or access rule violation

dependent privilege descriptors still exist

invalid character set name

invalid transaction termination

invalid connection name

SQL routine exception

invalid collation name

invalid SQL statement identifier

invalid SQL descriptor name

invalid cursor name

invalid condition number

cursor sensitivity exception

dynamic SQL syntax error or access rule violation

external routine exception

external routine invocation exception

ambiguous cursor name

invalid catalog name

invalid schema name

syntax error or access rule violation

with check option violation

remote database access

The class code HZ is reserved for conditions defined in International Standard ISO/IEC DIS 9579-2, Remote Database Access .

Table 8-4, «SQLSTATE Codes» shows how errors map to SQLSTATE status codes. In some cases, several errors map to the status code. In other cases, no error maps to the status code (so the last column is empty). Status codes in the range 60000..99999 are implementation-defined.

Using the SQL Communications Area

Oracle uses the SQL Communications Area (SQLCA) to store status information passed to your program at run time. The SQLCA is a record-like, COBOL data structure that is a updated after each executable SQL statement, so it always reflects the outcome of the most recent SQL operation. Its fields contain error, warning, and status information updated by Oracle whenever a SQL statement is executed. To determine that outcome, you can check variables in the SQLCA explicitly with your own COBOL code or implicitly with the WHENEVER statement.

When MODE=, the SQLCA is required; if the SQLCA is not declared, compile-time errors will occur. The SQLCA is optional when MODE=, but if you want to use the WHENEVER SQLWARNING statement, you must declare the SQLCA. The SQLCA must also be included when using multibyte NCHAR host variables.

When your application uses Oracle Net to access a combination of local and remote databases concurrently, all the databases write to one SQLCA. There is not a different SQLCA for each database. For more information, see «Concurrent Logons».

Contents of the SQLCA

The SQLCA contains runtime information about the execution of SQL statements, such as error codes, warning flags, event information, rows-processed count, and diagnostics.

Figure 8-2 shows all the variables in the SQLCA.

Figure 8-2 SQLCA Variable Declarations for Pro*COBOL

Description of «Figure 8-2 SQLCA Variable Declarations for Pro*COBOL»

Declaring the SQLCA

To declare the SQLCA, simply include it (using an EXEC SQL INCLUDE statement) in your Pro*COBOL source file outside the Declare Section as follows:

The SQLCA must be declared outside the Declare Section.

Do not declare SQLCODE if SQLCA is declared. Likewise, do not declare SQLCA if SQLCODE is declared. The status variable declared by the SQLCA structure is also called SQLCODE, so errors will occur if both error-reporting mechanisms are used.

When you precompile your program, the INCLUDE SQLCA statement is replaced by several variable declarations that allow Oracle to communicate with the program.

Key Components of Error Reporting

The key components of Pro*COBOL error reporting depend on several fields in the SQLCA.

Status Codes

Every executable SQL statement returns a status code in the SQLCA variable SQLCODE, which you can check implicitly with WHENEVER SQLERROR or explicitly with your own COBOL code.

Warning Flags

Warning flags are returned in the SQLCA variables SQLWARN0 through SQLWARN7, which you can check with WHENEVER SQLWARNING or with your own COBOL code. These warning flags are useful for detecting runtime conditions that are not considered errors.

Rows-Processed Count

The number of rows processed by the most recently executed SQL statement is returned in the SQLCA variable SQLERRD(3). For repeated FETCHes on an OPEN cursor, SQLERRD(3) keeps a running total of the number of rows fetched.

Parse Error Offset

Before executing a SQL statement, Oracle must parse it; that is, examine it to make sure it follows syntax rules and refers to valid database objects. If Oracle finds an error, an offset is stored in the SQLCA variable SQLERRD(5), which you can check explicitly. The offset specifies the character position in the SQL statement at which the parse error begins. The first character occupies position zero. For example, if the offset is 9, the parse error begins at the tenth character.

If your SQL statement does not cause a parse error, Oracle sets SQLERRD(5) to zero. Oracle also sets SQLERRD(5) to zero if a parse error begins at the first character (which occupies position zero). So, check SQLERRD(5) only if SQLCODE is negative, which means that an error has occurred.

Error Message Text

The error code and message for errors are available in the SQLCA variable SQLERRMC. For example, you might place the following statements in an error-handling routine:

At most, the first 70 characters of message text are stored. For messages longer than 70 characters, you must call the SQLGLM subroutine, which is discussed in «Getting the Full Text of Error Messages».

SQLCA Structure

This section describes the structure of the SQLCA, its fields, and the values they can store.

SQLCAID

This string field is initialized to «SQLCA» to identify the SQL Communications Area.

SQLCABC

This integer field holds the length, in bytes, of the SQLCA structure.

SQLCODE

This integer field holds the status code of the most recently executed SQL statement. The status code, which indicates the outcome of the SQL operation, can be any of the following numbers:

SQLERRM

This sub-record contains the following two fields:

Verify SQLCODE is negative before you reference SQLERRMC. If you reference SQLERRMC when SQLCODE is zero, you get the message text associated with a prior SQL statement.

This string field is reserved for future use.

SQLERRD

This table of binary integers has six elements. Descriptions of the fields in SQLERRD follow:

The rows-processed count is zeroed after an OPEN statement and incremented after a FETCH statement. For the EXECUTE, INSERT, UPDATE, DELETE, and SELECT INTO statements, the count reflects the number of rows processed successfully. The count does not include rows processed by an update or delete cascade. For example, if 20 rows are deleted because they meet WHERE-clause criteria, and 5 more rows are deleted because they now (after the primary delete) violate column constraints, the count is 20 not 25.

SQLERRD(4) This field is reserved for future use. SQLERRD(5) This field holds an offset that specifies the character position at which a parse error begins in the most recently executed SQL statement. The first character occupies position zero. SQLERRD(6) This field is reserved for future use.

SQLWARN

This table of single characters has eight elements. They are used as warning flags. Oracle sets a flag by assigning it a ‘W’ (for warning) character value. The flags warn of exceptional conditions.

For example, a warning flag is set when Oracle assigns a truncated column value to an output host character variable.

Figure 8-2, «SQLCA Variable Declarations for Pro*COBOL» illustrates SQLWARN implementation in Pro*COBOL as a group item with elementary PIC X items named SQLWARN0 through SQLWARN7.

Descriptions of the fields in SQLWARN follow:

To find out if a column value was truncated and by how much, check the indicator variable associated with the output host variable. The (positive) integer returned by an indicator variable is the original length of the column value. You can increase the length of the host variable accordingly.

SQLWARN2 This flag is set if one or more NULLs were ignored in the evaluation of a SQL group function such as AVG, COUNT, or MAX. This behavior is expected because, except for COUNT(*), all group functions ignore NULLs. If necessary, you can use the SQL function NVL to temporarily assign values (zeros, for example) to the NULL column entries. SQLWARN3 This flag is set if the number of columns in a query select list does not equal the number of host variables in the INTO clause of the SELECT or FETCH statement. The number of items returned is the lesser of the two. SQLWARN4 This flag is no longer in use. SQLWARN5 This flag is set when an EXEC SQL CREATE

statement fails because of a PL/SQL compilation error. SQLWARN6 This flag is no longer in use. SQLWARN7 This flag is no longer in use.

SQLEXT

This string field is reserved for future use.

PL/SQL Considerations

When your Pro*COBOL program executes an embedded PL/SQL block, not all fields in the SQLCA are set. For example, if the block fetches several rows, the rows-processed count, SQLERRD(3), is set to 1, not the actual number of rows fetched. So, you should rely only on the SQLCODE and SQLERRM fields in the SQLCA after executing a PL/SQL block.

Getting the Full Text of Error Messages

Regardless of the setting of MODE, you can use SQLGLM to get the full text of error messages if you have explicitly declared SQLCODE and not included SQLCA. The SQLCA can accommodate error messages up to 70 characters long. To get the full text of longer (or nested) error messages, you need the SQLGLM subroutine.

If connected to a database, you can call SQLGLM using the syntax

where the parameters are:

All parameters must be passed by reference. This is usually the default parameter passing convention; you need not take special action.

The maximum length of an error message is 512 characters including the error code, nested messages, and message inserts such as table and column names. The maximum length of an error message returned by SQLGLM depends on the value specified for MAX-SIZE.

The following example uses SQLGLM to get an error message of up to 200 characters in length:

In the example, SQLGLM is called only when a SQL error has occurred. Always make sure SQLCODE is negative before calling SQLGLM. If you call SQLGLM when SQLCODE is zero, you get the message text associated with a prior SQL statement.

If your application calls SQLGLM to get message text, the message length must be passed. Do not use the SQLCA variable SQLERRML. SQLERRML is a PIC S9(4) COMP integer while SQLGLM and SQLIEM expect a PIC S9(9) COMP integer. Instead, use another variable declared as PIC S9(9) COMP.

DSNTIAR

DB2 provides an assembler routine called DSNTIAR to obtain a form of the SQLCA that can be displayed. For users migrating to Oracle from DB2, Pro*COBOL provides DSNTIAR. The DSNTIAR implementation is a wrapper around SQLGLM. The DSNTIAR interface is as follows

where MESSAGE is the output message area, in VARCHAR form of size greater than or equal to 240, and LRECL is a full word containing the length of the output messages, between 72 and 240. The first half-word of the MESSAGE argument contains the length of the remaining area. The possible error codes returned by DSNTIAR are listed in the following table.

Table 8-2 DSNTIAR Error Codes and Their Meanings

More data was available than could fit into the provided message

The logical record length (LRECL) was not between 72 and 240

The message area was not large enough (greater than 240)

WHENEVER Directive

By default, Pro*COBOL ignores error and warning conditions and continues processing, if possible. To do automatic condition checking and error handling, you need the WHENEVER statement.

With the WHENEVER statement you can specify actions to be taken when Oracle detects an error, warning condition, or «not found» condition. These actions include continuing with the next statement, PERFORMing a paragraph, branching to a paragraph, or stopping.

Conditions

You can have Oracle automatically check the SQLCA for any of the following conditions.

SQLWARNING

SQLWARN(0) is set because Oracle returned a warning (one of the warning flags, SQLWARN(1) through SQLWARN(7), is also set) or SQLCODE has a positive value other than +1403. For example, SQLWARN(1) is set when Oracle assigns a truncated column value to an output host variable.

Declaring the SQLCA is optional when MODE=. To use WHENEVER SQLWARNING, however, you must declare the SQLCA.

You have to have included SQLCA for this to work.

SQLERROR

SQLCODE has a negative value if Oracle returns an error.

NOT FOUND or NOTFOUND

SQLCODE has a value of +1403 (or +100 when MODE= or when END_OF_FETCH=100) when the end of fetch has been reached. This can happen when all the rows that meet the search criteria have been fetched or no rows meet that criteria.

You may use the END_OF_FETCH option to override the value use by the MODE macro option.

For more details, see «END_OF_FETCH».

Actions

You can use the WHENEVER statement to specify the following actions.

CONTINUE

Your program continues to run with the next statement if possible. This is the default action, equivalent to not using the WHENEVER statement. You can use it to «turn off» condition checking.

DO CALL

Your program calls a nested subprogram. When the end of the subprogram is reached, control transfers to the statement that follows the failed SQL statement.

DO PERFORM

Your program transfers control to a COBOL section or paragraph. When the end of the section is reached, control transfers to the statement that follows the failed SQL statement.

GOTO or GO TO

Your program branches to the specified paragraph or section.

Your program stops running and uncommitted work is rolled back.

Be careful. The STOP action displays no messages before logging off.

Though in the generated code EXEC SQL WHENEVER SQLERROR STOP is converted to IF SQLCODE IN SQLCA IS EQUAL TO 1403 THEN STOP RUN END-IF, Oracle server will take care of rolling back uncommitted data.

Coding the WHENEVER Statement

Code the WHENEVER statement using the following syntax:

DO PERFORM

When using the WHENEVER . DO PERFORM statement, the usual rules for PERFORMing a paragraph or section apply. However, you cannot use the THRU, TIMES, UNTIL, or VARYING clauses.

For example, the following WHENEVER . DO statement is invalid :

In the following example, WHENEVER SQLERROR DO PERFORM statements are used to handle specific errors:

Notice how the paragraphs check variables in the SQLCA to determine a course of action.

DO CALL

This clause calls an action subprogram. Here is the syntax of this clause:

The following restrictions or rules apply:

You cannot use the RETURNING, ON_EXCEPTION, or OVER_FLOW phrases in the USING clause.

You may have to enter the subprogram name followed by the keyword COMMON in the PROGRAM-ID statement of your COBOL source code.

You must use a WHENEVER CONTINUE statement in the action subprogram.

The action subprogram name may have to be in double quotes in the DO CALL clause of the WHENEVER directive.

Here is an example of a program that can call the error subprogram SQL-ERROR from inside the subprogram LOGON, or inside the MAIN program, without having to repeat code in two places, as when using the DO PERFORM clause:

Scope

Because WHENEVER is a declarative statement, its scope is positional, not logical. It tests all executable SQL statements that follow it in the source file, not in the flow of program logic. So, code the WHENEVER statement before the first executable SQL statement you want to test.

A WHENEVER statement stays in effect until superseded by another WHENEVER statement checking for the same condition.

Suggestion : You can place WHENEVER statements at the beginning of each program unit that contains SQL statements. That way, SQL statements in one program unit will not reference WHENEVER actions in another program unit, causing errors at compile or run time.

Careless Usage: Examples

Careless use of the WHENEVER statement can cause problems. For example, the following code enters an infinite loop if the DELETE statement sets the NOT FOUND condition, because no rows meet the search condition:

In the next example, the NOT FOUND condition is properly handled by resetting the GOTO target:

Getting the Text of SQL Statements

In many Pro*COBOL applications, it is convenient to know the text of the statement being processed, its length, and the SQL command (such as INSERT or SELECT) that it contains. This is especially true for applications that use dynamic SQL.

The routine SQLGLS, which is part of the SQLLIB runtime library, returns the following information:

The text of the most recently parsed SQL statement

The length of the statement

A function code

You can call SQLGLS after issuing a static SQL statement. With dynamic SQL Method 1, you can call SQLGLS after the SQL statement is executed. With dynamic SQL Method 2, 3, or 4, you can call SQLGLS after the statement is prepared.

To call SQLGLS, you use the following syntax:

Источник

Adblock
detector

Как отлавливать ошибки в Oracle (PLSQL) EXCEPTION,SQLERRM,SQLCODE

Осваиваем Oracle и PL/SQL

EXCEPTION
   WHEN наименование_ошибки_1 THEN
      [statements]

   WHEN наименование_ошибки_2 THEN
      [statements]

   WHEN наименование_ошибки_N THEN
      [statements]

   WHEN OTHERS THEN
      [statements]

END [наименование_процедуры];

Вы можете использовать функции SQLERRM и SQLCODE для вызова сообщения об ошибке например таким образом:

EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'Произошла ошибка - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
-- В данном случае появится всплывающее сообщение.

Или вы можете сохранить сообщение об ошибке в таблицу таким образом:

EXCEPTION
   WHEN OTHERS THEN
      err_code := SQLCODE;
      err_msg := SUBSTR(SQLERRM, 1, 200);

      INSERT INTO error_log_table (error_time, error_number, error_message)
      VALUES (sysdate, err_code, err_msg);
END;
--В данном случае ошибка будет помещена в таблицу error_log_table

Варианты основных возможных ошибок:

DUP_VAL_ON_INDEX
ORA-00001
При попытке произвести вставку INSERT или изменение UPDATE данных которое создает дублирующую запись нарушающую уникальный индекс (unique index).

TIMEOUT_ON_RESOURCE
ORA-00051
Происходит когда ресурс над которым производится операция заблокирован и произошел сброс по таймауту.

TRANSACTION_BACKED_OUT
ORA-00061
Произошёл частичный откат транзакции.

INVALID_CURSOR
ORA-01001
Попытка использовать курсор которого не существует. Может происходить если вы пытаетесь использовать FETCH курсор или закрыть CLOSE курсор до того как вы этот курсор открыли OPEN.

NOT_LOGGED_ON
ORA-01012
Попытка произвести действия не залогинившись.

LOGIN_DENIED
ORA-01017
Неудачная попытка залогиниться, в доступе отказано, не верный пользователь или пароль.

NO_DATA_FOUND
ORA-01403
Что то из перечисленного: Попытка произвести вставку SELECT INTO несуществующего набора значений (select — ничего не возвращает). Попытка доступа к неинициализированной строке/записи в таблице. Попытка чтения записи после окончания файла при помощи пакета UTL_FILE.

TOO_MANY_ROWS
ORA-01422
Попытка вставить значение в переменную при помощи SELECT INTO и select вернул более чем одно значение.

ZERO_DIVIDE
ORA-01476
Попытка деления на ноль.

INVALID_NUMBER
ORA-01722
Попытка выполнить SQL запрос который производит конвертацию строки (STRING) в число (NUMBER) при этом такое преобразование невозможно.

STORAGE_ERROR
ORA-06500
Либо нехватка памяти, либо ошибка в памяти.

PROGRAM_ERROR
ORA-06501
Внутренняя программная ошибка рекомендуется с такой ошибкой обращаться в службу поддержки Oracle.

VALUE_ERROR
ORA-06502
Попытка выполнить операцию конвертации данных которая закончилась с ошибкой (например: округление, преобразование типов, и т.п.).

CURSOR_ALREADY_OPEN
ORA-06511
Вы пытаетесь открыть курсор который уже открыт.

Вот пожалуй и всё.

Oracle
PLSQL
SQL
EXCEPTION
SQLERRM
SQLCODE
ошибки
программирование

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;