Exception Handling in PL/SQL

In any procedural language, it is critical to remember that the programs are a complete and separate entity from the database. Hence, whenever the program requests rows from the database, the programmer must make sure that the request completed successfully.

In relational databases, the database will always pass a special variable called SQLCODE back to any calling program. The SQLCODE returned from reach call from the program to the database is translated by Oracle into a named Boolean variable (See table below).

PL/SQL Exception Variable
ACCESS_INTO_NULL
COLLECTION_IS_NULL
CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
ROWTYPE_MISMATCH
SUBSCRIPT_BEYOND_COUNT
SUBSCRIPT_OUTSIDE_LIMIT
SYS_INVALID_ROWID
TIMEOUT_ON_RESOURCE

For example, if the database returns a SQLCODE=100, the PL/SQL variable NO_DATA_FOUND will be set to TRUE.

Without exception, all PL/SQL programs should be made to abort whenever an unexpected SQLCODE is returned by the Oracle database.

This can have a disastrous effect on the database, especially when the PL/SQL loads data into tables based upon false premises. To prevent this tragedy, Oracle provides a WHEN OTHERS variable, which is set to TRUE if any unexpected SQLCODE is returned from the Oracle database.

For example, consider the following code:

DECLARE
err_num NUMBER;
err_msg VARCHAR2(100);
BEGIN
...
EXCEPTION
...
WHEN OTHERS THEN
err_num:= SQLCODE;
err_msg:= SUBSTR(SQLERRM, 1, 100);
INSERT INTO errors VALUES (err_num, err_msg);
END;

Here we see that our exception handling has an EXCEPTIONS area testing WHEN OTHERS. If the WHEN OTHERS Boolean variable is TRUE, the PL/SQL code captures the SQLCODE and the associated error message (SQLERRM), and stores these values ​​into a special Oracle errors table.

Oracle Exception Handling

Developers often flag error conditions and handle them using Oracle exception handling and the use of IF-THEN logic.

Oracle exception handling using IF-THEN logic to flag errors

The above example illustrates Oracle exception handling using the boolean variable bAidAmountOk to keep track of a condition throughout the processing of each student record.

This use of Oracle exception handling has an impact on performance. Oracle exception handling uses multiple instructions to test for the error condition. Each Oracle exception handling instruction requires CPU cycles to complete. A much better approach involves the use of Oracle exception handling to avoid wasting CPU cycles, as seen below:

Using Oracle exception handlers to improve performance.

In this example of Oracle exception handling, the xAID_AMOUNT_OK exception is explicitly raised inside the loop. This allows execution to skip the instructions that occur after the student's GPA is checked, cutting down on the cpu used in the Oracle exception handling.

Oracle exception handling is highly performance efficient. When an Oracle exception is raised, all subsequent instructions within the block are bypassed so the exception can be handled by an Oracle exception handler. Oracle exception handling can be utilized to significantly boost performance.

Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum . Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their . Errata? performance tuning

Copyright 1996 - 2017

All rights reserved by Burleson

® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational

In this chapter, we will discuss Exceptions in PL/SQL. An exception is an error condition during a program execution. PL/SQL supports programmers to catch such conditions using EXCEPTION block in the program and an appropriate action is taken against the error condition. There are two types of exceptions −

• System-defined exceptions
• User-defined exceptions

Syntax for Exception Handling

The general syntax for exception handling is as follows. Here you can list down as many exceptions as you can handle. The default exception will be handled using WHEN others THEN −

DECLARE BEGIN EXCEPTION WHEN exception1 THEN exception1-handling-statements WHEN exception2 THEN exception2-handling-statements WHEN exception3 THEN exception3-handling-statements ........ WHEN others THEN exception3-handling-statements END;

example

Let us write a code to illustrate the concept. We will be using the CUSTOMERS table we had created and used in the previous chapters −

DECLARE c_id customers.id%type:= 8; c_name customerS.Name%type; c_addr customers.address%type; BEGIN SELECT name, address INTO c_name, c_addr FROM customers WHERE id = c_id; DBMS_OUTPUT.PUT_LINE("Name: "|| c_name); DBMS_OUTPUT.PUT_LINE("Address: " || c_addr); EXCEPTION WHEN no_data_found THEN dbms_output. put_line("No such customer!"); WHEN others THEN dbms_output. put_line("Error!"); END; /

No such customer! PL/SQL procedure successfully completed.

The above program displays the name and address of a customer whose ID is given. Since there is no customer with ID value 8 in our database, the program raises the run-time exception NO_DATA_FOUND, which is captured in the EXCEPTION block.

Raising Exceptions

Exceptions are raised by the database server automatically whenever there is any internal database error, but exceptions can be raised explicitly by the programmer by using the command RAISE. Following is the simple syntax for raising an exception −

DECLARE exception_name EXCEPTION; BEGIN IF condition THEN RAISE exception_name; END IF; EXCEPTION WHEN exception_name THEN statement; END;

You can use the above syntax in raising the Oracle standard exception or any user-defined exception. In the next section, we will give you an example on raising a user-defined exception. You can raise the Oracle standard exceptions in a similar way.

User-defined Exceptions

PL/SQL allows you to define your own exceptions according to the need of your program. A user-defined exception must be declared and then raised explicitly, using either a RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.

The syntax for declaring an exception is −

DECLARE my-exception EXCEPTION;

example

The following example illustrates the concept. This program asks for a customer ID, when the user enters an invalid ID, the exception invalid_id is raised.

DECLARE c_id customers.id%type:= &cc_id; c_name customerS.Name%type; c_addr customers.address%type; -- user defined exception ex_invalid_id EXCEPTION; BEGIN IF c_id<= 0 THEN RAISE ex_invalid_id; ELSE SELECT name, address INTO c_name, c_addr FROM customers WHERE id = c_id; DBMS_OUTPUT.PUT_LINE ("Name: "|| c_name); DBMS_OUTPUT.PUT_LINE ("Address: " || c_addr); END IF; EXCEPTION WHEN ex_invalid_id THEN dbms_output.put_line("ID must be greater than zero!"); WHEN no_data_found THEN dbms_output.put_line("No such customer!"); WHEN others THEN dbms_output.put_line("Error!"); END; /

When the above code is executed at the SQL prompt, it produces the following result −

Enter value for cc_id: -6 (let "s enter a value -6) old 2: c_id customers.id%type:= &cc_id; new 2: c_id customers.id%type:= -6; ID must be greater than zero !PL/SQL procedure successfully completed.

Pre-defined Exceptions

PL/SQL provides many pre-defined exceptions, which are executed when any database rule is violated by a program. For example, the predefined exception NO_DATA_FOUND is raised when a SELECT INTO statement returns no rows. The following table lists a few of the important pre-defined exceptions −

Exception	Oracle Error	SQLCODE	Description
ACCESS_INTO_NULL	06530	-6530	It is raised when a null object is automatically assigned a value.
CASE_NOT_FOUND	06592	-6592	It is raised when none of the choices in the WHEN clause of a CASE statement is selected, and there is no ELSE clause.
COLLECTION_IS_NULL	06531	-6531	It is raised when a program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values ​​to the elements of an uninitialized nested table or varray.
DUP_VAL_ON_INDEX	00001	-1	It is raised when duplicate values ​​are attempted to be stored in a column with unique index.
INVALID_CURSOR	01001	-1001	It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor.
INVALID_NUMBER	01722	-1722	It is raised when the conversion of a character string into a number fails because the string does not represent a valid number.
LOGIN_DENIED	01017	-1017	It is raised when a program attempts to log on to the database with an invalid username or password.
NO_DATA_FOUND	01403	+100	It is raised when a SELECT INTO statement returns no rows.
NOT_LOGGED_ON	01012	-1012	It is raised when a database call is issued without being connected to the database.
PROGRAM_ERROR	06501	-6501	It is raised when PL/SQL has an internal problem.
ROWTYPE_MISMATCH	06504	-6504	It is raised when a cursor fetches value in a variable having incompatible data type.
SELF_IS_NULL	30625	-30625	It is raised when a member method is invoked, but the instance of the object type was not initialized.
STORAGE_ERROR	06500	-6500	It is raised when PL/SQL ran out of memory or memory was corrupted.
TOO_MANY_ROWS	01422	-1422	It is raised when a SELECT INTO statement returns more than one row.
VALUE_ERROR	06502	-6502	It is raised when an arithmetic, conversion, truncation, or sizeconstraint error occurs.
ZERO_DIVIDE	01476	1476	It is raised when an attempt is made to divide a number by zero.

Ph.D. Vladimir Likhachev, Kaluga Pedagogical University named after K.E. Tsiolkovsky

For programs working with databases, it is important not only to correctly handle their errors, but also to generate informative messages about these errors. The presence of such messages allows you to quickly identify the causes and correct errors. This is especially true when working with the end user program, since in most cases he does not know not only the structure of a particular database, but also the theoretical foundations of relational databases.

Oddly enough, the situation with the formation of error messages in programs is quite often very different from the processing of errors themselves. When handling errors, it is usually possible to develop a common strategy, which allows you to localize their processing in one or more functions. A similar approach for error messages can be implemented on the basis that in the error message the Oracle server indicates the type of error and the database object that caused it. Such objects are usually constraints, such as primary, unique and foreign keys, unique indexes, "not null" constraints, etc. Detailed information about these constraints can be obtained from the system tables and database views and the values ​​that can be changed and changed can be determined. led to an error. But the problem is that the implementation of such a mechanism for generating error messages in real applications encounters a number of difficulties:

• The dependence of the error message on the purpose of the program. Even for programs working with the same database, it may be necessary to generate different messages about the same error. For example, in a program for editing user data, the message should be: "A product with this name is already registered! Check the product name!". But in the data import program, a message with a completely different content is required: "Imported data is duplicated - check the date for which the data is being imported!".
• Difficulty generating messages for some errors caused by database limitations. For example, CHECK constraints on tables can use fairly complex queries and conditions. Therefore, the formation of messages based on their analysis can be quite a difficult task.
• Using custom names of tables and columns in client programs that are different from their names in the database. For example, the table has the name "GOODS", and in the client application, the data of this table can be displayed in the directory as "Products" or "Products".

The combination of these factors usually leads to the fact that the formation of messages even about the same type of errors is implemented individually for each transaction. As a result, the code for generating error messages is distributed throughout the application, which complicates its maintenance. Due to the need to write code for almost every possible error, some of the errors that the developer is aware of turn out to be without corresponding messages to the user. As a result, sufficiently informative messages for the end user are formed only for some of the errors, in other cases, he has to be content, at best, with messages from the database server itself. Informativeness of such messages for the average user in most cases is not enough to identify the cause of the problem and eliminate it.

The method of generating informative error messages for the user considered in the article is quite universal; it can be implemented both in client applications and on the Oracle server side. It can be used in various types of programs, such as:

• Programs that use a special interface to enter and modify database data. In most cases, informative error messages can be obtained from an analysis of the database structure. This will inform the user about their cause with minimal effort on the part of developers and software.
• Programs with the ability to build arbitrary SQL queries by the user. The formation of messages based on the analysis of the database structure can be especially relevant for programs that are targeted at a wide range of users, including those with a low level of knowledge in this area. This will make error messages in SQL queries more understandable for the user.
• subject platforms. Using the methods described in the article will allow the subject platform itself to generate informative database error messages based on the analysis of its structure. This will make it possible to shorten the platform language code used to handle error situations. And errors that require special messages, but turned out to be without them, will be informative enough to make it much easier to identify their cause.

The problems of message formation described above can be solved if error messages are conventionally divided into two groups:

• universal messages that are formed based on the analysis of the database structure;
• special messages that are defined individually for each error.

The method of generating database error messages described in the article can be applied to many relational database servers. An example of its use for Firebird server databases is discussed in the article. If the client application is developed in Object Pascal (Delphi, Kylix, Free Pascal), then the capabilities of the JEDI library can be useful to identify the causes of unexpected errors.

1. Universal error messages caused by database restrictions

As mentioned above, the main idea of ​​​​creating universal messages is to form a message that is sufficiently informative and understandable for the end user based on the data from the error message from Oracle and the database structure. Let's assume that in the table "GOODS" (script 1.1) the user tries to add a product with a name (column "TITLE"), which is already in the table.

CREATE TABLE DEMO.GOODS (CODE INTEGER NOT NULL , TITLE VARCHAR2(50 byte) NOT NULL , PRICE NUMBER(16, 2) NOT NULL , CONSTRAINT CK_PRICE CHECK (PRICE > 0), CONSTRAINT PK_GOODS PRIMARY KEY (CODE)); COMMENT ON TABLE DEMO.GOODS is "Goods"; COMMENT ON COLUMN DEMO.GOODS.CODE is "Product Code"; COMMENT ON COLUMN DEMO.GOODS.TITLE is "Title"; COMMENT ON COLUMN DEMO.GOODS.PRICE is "Price"; CREATE UNIQUE INDEX DEMO.IDX_GOODS_TITLE ON DEMO.GOODS(TITLE);

Script 1.1. Creation of the "GOODS" table.

The server will generate an error in this case, because the "TITLE" column, which stores the name of the product, is included in the unique index "DEMO.IDX_GOODS_TITLE":

Instead, a message for the user can be formed, for example, one of the messages:

• The value of the "Name" field in the "Products" table must be unique!
• A product with this name is already registered! Check the product name!
• Products with the same name cannot be in the product directory!

Although these messages differ, they all indicate information about the object for which the uniqueness constraint is violated - this is the "Name" field of the "Products" table.

One of the problems with generating this type of message is that custom field and table names are different from table and column names in the database. In order for the user to understand the error message, it must use custom names. A separate table or comments for tables and columns can be used to match table and field names and their custom names. The latter option can be considered more preferable, as it allows you to document the database at the same time. That is why in script 1.1, the user-defined names are given as comments for the table and its columns. If we compare the above messages and comments for the table and columns, we can see that the formation of the first message is the simplest option. To form the other two messages, lexical synthesis may be required, but this is a separate task. I would like to draw your attention to the fact that in the future in the article only one of the possible message options for each error case is given. In practice, the choice of message style and content may depend on a number of factors and will be determined by the system designer.

Of course, it cannot be ruled out that there are no comments for a table or column that should be included in the message. In this situation, the error message may display the name of the table or column directly.

2. The value of the required field is not specified (restriction NOT NULL)

This error is generated by the server in several cases:

• the "not null" constraint on the column was violated;
• no value was specified for a column that is part of a unique index, master key, or unique key.

In all these cases, the server generates an error:

To get the table and column description from the error message, query 2.1 can be used.

select tc.comments as table_comment, cc.comments as column_comment from all_tab_columns c, all_tab_comments tc, all_col_comments cc c.owner and cc.table_name = c.table_name and cc.column_name = c.column_name

Request 2.1. Get a table and column description

As query parameters "owner", "table_name", "column_name" you must specify the name of the schema, table and column from the error message, respectively. The query returns the comments for the table and column.

Using the results of this query, an error message can be generated, for example, the following content:

You must specify the value of the column "<Описание поля>"in the table"<Описание таблицы>" at<добавлении новой/изменении>records.

3. The uniqueness of the value of a field or a set of columns is broken

The need to enter a unique value for a column can be required mainly in three cases:

• the column is part of the master key;
• the column is included in the unique key;
• the column is included in a unique index.

In all three cases, Oracle Database generates the same error:
ORA-00001: unique constraint violated (<Схема>.<Ограничение>)

The error message specifies the constraint that caused the error. To obtain information about the columns included in the main or unique keys, you can use query 3.1, to get information about the index - query 3.2.

select dcs.constraint_type, cc.table_name, tc.comments as table_comment, cc.column_name, ccom.comments as column_comment from all_cons_columns cc join all_tab_comments tc on (tc.owner = cc.owner and tc.table_name = cc.table_name) join all_col_comments ccom on (ccom.owner = cc.owner and ccom.table_name = cc.table_name and ccom.column_name = cc. column_name) join all_constraints dcs on (dcs.constraint_name = cc.constraint_name) where cc.owner = :owner and cc.constraint_name = :key_name

Request 3.1. Obtaining information about the columns of the table included in the main or unique keys.

select ic.table_name, tc.comments as table_comment, ic.column_name, ccom.comments as column_comment from all_ind_columns ic join all_tab_comments tc on (tc.owner = ic.table_owner and tc.table_name = ic.table_name) join all_col_comments ccom on (ccom.owner = ic.table_owner and ccom.table_name = ic.table_name and ccom.column_name = ic. column_name) where table_owner = :owner and index_name = :index_name

Request 3.2. Getting information about the columns of the table included in the index.

Query parameters are schema name ("owner"), key name ("key_name") or index name ("index_name"). The queries return the names and comments for the tables and columns in the constraint. Query 3.1 also returns the constraint type ("constraint_type"): "P" - master key, "U" - unique key. The number of records returned by queries matches the number of columns in the unique constraint.

Based on the information received about the uniqueness constraint for the user, variants of error messages can be generated, for example, those given in section 1.

4. Errors Caused by Foreign Key Constraints

When performing operations on tabular data associated with foreign keys, there are several reasons that lead to errors:

1. A record is added to the subordinate table, in which the column included in the foreign key does not have a corresponding value in the main table. A similar situation occurs when changing the value of a column of a subordinate table if the new column value is not in the main table. Oracle Database in this case generates an error:

1. An attempt is made to change the value of a column in the parent table that is referenced in the child table. For this case, Oracle Database generates an error:

1. An attempt is made to delete data in the master table that is referenced in the child table. If a "NO ACTION" constraint is specified in the definition of a relationship between tables for a data delete operation, then Oracle does not allow deleting data from the main table if the child table has records associated with the record being deleted. For this situation, Oracle Database generates an error similar to the previous case.

You can use query 4.1 below to get information about the columns of the master and child tables that are part of the foreign key.

select a.constraint_name, a.table_name, tc1.comments as table_comment, a2.column_name, cc1.comments as column_comment, b.owner as r_owner, b.table_name as r_table_name, tc2.comments as r_table_comment, b2.column_name as r_column_name, cc2 .comments as r_column_comment from all_constraints a, all_constraints b, all_cons_columns a2, all_cons_columns b2, all_tab_comments tc1, all_col_comments cc1, all_tab_comments tc2, all_col_comments cc2 where a.owner = :owner and a.constraint_type = "R" and a.constraint_name = :foreign_key and in b.constraint_type ("P","U") and b.constraint_name = a.r_constraint_name and b.owner = a.r_owner and a2.constraint_name = a.constraint_name and a2.table_name = a.table_name and a2.owner = a.owner and b2.constraint_name = b.constraint_name and b2.table_name = b.table_name and b2.owner = b.owner and b2.position = a2.position and tc1.owner = a.owner and tc1.table_name = a.table_name and cc1. owner = a2.owner and cc1.table_name = a2.table_name and cc1.column_name = a2.column_name and tc2.owner = b.owner and tc2.table_name = b.table_name and cc2.owner = b2.owner and cc2.table_name = b2.table_name and cc2.column_name = b2.column_name

Request 4.1. Getting information about a foreign key.

The request has two parameters: "owner" and "foreign_key" - schema and foreign key, about which you want to get information. It returns information about the columns included in the foreign key: "table_name", "table_comment" - the name and description of the subordinate table; "column_name", "column_comment" - the name and description of the column of the subordinate table. Query columns prefixed with "r_" return information about the main table. The number of records returned by the query corresponds to the number of columns included in the foreign key.

Based on this information, foreign key error messages can be generated for the user.

5. Special error messages caused by database restrictions

The need to use special messages may arise if the universal error message for some reason cannot be used or cannot be generated. An example of the latter case is CHECK constraints on tables. In constrained conditions, queries and conditions can be used, the analysis of which can be quite a difficult task. Therefore, for these constraints, it is often more convenient to use messages that are defined at design time.

Two groups of special error messages can be distinguished. The first type of special messages is intended for use in all applications that work with a common database. They can be loosely referred to as "special database-level error messages". The second group of messages is application specific. They may be needed when different applications need to give the user different messages about the same error. They can be tentatively called "special application-level error messages". It is convenient to store information about the first group of messages in the database itself and use a separate table for this. Messages specific to a program can be stored in its resources, for example, as a separate file or also in a database. Special messages can be identified based on the error code, schema name, and one or more keywords from the error message.

6. CHECK constraint error messages for tables

When an error occurs due to a CHECK constraint on a table, the server generates an error:
ORA-02290: CHECK integrity constraint violated (<Схема>.<Имя ограничения>)

As mentioned above, it is often convenient to use special messages for such errors. For example, the "CK_PRICE" constraint on the "GOODS" table can use a custom message stored in the custom message table:

7. Comprehensive use of custom and generic error messages

A flexible mechanism for generating informative error messages for the user is implemented in several stages (Fig. 1):

1. Output a custom application-level error message. The program first searches for an error message among the specific messages for that application. If such a message is found, it is displayed, and the formation of the message is completed.

2. Output a special error message at the database level. If no message was found in step 1, a special database-level error message is searched for. If found, it is displayed to the user and the error message generation ends there.

3. Displaying a message based on the analysis of the database structure (universal message). If no special messages were found at the previous stages, then it is formed based on the analysis of the database structure. It is displayed to the user and this completes the formation of the message.

4. Displaying a message from the database server. If no message was generated for the user in the previous three steps, then an error message from Oracle is displayed. This situation may arise for several reasons. For example, when a custom error occurs that was intentionally generated in a stored procedure or trigger using the RAISE_APPLICATION_ERROR function, and the content of the message about which is not required to change.

More complex cases than the one given in this article are possible. For example, if the message is generated in a stored procedure, which in turn can be called from a trigger or another stored procedure. In this case, you may also need information about how the procedure that generated the error message was called. And therefore, the original message can be supplemented or changed, for example, based on information about the call stack of stored procedures and triggers.

In some cases, such messages can be even more informative than those generated at the previous stages. For example, instead of the CK_PRICE constraint for the DEMO.GOODS table (script 1.1), you can perform the necessary check in the trigger before inserting and updating the record and generate a message for the user in an already "ready" form:

If the price of a product is less than or equal to zero, the server will generate an error, for example:

The client application can immediately send this message to the user without modification.

Another reason may be the appearance of an error for which the formation of a message is not provided.

Rice. 1. The sequence of generating a database error message.

I would like to draw your attention to the fact that even if the application uses only special error messages, then the use of a common function for generating messages will improve the structure of the program. If necessary, the format of special messages can support links to the help system, pictures, etc. The described method of generating database error messages is focused more on implementation in the client application. At the same time, it can be used on the server side in stored procedures, table triggers, as well as in system triggers for the database or schema SERVERERROR event.

Conclusion

The purpose of this article is to show the main ideas of a method that can be used to generate informative Oracle database error messages for the end user. Although some aspects of implementation remained outside the scope of the article, I would like to hope that the approach described in the article will reduce labor costs in software development, improve its reliability and quality.

When exceptions occur, it is important to issue user-friendly error messages. Exceptions have already been mentioned in the section on basic PL/SQL blocks. Now it's time to consider them in more detail.

Exceptions

An exception is an error condition that is activated - or excited - when a problem occurs. There are many different exceptions, each associated with a specific type of problem. When an exception occurs, code execution stops at the statement that threw the exception, and control is transferred to the part of the block that handles the exception. If the block does not contain an executable section, PL/SQL tries to find an executable section in including base unit (enclosing basic block), i.e. in a block that is external to the code that threw the exception. If there is no handler for this exception in the immediate enclosing block, then the search continues in blocks of the next levels until a suitable handler is found, and if it cannot be found, then the program execution stops with an unhandled error message.

The exception-handling part of the block is the ideal place to issue informative error messages and execute cleaning (cleanup), which allows you to get rid of everything that could cause confusion or problems in the future. If an exception was thrown during the execution of a procedure that inserted a row into a table, then a typical cleanup procedure might include a ROLLBACK statement.

Once control has been transferred to an exception handler, it is no longer returned to the statement that caused the exception. Instead, control is transferred to the enclosing base block statement immediately following the nested block or procedure/function call.

System Exceptions

You are already familiar with the ZERO_DIVIDE exception predefined in PL/SQL. There are quite a few other system exceptions that are recognized and thrown by PL/SQL or Oracle. Table 1 provides a more complete list of system exceptions.

In PL/SQL, you can give error information to users in two ways. The first way is to use the SQLCODE command, which returns an error code. This code is a negative number, usually equal to the number of the ORA error that is printed when the application terminates if the exception is left unhandled. The second way is to return a text message describing the error. Not surprisingly, the corresponding command is called SQLERRM. Both SQLCODE and SQLERRM can be used in an exception handler. Note: Not all system exceptions have names.

Table1 . System Exceptions

system exception,error code	Reason for arousal
CURSOR_ ALREADY_ OPEN ORA-06511	Attempting to open an already open cursor
DUP_VAL_ON_INDEX ORA-00001	Attempting to insert a duplicate value into a column that has a unique index and therefore a unique constraint
INVALID_ CURSOR ORA-01001	Attempting to FETCH an unopened cursor, or attempting to close a cursor that hasn't been opened
NO_DATA_FOUND ORA-01403	Trying to SELECT INTO when SELECT returns zero rows (and other reasons that are beyond the scope of this book)
PROGRAM_ ERROR ORA-06501	Internal error. Usually means you need to contact Oracle Support
STORAGE_ ERROR ORA-06500	The program does not have enough system memory
TIMEOUT_ON_RESOURCE ORA-00051	The program waited too long for some resource to be available
TOO_MANY_ROWS ORA-01422	SELECT INTO in PL/SQL returned more than one row
VALUE_ ERROR ORA-06502	PL/SOL encountered an invalid data conversion or truncation, or an invalid data constraint
ZERO_ DIVIDE ORA-01476	Attempt to divide by zero
	All other exceptions and internal errors that are not covered by the exceptions defined in the base block. Used when you don't know exactly which named exception to handle and want to handle any exceptions that are thrown

Now let's go back to the very first example in this chapter and use SQLCODE and SQLERRM in it. Below is the source code of the example and the results of its launch (Fig. 1).

Num_a NUMBER:= 6;

Num_b NUMBER;

Num_b:= 0;

Num_a:= Num_a / Num_b;

Num_b:= 7;

dbms_output. put_line(" Value of Num_b "|| Num_b);

EXCEPTION

WHEN ZERO_DIVIDE THEN

err_num NUMBER:= SQLCODE;

err_msg VARCHAR2(512) := SQLERRM;

dbms_output. put_line("ORA Error Number " || err_num);

dbms_output. put_line("ORA Error message " || err_msg);

dbms_output.put_line("Value of Num_a " || Num_a);

dbms_output.put_line("Value of Num_b " || Num_b);

SQL> set server output on

SQL> DECLARE

2 num_a NUMBER:= 6;

3 num_b NUMBER;

4 BEGINS

5 num_b:= 0;

6 num_a:= num_a / num_b;

7 num_b:= 7;

8 dbms_output. put_line(" Value of num_b "|| num_b);

9 EXCEPTION

10 WHEN ZERO_DIVIDE

11 THEN

13 err_num NUMBER:= SQLCODE;

14 err_msg VARCHAR2(512) := SQLERRM;

15 BEGIN

16 dbms_output. put_line("ORA Error Number "|| err_num);

17 dbms_output. put_line("ORA Error message " || err_msg);

18 dbms_output. put_line("Value of num_a " || num_a);

19 dbms_output. put_line("Value of num_b " || num_b);

20END;

21END;

ORA Error Number -1476

ORA Error Message ORA-01476: divisor is equal to zero

Value of num_a 6

Value of num_b 0

PL/SQL procedure successfully completed.

Rice. one. Using SQLCODE and SQLERRM when handling system exceptions

Oracle database administrators and programmers sometimes face in their work that the database begins to "swear" and often in a language that is not entirely clear. In this article, we'll look at the Oracle Errors language and warning messages. All errors are grouped into large sections and subsections to help users quickly navigate what is happening and take measures to correct the situation. In this article, we will look at groups first thousand Oracle errors(by ranges codes from 0 to 999), as well as explain the reasons for their appearance with a proposal for actions to correct them. The format of the error is "ORA-xxxxx". At the beginning comes the prefix "ORA", and then through the dash a five-digit error code.

Oracle Error Groups

Error Messages ORA-00000 - ORA-00099

ORA-00000 messages are normal, successful completion, i.e., not an error.

Methodological Kernel Access Errors 0001-0049

• ORA-00001: duplicate key in index
• ORA-00017: maximum number of calls exceeded
• ORA-00018: maximum number of sessions exceeded
• ORA-00019: number of sessions exceeded number of licenses
• ORA-00020: maximum number of processes (num) exceeded
• ORA-00021 session is in use by another process. Don't switch session
• ORA-00022: invalid session id. Access is denied
• ORA-00023: Session contains private memory references. Can't disconnect session
• ORA-00024: connection to more than one process prohibited in single user mode
• ORA-00025: cannot place [string]
• ORA-00026: missing or invalid session id
• ORA-00027: unable to destroy current session
• ORA-00028: your session has been destroyed
• ORA-00029: session is not a user session
• ORA-00030: user session with specified ID does not exist
• ORA-00031: session marked for destruction
• ORA-00032: invalid session move password
• ORA-00033: current session with empty move password
• ORA-00034: unable to COMMIT or ROLLBACK in current PL/SQL session
• ORA-00035: LICENSE_MAX_USERS value cannot be less than the current number of users
• ORA-00036: maximum number of levels (value) of SQL recursion reached
• ORA-00037: unable to switch session to another server group
• ORA-00038: Unable to create session. The server group is owned by another user

ENQ errors 0050-0080

• ORA-00050: operating system error while getting queue
• ORA-00051: resource timed out
• ORA-00052: maximum number of returned resources exceeded
• ORA-00053: maximum number of queues exceeded
• ORA-00054: resource busy accessing NOWAIT pointer
• ORA-00055: maximum number of DML locks exceeded
• ORA-00056: A DDL lock is placed on a locked object
• ORA-00057: maximum number of temporary table locks exceeded
• ORA-00058: DB_BLOCK_SIZE must be equal to the database being mounted
• ORA-00059: DB_FILES parameter value exceeded
• ORA-00060: a deadlock occurred while waiting for a resource
• ORA-00061: different instance has different DML_LOCK settings
• ORA-00062: DML lock on entire table cannot be acquired. DML_LOCKS setting is null
• ORA-00063: LOG_FILES parameter value exceeded
• ORA-00064: object cannot be placed, it is too large for the operating system
• ORA-00065: error initializing parameter FIXED_DATE
• ORA-00066: LOG_FILES values ​​do not match
• ORA-00067: invalid value for string parameter, must be a string
• ORA-00068: invalid value for string parameter, must be string
• ORA-00069: cannot lock table - lock denied on [string]
• ORA-00070: command [string] invalid
• ORA-00071: number of processes must be greater than 1:
• ORA-00072: The specified process is not active
• ORA-00073: invalid number of arguments specified for command
• ORA-00074: no process defined
• ORA-00075: process [string] not found in current instance
• ORA-00076: dump [string] not found
• ORA-00077: The specified dump is invalid
• ORA-00078: unable to resolve dump by name
• ORA-00079: variable [value] not found
• ORA-00080: an attempt was made to dump an invalid area of ​​memory
• ORA-00081: specified range is not valid
• ORA-00082: memory range not in specified range
• ORA-00083: SGA possibly corrupted
• ORA-00084: Global Area must be PGA, SGA or UGA
• ORA-00085: current call does not exist
• ORA-00086: user call does not exist
• ORA-00087: command cannot be executed on remote instance
• ORA-00088: command cannot be executed by shared server
• ORA-00089: invalid instance number in ORADEBUG command
• ORA-00090: ORADEBUG command failed to correctly allocate memory in a clustered database
• ORA-00091: LARGE_POOL_SIZE must be specified
• ORA-00092: LARGE_POOL_SIZE must be greater than LARGE_POOL_MIN_ALLOC
• ORA-00093: %s specified incorrectly
• ORA-00094: %s must contain an Integer value
• ORA-00096: invalid value [value] for parameter [value], must be in range
• ORA-00097: Oracle SQL features not supported by SQL92:
• ORA-00099: The resource timed out. Potential PDML deadlock

Region and segment errors ORA-00100 - ORA-00299

• ORA-00100: no data found
• ORA-00101: invalid specification of system parameter DISPATCHERS
• ORA-00102: The specified network protocol cannot be used by dispatchers
• ORA-00103: invalid network protocol, reserved for use by dispatchers
• ORA-00104: deadlock detected, all available servers blocked, waiting for resource
• ORA-00105: too many dispatcher configurations
• ORA-00106: database cannot be started or stopped while connected to dispatcher
• ORA-00107: unable to connect to ORACLE listener process
• ORA-00108: unable to configure dispatcher to accept asynchronous connections
• ORA-00111: invalid attribute [string]
• ORA-00112: attribute value is null
• ORA-00113: protocol name [string] too long
• ORA-00114: invalid value for system parameter SERVICE_NAMES
• ORA-00115: connection dropped, connection table full
• ORA-00116: SERVICE_NAMES value is invalid
• ORA-00117: PROTOCOL, ADDRESS or DESCRIPTION must be specified
• ORA-00118: only one PROTOCOL, ADDRESS or DESCRIPTION value can be specified
• ORA-00119: invalid system parameter value
• ORA-00120: dispatcher not allowed or not installed
• ORA-00121: SHARED_SERVERS defined without DISPATCHERS option
• ORA-00122: unable to initialize network configuration
• ORA-00123: idle shared server
• ORA-00124: DISPATCHERS specified without MAX_SHARED_SERVERS
• ORA-00125: connection reset; misconception
• ORA-00126: connection reset; contradiction
• ORA-00127: dispatcher does not exist
• ORA-00128: command requires dispatcher name
• ORA-00129: invalid listen address
• ORA-00130: invalid listen address
• ORA-00131: network protocol does not support the specified registration
• ORA-00132: syntax error or invalid network name
• ORA-00150: duplicate transaction id
• ORA-00151: duplicate transaction id
• ORA-00152: current session does not match the requested session
• ORA-00153: error in XA library
• ORA-00154: protocol error in transaction monitor
• ORA-00155: cannot execute job outside of global transaction
• ORA-00160: global transaction name exceeds maximum length
• ORA-00161: transaction length invalid
• ORA-00162: external database id exceeds max value
• ORA-00163: external database name exceeds maximum value
• ORA-00164: distributed autonomous transactions not allowed on top of portable distributed transactions
• ORA-00165: offline portable distributed transactions with remote operations not allowed
• ORA-00200: control file could not be created
• ORA-00201: control file version [string] is incompatible with ORACLE version [string]
• ORA-00202: control file: [string]
• ORA-00203: invalid control file used
• ORA-00204: error reading data block (block [string], blocks [string]) in control file
• ORA-00205: control file identification failed. See log for more information
• ORA-00206: error writing to control file (block [string], blocks [string])
• ORA-00207: control file not from this database
• ORA-00208: number of control files exceeds allowed value [string]
• ORA-00209: control file data block did not match. See log for more information
• ORA-00210: Unable to open specified control file
• ORA-00211: control file does not match previous
• ORA-00212: block size [string] less than minimum required [string]
• ORA-00213: cannot reuse control file, file size %1: %2 required:
• ORA-00214: control file version incompatible with file version
• ORA-00215: there must be at least one control file
• ORA-00216: control file size cannot be changed for porting from 8.0.2:
• ORA-00217: control file size cannot be changed to migrate from 9.0.1:
• ORA-00218: data block size of control files does not match size specified by DB_BLOCK_SIZE
• ORA-00219: control file size exceeds set size
• ORA-00220: control file could not be included, see alert-log for more details
• ORA-00221: error writing to control file
• ORA-00222: operation tries to use the name of an already mounted control file
• ORA-00223: The file being converted is invalid or has the wrong version
• ORA-00224: failed resizing control file with wrong record type
• ORA-00225: expected size of control file [string] differs from actual size [string]
• ORA-00226: operation not possible while alternate control file is open
• ORA-00227: corrupt data block found in control file (block [string] blocks [string]).
• ORA-00228: length of alternate control file name exceeds allowed value [string]
• ORA-00229 operation denied. The snapshot file has already been queued and is being processed.
• ORA-00230 operation denied. Control file snapshot queue not available
• ORA-00231: control file snapshot not named
• ORA-00232: control file snapshot does not exist, is corrupt or unreadable
• ORA-00233: control file copy corrupted or unreadable
• ORA-00234: error identifying or opening snapshot or copying control file
• ORA-00235: control file blocked for parallel modification
• ORA-00236: snapshot canceled, fallback control file selected
• ORA-00237: snapshot operation not allowed. Created a new control file
• ORA-00238: operation tries to use filename as database name
• ORA-00250: archiver not running
• ORA-00251: LOG_ARCHIVE_DUPLEX_DEST cannot be the same directory as %1:
• ORA-00252: log is empty. Archiving is not possible
• ORA-00253: length of specified string exceeds limit
• ORA-00254: error in archive control line
• ORA-00255: error while archiving log file
• ORA-00256: An error occurred while parsing an archive string
• ORA-00257: archiver error. I can't connect while the resource is busy
• ORA-00258: Manual archiving in NOARCHIVELOG log must be specified
• ORA-00259: open instance log is current log and cannot be archived
• ORA-00260: cannot find active queue log [string] for thread [string]
• ORA-00261: log has been modified or archived
• ORA-00262: current log [string] is busy with another thread [string], cannot switch
• ORA-00263: no logs to archive for thread [string]
• ORA-00264: no restore needed
• ORA-00265: Instance recovery required, but ARCHIVELOG mode can be set
• ORA-00266: archive log name required
• ORA-00267: archive log name not required
• ORA-00268: The specified log file does not exist
• ORA-00269: Specified log file is part of stream [string] not [string]
• ORA-00270: error creating archive log [string]
• ORA-00271: no logs to archive
• ORA-00272: error writing to archive log [string]
• ORA-00273: Restoring data loaded directly without logging
• ORA-00274: invalid recovery parameter value [value] specified
• ORA-00275: recovery procedure already started
• ORA-00276: CHANGE keyword specified but no change number specified
• ORA-00277: invalid restore option UNTIL flag [string]
• ORA-00278: log file [string] is no longer required for recovery
• ORA-00279: log file name required
• ORA-00280: required stream and sequence name
• ORA-00281: restore cannot be performed using dispatcher
• ORA-00282: UPI call not supported, use ALTER DATABASE RECOVER
• ORA-00283: restore session canceled due to errors
• ORA-00284: recovery session in progress
• ORA-00285: invalid TIME value
• ORA-00286: no files available or valid data files
• ORA-00287: specified change number [string] not found in stream [string]
• ORA-00288: use ALTER DATABASE RECOVER CONTINUE to continue recovery
• ORA-00289: variable [value]
• ORA-00290: Operating system error. See error message below
• ORA-00291: PARALLEL requires a numeric value
• ORA-00292: parallel restore component not installed
• ORA-00293: control file out of sync with redo log
• ORA-00294: invalid format specified for archive log [string]
• ORA-00295: invalid data/temp data file number [string], must be between 1: and [string]
• ORA-00296: RECOVER DATAFILE LIST value exceeded
• ORA-00297: required to specify RECOVER DATAFILE LIST before RECOVER DATAFILE START
• ORA-00298: missing or invalid attribute value
• ORA-00299: file level restore of data file %1

Database I/O management errors ORA-00300 - ORA-00399

Errors in managing the entry to and exit from the Oracle database:

• ORA-00300: Invalid redo log block size, specified size [string] exceeds size limit [string]
• ORA-00301: error adding log file [string] - file could not be created
• ORA-00302: limit exceeded for number of redo files
• ORA-00303: unable to execute Parallel Redo
• ORA-00304: requested INSTANCE_NUMBER is busy
• ORA-00305: log [string] of thread [string] is inconsistent and belongs to another database
• ORA-00306: instance limit reached [string]
• ORA-00307: requested INSTANCE_NUMBER out of range, max [string]
• ORA-00308: unable to open archive log [string]
• ORA-00309: log belongs to another database
• ORA-00310: archived log contains sequence [string]; required sequence [value]
• ORA-00311: Unable to read archive log header
• ORA-00312: available log [string] thread [string]
• ORA-00313: error opening loggroup file [string] stream [string]
• ORA-00314: log [string], expected sequence [string] does not match [string]
• ORA-00315: log [string] stream [string], invalid value [string] in header
• ORA-00316: log [string], value [string] in header indicates that this is not a log file
• ORA-00317: specified file [string] is not a log file
• ORA-00318: log [string] exceeded allowed size [string] does not match [string]
• ORA-00319: log [string] has incorrect flush status
• ORA-00320: unable to read file header [string] of stream [string]
• ORA-00321: Unable to update data in log file header [string] stream [string]
• ORA-00322: log [string] of thread [string] is not the current copy
• ORA-00323: current stream log [string] is not usable and all others need to be archived
• ORA-00324: log file name [string] translated [string] too long, limit exceeded [string]
• ORA-00325: stream archive log [string] contains invalid header value [string]
• ORA-00326: log starts with [string], previously changed [string] required
• ORA-00327: log [string] of stream [string] has physical size [string] less than required [string]
• ORA-00328: archived log ends with [string], later change required [string]
• ORA-00329: archived log starts with [string], needs to change [string]
• ORA-00330: archive log ends with [string], change required [string]
• ORA-00331: log version [string] is not compatible with ORACLE version [string]
• ORA-00332: archive log very small, possibly not fully archived
• ORA-00333: redo log read [string] bad blocks from available [string]
• ORA-00334: archive log [string]
• ORA-00335: log available [string]: No log with this number, log does not exist
• ORA-00336: log file block size less than minimum block size [string]
• ORA-00337: log file [string] does not exist and size is not specified
• ORA-00338: log [string] greater than last control file value
• ORA-00339: archived log does not contain any retries
• ORA-00340: I/O error while processing log [string] of thread [string]
• ORA-00341: log file [string] has invalid header number [string]
• ORA-00342: archive log contains no SCN information [string]
• ORA-00343: too many errors, log closed
• ORA-00344: unable to recreate available log [string]
• ORA-00345: error writing to redo log, block [string] total [string]
• ORA-00346: journal marked as out of date (STALE)
• ORA-00347: log [string] of thread [string] has block size [string], does not match, should be [string]
• ORA-00348: failed redo recovery processing, instance must be stopped
• ORA-00349: Retrieving block size for [string] failed
• ORA-00350: log [string] (stream [string]) of instance [string] needs to be archived
• ORA-00351: invalid recovery time
• ORA-00352: all logs from stream [string] must be archived - cannot be allowed
• ORA-00353: corrupt log block [string] change [string] time [string]
• ORA-00354: redo log block header corrupted
• ORA-00355: change number out of sequence
• ORA-00356: invalid change description length
• ORA-00357: too many members specified for log file, max [string]
• ORA-00358: too many members specified, max [string]
• ORA-00359: log file group [string] does not exist
• ORA-00360: Not member of log file: [string]
• ORA-00361: cannot remove last log [string] from group [string]
• ORA-00362: required member is a valid log file in group [string]
• ORA-00363: log not archived
• ORA-00364: cannot write to new log header
• ORA-00365: The specified log is invalid
• ORA-00366: Log [string] stream [string], checksum error in file header
• ORA-00367: checksum error in file header
• ORA-00368: checksum error in redo log block
• ORA-00369: current log of thread [string] corrupted and other log cleared
• ORA-00371: not enough memory in shared pool, must be at least [string]
• ORA-00372: file [string] cannot be modified at this time
• ORA-00373: open log version [string] is incompatible with ORACLE version [string]
• ORA-00374: value of parameter db_block_size=[value] is invalid; must be composite in the range [value..value]
• ORA-00375: unable to get default value of db_block_size
• ORA-00376: file [string] cannot be read at this time
• ORA-00377: frequent backup of file [string] cause deadlock in write operations
• ORA-00378: buffer cache could not be created as specified
• ORA-00379: no free buffers in buffer cache [string] for block [string]K
• ORA-00380: cannot specify db_[value]k_cache_size [value]K is the standard block size.
• ORA-00381: cannot use old and new options to specify buffer cache size
• ORA-00382: %s invalid block size, valid value [str..value]
• ORA-00383: cache default value cannot be reduced to zero
• ORA-00384: not enough memory to grow cache
• ORA-00390: log [string] of thread [string] cleared and cannot be the current log
• ORA-00391: all threads from now on must switch to new log format
• ORA-00392: log [string] of thread [string] was cleared, operation not allowed
• ORA-00393: log [string] of thread [string] needed to restore an offline database.
• ORA-00394: available log is reused while archiving
• ORA-00395: log for database clone must be renamed
• ORA-00396: error [string] requires rollback to single pass restore
• ORA-00397: instance restore aborted with error
• ORA-00398: restore aborted before proper reconfiguration
• ORA-00399: corrupt description of redo log changes

Database initialization errors ORA-00400 - ORA-00499

• ORA-00400: invalid value [string] for parameter [string]
• ORA-00401: value for parameter [value] is not supported by this version
• ORA-00402: database changes of version [string] cannot be used by version [string]
• ORA-00403: [string] is not the same as other instances of [string]
• ORA-00404: Converted file not found: [string]
• ORA-00405: compatibility type [string]
• ORA-00406: COMPATIBLE option requires [string] or higher
• ORA-00407: Rollback upgrade from version [string].[string] to version [string].[string] not allowed
• ORA-00408: parameter value [string] set to TRUE
• ORA-00436: Oracle is not licensed. Contact Oracle Corporation for Help
• ORA-00437: Oracle advanced features not licensed. Contact Oracle Corporation for Help
• ORA-00438: option [string] not set
• ORA-00439: optional feature not allowed: [string]
• ORA-00443: background process [string] not running
• ORA-00444: background process [string] failed on startup
• ORA-00445: background process [string] did not start after [string] seconds elapsed
• ORA-00446: background process started when not expected
• ORA-00447: critical error in background process
• ORA-00448: normal termination of a background process
• ORA-00449: background process [string] terminated abruptly with error [string]
• ORA-00470: LGWR process exited with an error
• ORA-00471: DBWR process exited with an error
• ORA-00472: pmon process exited with an error
• ORA-00473: ARCH process exited with an error
• ORA-00474: smon process exited with an error
• ORA-00475: TRWR process exited with an error
• ORA-00476: RECO process exited with an error
• ORA-00477: SNP* process exited with an error
• ORA-00478: smon process exited with error [string]
• ORA-00480: LCK* process exited with error
• ORA-00481: LMON process exited with an error
• ORA-00482: LMD* process exited with an error
• ORA-00483: process terminated abnormally during shutdown
• ORA-00484: LMS* process exited with an error
• ORA-00485: DIAG process exited with error [string]

Error Messages ORA-00500 - ORA-00599

• ORA-00568: maximum number of handler interrupts exceeded

Disaster recovery errors ORA-00600 - ORA-00699

• ORA-00600: Internal error code, arguments [value], [value], [value], [value], [value], [value], [value], [value]
• ORA-00601: net lock conflict
• ORA-00602: internal software exception
• ORA-00603: Oracle server session was terminated by a fatal error
• ORA-00604: error occurred at recursive SQL level [string]
• ORA-00606: internal error code
• ORA-00607: Internal error while changing data block

Dictionary errors ORA-00700 - ORA-00799

• ORA-00701: object required to start database cannot be modified
• ORA-00702: loader version [string] incompatible with version [string]
• ORA-00703: maximum number of instance row cache locks exceeded
• ORA-00704: boot process ended abnormally
• ORA-00705: invalid state during startup; stop the instance and restart
• ORA-00706: error changing file format [string]

General ORACLE errors ORA-00800 - ORA-00899

• ORA-0800: buffer write process is not active(the process of writing to the buffer is not active).
  Cause: The problem is related to an attempt to start ORACLE, which caused the process of writing to the buffer to be removed. Typically, this message is issued along with a system error message explaining the reason for the failure.
  Action: Use the system error message (if any) to find out the cause of the error. If there is no system error, refer to the ORACLE Installation Guide for a list of requirements. Make sure all ORACLE logical names are correct, that there is enough free disk space in the ORACLE directory, and that there are enough global sections and pages. Also make sure the ORACLE budget has the required priority. If the source of the problem is not identified, refer to the appropriate installation software.
• ORA-0801: after image write process is not active
  Cause: This property is not supported.
  Action: Please refer to the corresponding installation software.
• ORA-0802: invalid context area
  Cause
  Action
• ORA-0805: opiodr: inconsistent recursion level number
  Cause:This is an internal error message that cannot occur during normal operation.
  Action: Please refer to the relevant setup software for a detailed description of the problem.
• ORA-0806: before image process is not active
  Cause
  Action
• ORA-0807: clean up process is not active
  Cause: The problem is related to an attempt to start ORACLE, which caused the preview process to be cancelled. Usually, this message is issued along with a system error message explaining the reason for the failure.
  Action: Use the system error message (if any) to find out the cause of the error. If there is no system error, refer to the ORACLE installation guide for your operating system to verify the correct installation. If the source of the problem is not identified, refer to the appropriate installation software.
• ORA-0809: opispf: invalid special function code.
  Cause:This is an internal error message that cannot occur during normal operation.
  Action: Please refer to the relevant setup software for a detailed description of the problem.
• ORA-0810: opiomc: context area not remapped at original addres
  Cause:This is an internal error message that cannot occur during normal operation.
  Action: Please refer to the relevant setup software for a detailed description of the problem.
• ORA-0811: readahead process is not active
  Cause: The problem is related to an attempt to start ORACLE, which caused the read-ahead process to be cancelled. Typically, this message is issued along with a system error message explaining the reason for the failure.
  Action: Use the system error message (if any) to find out the cause of the error. If there is no system error, refer to the ORACLE installation guide for your operating system to verify the correct installation. If the source of the problem is not identified, refer to the appropriate installation software.
• ORA-0813: assertion failure (appendix removed).
  Cause:This is an internal error message related to ORACLE SORT. This cannot happen during normal operation.
  Action: Please refer to the relevant setup software for a detailed description of the problem.
• ORA-0814: ltbdrv: illegal lock mode
  Cause:This is an internal error message that cannot occur during normal operation.
  Action: Please refer to the relevant setup software for a detailed description of the problem.
• ORA-00816: error message translation failed
  Cause:This is an internal error message that cannot occur during normal operation.
  Action: Please refer to the relevant setup software for a detailed description of the problem.
• ORA-0817: prsgkw: internal error token not found
  Cause:This is an internal error message that cannot occur during normal operation.
  Action: Please refer to the relevant setup software for a detailed description of the problem.
• ORA-0818: opispf: osf too big
  Cause:This is an internal error message that cannot occur during normal operation.
  Action: Please refer to the relevant setup software for a detailed description of the problem.

Syntax errors ORA-00900 - ORA-00999

• ORA-00900: invalid SQL statement.
  Cause: The statement you entered was not recognized as a valid SQL command.
  Action: Check for typos, make sure SQL command keywords start with one of the following words: ALTER, AUDIT, CREATE, DELETE, DROP, GRANT, INSERT, NOAUDIT, RENAME, REVOKE, SELECT, UPDATE, LOCK, VALIDATE. Other commands will cause this error.
• ORA-00901: invalid CREATE command syntax / Invalid create command.
  Cause: An invalid CREATE option was used on the CREATE command.
  Action: Check for typos, make sure the CREATE command is followed by one of the following options: INDEX, PARTITION, SPACE DEFINITION, SYNONYM, TABLE, or VIEW.
• ORA-00902: invalid data type.
  Cause: The entered data type in a CREATE or ALTER TABLE statement is not valid.
  Action: Check for typos, make sure each column name is followed by one of the following data types: DATA, CHAR, NUMBER, RAW, LONG, or LONG RAW.
• ORA-00903: invalid table name.
  Cause: The entered table or group names do not exist or are invalid. This message also appears if an invalid or nonexistent group name is specified in the ALTER/DROP CLUSTER command.
  Action: Check for typos. A valid group name must begin with a letter and contain only letters, numbers, and special characters: $, #, and _. The name must be no longer than 30 characters and must not be a reserved word.
• ORA-00904: invalid column name.
  Cause: The entered column name is missing or invalid.
  Action: Enter a valid column name. A valid name must begin with a letter and contain only letters, numbers, and special characters: $, #, and _. The name must be no longer than 30 characters and must not be a reserved word. If it contains other characters, it must be in double quotes.
• ORA-00905: missing keyword.
  Cause: Required keyword is omitted.
  Action: Check command syntax and add missing keywords.
• ORA-00906: missing left parenthesis.
  Cause: Required left parenthesis omitted. Basic commands (such as CREATE TABLE, CREATE CLUSTER, and INSERT) require a list of items enclosed in parentheses. Parentheses are also required around sequences in the WHERE clause and in the UPDATE table SET column = (SELECT ...).
  Action: Check the syntax of the command and insert the missing brackets.
• ORA-00907: missing right parenthesis
  Cause: The left parenthesis was entered without the closing right parenthesis, or the previous information was enclosed in parentheses. All parentheses must be paired.
  Action: Insert a closing right bracket.
• ORA-00908: missing NULL keyword
  Cause: In CREATE or ALTER TABLE statements, NOT was introduced to indicate that no null values ​​were allowed in this column, but the NULL keyword was omitted.
  Action: If you require a value in this column, replace the NOT keyword with NOT NULL.
• ORA-00909: invalid number of arguments
  Cause: A reference to a built-in ORACLE function was made with the wrong number of arguments. All ORACLE functions except SYSDATE require at least one argument.
  Action: Check the command syntax and enter the required number of arguments.
• ORA-00910: specified length too large for CHAR colum
  Cause: Character field size not specified or specified incorrectly. You must specify the maximum length for each column of characters. This length can take values ​​from 0: to 240: .
  Action: Enter the maximum length from 0 to 240: .
• ORA-00911: invalid character
  Cause: The special character is invalid or only valid in certain places. If a special character (other than $, # and _) is used in a name and the name is not enclosed in double quotes ("), this message will appear.
  Action: Remove the invalid character from the command.
• ORA-00912: operand pop operation with no operands on stack
  Cause: This is an internal error message that cannot occur during normal operation.
  Action: Please refer to the relevant setup software for a detailed description of the problem.
• ORA-00913: too many values
  Cause: The command assumes two identical sets of values, but the second set has more items than the first. For example: a subquery in a WHERE or HAVING clause may have too many columns, or there may be more columns in a VALUES or SELECT clause than an INSERT clause.
  Action: Check the number of items and change them.
• ORA-00914: missing ADD keyword
  Cause: The ALTER PARTITION command was entered without the ADD keyword.
  Action: check the syntax of the command and retry it.
• ORA-00915: network access of dictionary table not currently allowed
  Cause:This is an internal error message that cannot occur during normal operation.
  Action: Please refer to the relevant setup software for a detailed description of the problem.
• ORA-00917: missing comma missing comma
  Cause: A comma is missing in a list of columns, or in a list of values ​​in an INSERT statement, or in a list of the form ((c,d),(e< f),...).
  Action: Check the syntax in your SQL statement and add a missing comma if necessary.
• ORA-00918: column ambigiuously defined
  Cause: The column used for the join exists in more than one table. When joining, any column present in more than one table must contain the table name. The column name must be TABLE.COLUM or TABLE_ALIAS.COLUMN, EMP.DEPTNO or E.DEPTNO, not just EMP.
  Action: Enter the table name in the column name before the dot or alternative table name as shown above.
• ORA-00919: invalid functio
  Cause: Function-like input of type function(argument) is not recognized as an ORACLE function.
  Action: See the manual for a list of valid function names.
• ORA-00920: invalid relational operator
  Cause: Search task with invalid or missing condition statement.
  Action: Enter a valid conditional statement. The following relational statements are allowed: =, !=,<>, >, >=, <, <=,IN, IS, NULL, LIKE, и BETWEEN.
• ORA-00921: unexpected end of SQL command
  Cause: The SQL command is incomplete. Part of a valid command was entered, but at least one major component was omitted.
  Action: Check the command syntax and insert missing components.
• ORA-00922: invalid optio
  Cause: An invalid option was declared in a column definition or an area definition.
  Action: Remove the invalid option from the column or area definition. A valid option describing a column is NOT NULL to indicate that the column cannot contain NULL values. Anything else following the data type, except for a comma or a closing parenthesis, is classified as an invalid option. When describing a length for DATA or a LONG data type, you will get this error message. Only the following options are allowed in the scope definition declaration: INITIAL, INCREMENT and MAXEXTENTS, (for DATAPAGES and INDEXPAGES) and PCTFREE (for DATAPAGES only).
• ORA-00923: missing FROM keyword
  Cause: In a SELECT or REVOKE statement, the FROM keyword is either omitted, misplaced, or misspelled. The FROM keyword must follow the selected item in the SELECT statement, or the selected table name in the REVOKE statement.
  Action: Replace the word FFROM. The selected list may itself be erroneous. Make sure you use single quotes to enclose the alias, and whether or not the alias is a reserved word.
• ORA-00924: missing BY keyword
  Cause: Missing BY keyword in GROUP BY, ORDER BY, or CONNECTED BY expressions. In addition, in the GRANT command, the word INDENTIFIED must be followed by BY.
  Action: Insert the word BY correctly.
• ORA-00925: missing INTO keyword
  Cause: An INSERT command was used without the INTO keyword.
  Action: Replace INSERT with INSERT INTO.
• ORA-00926: missing VALUES keyword
  Cause: INSERT statement without the VALUES or SELECT keyword. An INSERT INTO statement must be followed by a VALUES or SELECT sequence.
  Action: Type a VALUES expression or sequence after the INSERT INTO expression.
• ORA-00927: missing equal sig
  Cause: The equal sign is missing in one of the following places: * in the SET statement of the UPDATE command * after! to indicate inequality * in the PCTFREE expression of the CREATE INDEX command
  Action: Insert an equal sign.
• ORA-00928: missing SELECT keyword
  Cause: A SELECT sequence must be included in the CREATE VIEW command.
  Action: Insert the required SELECT statement in the CREATE VIEW command.
• ORA-00929: missing period
  Cause:This is an internal error message that cannot occur during normal operation.
  Action: Please refer to the relevant setup software for a detailed description of the problem.
• ORA-00930: missing asterisk
  Cause:This is an internal error message that cannot occur during normal operation.
  Action: Please refer to the relevant setup software for a detailed description of the problem.
• ORA-00931: missing identifier
  Cause:This is an internal error message that cannot occur during normal operation.
  Action: Please refer to the relevant setup software for a detailed description of the problem.
• ORA-00932: inconsistent datatypes inconsistent data type
  Cause: Attempt to add a character field to a data field. Data can only be added to numeric fields.
  Action: Convert a character field to a numeric field using the TO_NUMBER function before adding the data field.
• ORA-00933: SQL command and not properly ended
  Cause: SQL command terminated with invalid expression. For example: an ORDER BY clause can be included in a CREATE VIEW or INSERT statement. However, an ORDER BY must not be used to create another view or be included in the main order.
  Action: Check the command syntax and remove unnecessary components. You should be able to duplicate deleted expressions with other commands. For example, if you want to order the review lines, proceed in the same way as when requesting a review, but not as when creating one. This error can also occur when using SQL*Forms if the line continuation is indented. Check offsets and remove spaces. You must use the appropriate SQL endings if you use an I expression with one argument, for example: IN(X). The IN expression must take at least two arguments.
• ORA-00934: set function not allowed here
  Cause: one of the group functions (such as AVG, SUM, MAX, MIN, COUNT) was used in a WHERE or GROUP BY clause.
  Action: Remove the group function from the WHERE or GROUP BY clauses. You can get the desired result by including the function in a query or a HAVING clause.
• ORA-00935: set function is nested too deep
  Cause:This is an internal error message that cannot occur during normal operation.
  Action: Please refer to the relevant setup software for a detailed description of the problem.
• ORA-00936: missing expressio
  Cause: Required part of expression omitted. For example, a SELECT command is entered without a list of columns, or with an incompatible type expression (SAL+). This can also happen if reserved words like SELECT TABLE are omitted.
  Action: Check the command syntax and enter the missing commands.
• ORA-00937: not a single group set functio
  Cause: You cannot include both a group function (AVG, SUM,COUNT,MIN, or MAX) and a custom column expression in a query at the same time unless the column expression is included in the GROUP BY clause.
  Action: Remove either the column expression or the group function from the query, or add a GROUP BY clause that includes a column enumeration.
• ORA-00938: not enough arguments for functio
  Cause: Too few arguments are declared in the function.
  Action: Check the syntax of the function and add any required arguments.
• ORA-00939: too many arguments for functio
  Cause: The function has too many arguments.
  Action: Check the syntax of the function and describe only the required arguments.
• ORA-00940: invalid ARTER comand invalid ALTER command
  Cause: Invalid ALTER option described. Only sections and tables can be non-repeating. A valid ALTER command must begin with one of the following options: ALTER TABLE or ALTER PARTITION.
  Action Note: Check the syntax for the correct spelling of the ALTER command.
• ORA-00941: missing cluster name
  Cause: Group name is either missing or invalid.
  Action: Describe the correct group name. A valid group name starts with a letter, contains no more than 30 characters, and consists only of letters, numbers, and the $, _, and # special characters. It must not be a reserved word. The name must be printed immediately after the CREATE CLUSTER keyword.
• ORA-00942: table or view does not exist
  Cause: This table or overview does not exist. or a view name is entered instead of a table. Existing user tables and overviews can be viewed in the TAB overview.
  Action: Check for typos and if you have entered a review name instead of a table. Enter existing names.
• ORA-00943: cluster does not exist
  Cause: The user resource is not included in the group with the described name.
  Action: Describe the correct group name immediately after the CLUSTER keyword.
• ORA-00944: insufficient number of clustered columns
  Cause: An attempt was made to create a table with fewer grouped columns than described in the CREATE CLUSTER command. The CLUSTER expression of the CREATE TABLE command must describe all the grouped columns defined when the group was created. Using the group name, you can view all group columns in the COL dictionary table.
  Action: Describe all column names in the CREATE TABLE command.
• ORA-00945: specified clustered column does not exist
  Cause: The column described in the expression of the CREATE TABLE statement is not a column in this table.
  Action: Repeat using the table column name.
• ORA-00946: missing TO keyword
  Cause: The GRANT command was entered without the TO keyword, or an invalid form of this command was used.
  Action: Check the syntax of the two main forms of the GRANT command (granting database access and granting privileges). Insert the TO keyword correctly in the GRANT command.
• ORA-00947: not enough values
  Cause: The SQL statement requires two identical sets of values, and the second set contains fewer values. This can also happen if a nested SELECT finds fewer columns in a WHERE or HAVING expression, as in: WHERE(A,B) IN (SELECT C FROM..) An error can also occur in an INSERT statement in which the expression VALUES or SELECT does not contain enough values ​​for INSERT, as in: INSERT INTO table (A,B) VALUES("C").
  Action: Check the strength of each set and make them equal in number.
• ORA-00948: ALTER CLUSTER statement no longer supported
  Cause: ALTER CLUSTER statement removed
  Action: To add data from a table, use the following set of SQL statements: CREATE TABLE<новая_таблица>SELECT*FROM<старая_таблица>CLUSTER<имя_группы>DROP<старая_таблица>and RENAME TABLE<новая_таблица> <старая_таблица>.
• ORA-00949: illegal reference to external database
  Cause:This is an internal error message that cannot occur during normal operation.
  Action: Please refer to the relevant setup software for a detailed description of the problem.
• ORA-00950: invalid drop optio invalid DROP option
  Cause: After the DROP command, there was no DROP option such as TABLE, VIEW, SYNONYM, CLUSTER, or SPACE.
  Action: Check the command syntax and use the correct format for the DROP option.
• ORA-00951: cluster not empty
  Cause: The DROP command describes a non-empty group. A group can only be deleted if it contains no tables. Remove a table from a group using the DROP TABLE command.
  Action: Remove all tables from the group and then use the DROP CLUSTER command.
• ORA-00952: missing GROUP keyword
  Cause: The group is incorrectly implemented.
  Action: No action is required from the user.
• ORA-00953: missing index name
  Cause: In the CREATE, DROP, VALIDATE INDEX commands, the index name is incorrect or missing.
  Action: Type the correct index name after the INDEX keyword. If you wish to delete or correct an index, check the name by looking at the INDEXES overview. If you need to create a new index, check the syntax first.
• ORA-00954: missing INDENTIFIED keyword
  Cause: The GRANT CONNECTION command was issued without the INDENTIFIED keyword.
  Action: Insert the INDENTIFIED keyword after the username. The command format is as follows: GRANT CONNECTION TO<список пользователей>INDETIFIED BY<пароль списка>.
• ORA-00955: name is already used by existing object
  Cause: An attempt was made to create a table, view, group, or synonym that already exists. Each user table name must be distinct from other table names, views, groups, or synonyms owned by other users.
  Action: Either enter a unique table, view, group, or synonym name, or modify or delete an existing one.
• ORA-00956: invalid auditing optio - invalid auditing option
  Cause: There was an invalid check option.
  Action: Check the syntax of the command and try again with the correct option.
• ORA-00957: duplicate column name
  Cause: The column name was declared twice in a CREATE or INSERT statement. The name of a column in a table, overview, or group must be unique.
  Action: Replace the column name in the CREATE command with a new unique one. In the INSERT command, remove the duplicate names.
• ORA-00958: missing CHECK keyword
  Cause: Missing CHECK immediately after the WHITH keyword in the WITH CHECK OPTION clause of the CREATE VIEW statement.
  Action: Check the syntax of the SQL statement.
• ORA-00959: space definition name does not exist
  Cause: The A DROP SPACE command described a non-existent area name.
  Action: Use existing scope definition names. To view existing names, select SNAME from SPACES.
• ORA-00960: invalid INITIAL value
  Cause: An invalid start datapage or indexpage number value was specified in the CREATE SPACE command. This value must be at least 3.
  Action: Enter an INITIAL value of at least 3.
• ORA-00961: invalid INCREMENT value
  Cause: An invalid step count value was described for datapage or indexpage. The step value must be greater than 3.
  Action: Enter an INCREMENT value greater than 3.
• ORA-00962: invalid PCTFREE value
  Cause: Incorrect free space percentage value was described in the SPACE definition. This value must be in the range from 1: to 99.
  Action: Enter a PCTFREE value between 1: and 99.
• ORA-00963: invalid SIZE value
  Cause: An invalid logical block size value was specified in the CREATE CLUSTER command. The logical block size is used to efficiently store small groups of data.
  Action: Describe the allowed value of the logical block size (greater than 0).
• ORA-00964: invalid space defenition name
  Cause: An invalid realm name was specified in the CREATE/DROP SPACE command or in CREATE TABLE, or a nonexistent realm name was specified in the CREATE TABLE command.
  Action: Enter a valid area name. A valid name starts with a letter, contains no more than 30 characters, and consists only of letters, numbers, and the $, _, and # special characters. It must not be a reserved word. If your name is correct, you may have inadvertently removed the TEMP scope definition.
• ORA-00965: space definition name already exists
  Cause: An existing realm name was used in the CREATE SPACE command. Realm definition names must be unique.
  Action: Enter a unique name for the area.
• ORA-00966: missing TABLE keyword
  Cause: A LOCK command was used and the TABLE keyword was misspelled, or omitted, or misplaced. The LOCK command must begin like this: LOCK TABLE<имя таблицы> ... .
  Action: Insert the TABLE keyword in the appropriate place.
• ORA-00968: missing INDEX keyword
  Cause: The CREATE UNIQUE command was used and the INDEX keyword was misspelled, or omitted, or misplaced.
  Action: Please check the syntax and try again.
• ORA-00969: missing ON keyword
  Cause: A GRANT or CREATE INDEX command was used and the ON keyword was misspelled, or omitted, or misplaced.
  Action: Insert the ON keyword in the appropriate place.
• ORA-00970: missing WITH keyword - missing WITH keyword
  Cause: The START keyword was used without WITH. Both keywords are required in the START WITH clause for the query.
  Action: Replace the word START with START WITH.
• ORA-00971: missing SET keyword - missing SET keyword
  Cause: In the UPDATE command, the SET keyword was misspelled, or omitted, or misplaced.
  Action: Insert the keyword SET after the name of the table to change.
• ORA-00972: indentifier is too big
  Cause: The length of the database object name is more than 30 characters.(Database objects are tables, groups, views, indexes, synonyms, scopes, and usernames.
  Action: Shorten the name to a maximum of 30 characters.
• ORA-00973: invalid row count estimate
  Cause: The row count value described in the CREATE INDEX command is a number less than 0.
  Action: Describe a valid value (greater than 0).
• ORA-00974: invalid index block loading factor (percentage)
  Cause: Percentage of the used volume, described in the CREATE INDEX command, the area does not belong to the interval from 1: to 90%.
  Action: Enter a PCTFREE value between 1 and 90. The default is 10.
• ORA-00975: Date + date not allowed
  Cause: Attempt to add two data fields together. Data can only be added to number fields, not to other data.
  Action: Add a data field to a number field.
• ORA-00977: duplicate auditing optio - double auditing option
  Cause: The same control option has been described again.
  Action: Remove redundant control description.
• ORA-00978: nested set function with out GROUP BY
  Cause: A group function (such as MIN, MAX, or AVG) was used inside another group function (such as MAX(COUNT(*))) without a corresponding GROUP BY clause.
  Action: Either add a GROUP BY clause or remove the outer nesting level.
• ORA-00979: not a GROUP BY expressio expression does not belong to GROUP BY
  Cause: The GROUP BY clause does not contain all the expressions of the SELECT clause. Expressions from SELECT not included in group functions (COUNT, SUM, AVG, MAX, MIN) must be listed in the GROUP BY clause.
  Action: Include all SELECT expressions that are not arguments to group functions in the GROUP BY clause.
• ORA-00980: synonym translation no longer valid
  Cause: The synonym you used was for a table, view, or synonym that has been deleted or renamed.
  Action: Replace the synonym with the name of the table, view, synonym for which it was intended. Or, regenerate the synonym for the correct tables, views, or synonyms.
• ORA-00981: cannot mix table and system-wide auditing options
  Cause: Simultaneously the width option of both the table and the system are described in the same AUDIT statement.
  Action: Correct the operator.
• ORA-00982: missing plus sign
  Cause: When appended, there is no plus sign (+) after the left parenthesis. When appending, the left (open) parenthesis is interpreted as describing an appendage, and a plus sign is expected. To describe an attachment to a column, the column description must be followed by a plus sign enclosed in brackets (+).
  Action: Correct the SQL syntax.
• ORA-00984: column not allowed here
  Cause: The column name has been used where it is not allowed, such as in the VALUES clause of an INSERT statement.
  Action: Check the command syntax and use column names only where allowed.
• ORA-00985: invalid program name
  Cause
  Action
• ORA-00986: missing or invalid group name(s) - missing or invalid group name(s)
  Cause: This property is not implemented.
  Action: No user action required.
• ORA-00987: missing or invalid user name(s)
  Cause: The username was not specified in the GRANT command, or one of the specified names is incorrect. Valid usernames must appear after the word TO in the GRANT command. A valid username starts with a letter, is limited to 30 characters, and consists only of letters, numbers, and the $, _, and # special characters. It must not be a reserved word.
  Action: Describe a valid username (or list of users) after the TO keyword in the GRANT command.
• ORA-00988: missing or invalid password(s)
  Cause: There are more usernames than passwords in the GRANT command. A valid password must be specified for each user listed in the GRANT command.
  Action: Enter the correct password for each user.
• ORA-00989: too many passwords for user names given
  Cause: There are more passwords than were described by usernames in the GRANT command. Only one password must be entered for each user listed in the GRANT command.
  Action: Enter the same number of users and passwords.
• ORA-00990: missing or invalid privilege
  Cause: No privileges were declared in the command for GRANT privileges, or one of them is invalid.
  Action: Enter one or more valid privileges. The following privileges are allowed: SELECT, INSERT, DELETE, UPDATE, ALTER, INDEX, DROP, CLUSTER, and ALL. More than one privilege can be granted by entering them in a list separated by commas (,) or by describing the word ALL to grant all privileges.
• ORA-00991: unrecognizable format for GRANT command
  Cause: An invalid form of the GRANT command was entered.
  Action: Check command syntax. There are two types of GRANT command. The first type is used to define user access to the database and should have the following format: GRANT CONNECT/RESOURCE/DBA TO<имя пользователя>INDENTIFIED BY<пароль>. At least one of the CONNECT, RESOURCE, or DBA keywords must be present. The second type is used to grant privileges to database objects and has the format: GRANT<список привелегий>ON<обзор/ таблица>TO< индентификатор пользователя>/PUBLIC.
• ORA-00992: unrecognizable format for REVOKE command
  Cause: An invalid form of the REVOKE command was entered.
  Action: Check the syntax of the command and retry it.
• ORA-00993: missing GRANT keyword
  Cause: The WITH option was declared at the end of a GRANT command without a GRANT option. To grant privileges to a user and permission to grant them to other users, use the WITH GRANT OPTIO option at the end of a GRANT command.
  Action: Replace the WITH keyword with WITH GRANT OPTION.
• ORA-00994: missing OPTION keyword - OPTIO keyword missing
  Cause: The WITH GRANT option was used at the end of the GRANT command without the word OPTION.
  Action: Change the key from WITH GRANT to WITH GRANT OPTION.
• ORA-00995: missing or invalid synonym indentifier
  Cause: In a CREATE or DROP SYNONYM command, a synonym name was either omitted or invalid.
  Action: Check for typos and command syntax. A valid synonym name must immediately follow the SYNONYM key in both commands. A valid synonym name starts with a letter, is limited to 30 characters, and consists only of letters, numbers, and the $, _, and # special characters. It must not be a reserved word.
• ORA-00996: the concatenate operator is ¦¦ not ¦ - the concatenate operator is ||, not |
  Cause: The single stroke (|) was understood as an attempt to describe concatenation, but the concatenation operator consists of two strokes (||).
  Action: Enter a double stroke for the concatenation operation, or remove a single stroke if the concatenation is not needed.
• ORA-00997: illegal use of long datatype
  Cause: You have used the LONG data type, which is used to store data larger than 240 characters, in a function or in WHERE, GROUP BY, ORDER BY clauses. The LONG value can only be used in a SELECT clause.
  Action: Remove this field from the function or offer.
• ORA-00998: must name this expression with a column label
  Cause: A function or expression was used in the CREATE VIEW command, but the corresponding column name was not declared. When an expression or function is used for a view, all column and view names must be correctly listed in the CREATE VIEW command.
  Action: Enter all the review column names in parentheses after the review name.
• ORA-00999: invalid view name.
  Cause: Missing or invalid view name in CREATE VIEW command.
  Action: Enter a valid view name immediately after CREATE VIEW. A valid browse name starts with a letter, is limited to 30 characters, and consists only of letters, numbers, and the $,_, and # special characters. It must not be a reserved word.