This chapter uses a series of examples to explain some of the Oracle errors that you might encounter when designing an Oracle database and prototyping an application. By no means is this list exhaustive. Determining which error in a SQL statement or PL/SQL subprogram causes a particular Oracle error can sometimes be difficult. The Oracle errors are listed in ascending order according to the Oracle error code number.
If one Oracle user has locked a row with a SELECT FOR UPDATE and another Oracle user attempts to lock the same row with a SELECT FOR UPDATE with the NOWAIT clause, Oracle returns the ORA-00054 error message to the second user.
If Oracle cannot identify the keyword following CREATE, it returns the ORA-00901 error message. Here is an example:
SQL> create tablespce abc
2 datafile 'C:\ORAWIN\DBS\wdbabc.ora'
3 size 10m;
create tablespce abc
*
ERROR at line 1:
ORA-00901: invalid CREATE command
Oracle returns this error when an invalid datatype is referenced in a SQL statement. For example, the following SQL statement attempts to create a column with an illegal datatype:
SQL> create table XYZ (
2 record_no number(4) primary key,
3 time_of_day time);
time_of_day time)
*
ERROR at line 3:
ORA-00902: invalid datatype
Oracle returns an error when a table name does not satisfy Oracle object-naming requirements. For instance, the first letter of a table name must be a letter. Please refer to Chapter 8, "Creating and Modifying Tables," for details on Oracle's object naming rules.
SQL> create table 21_day_orders (
2 order_no number(4),
3 cust_no number(4),
4 amount number(7,2));
create table 21_day_orders (
*
ERROR at line 1:
ORA-00903: invalid table name
Oracle returns an error when a column name does not satisfy Oracle object-naming requirements. For instance, the first letter of a column name must be a letter.
SQL> create table analgesic ( 2 123_compound varchar2(30), 3 active_ingredient varchar2(60)); 123_compound varchar2(30), * ERROR at line 2: ORA-00904: invalid column name
Oracle also returns this error if the specified column is not part of the table.
SQL> select Product_IDD from Product;
select Product_IDD from Product
*
ERROR at line 1:
ORA-00904: invalid column name
If Oracle doesn't detect the left parenthesis that it expects in a SQL statement, it returns an error message.
SQL> insert into Product 2 (Product_ID, Manufacturer_ID) 3 values 4 'ABC999', 'MMM000'); 'ABC999', 'MMM000') * ERROR at line 4: ORA-00906: missing left parenthesis
Sometimes, Oracle returns a different error message for a missing left parenthesis. If the column list is very long, spotting the real error can be difficult. The following example is missing a left parenthesis before Product_ID, but Oracle returns the ORA-00926 message instead:
SQL> insert into Product
2 Product_ID, Manufacturer_ID)
3 values
4 ('ABC999', 'MMM000');
Product_ID, Manufacturer_ID)
*
ERROR at line 2:
ORA-00926: missing VALUES keyword
If Oracle doesn't detect the right parenthesis that it expects in a SQL statement, it returns an error message.
SQL> select to_char(sysdate,'MM-DD-YY' from dual;
select to_char(sysdate,'MM-DD-YY' from dual
*
ERROR at line 1:
ORA-00907: missing right parenthesis
In some situations you expect Oracle to report the ORA-00907 error, but it reports a different error message instead.
SQL> insert into Product
2 (Product_ID, Manufacturer_ID)
3 values
4 ('ABC123','MMM999';
('ABC123','MMM999'
*
ERROR at line 4:
ORA-00917: missing comma
If the length of a particular datatype exceeds its allowed maximum length, Oracle returns an error. Here is an example:
SQL> create table Never_Created
2 (Lots_of_Text varchar2(2001));
(Lots_of_Text varchar2(2001))
*
ERROR at line 2:
ORA-00910: specified length too long for its datatype
The ORA-00911 error occurs when Oracle encounters what it considers to be an invalid character. Often, the real problem is a missing character. In the following example, a missing single quote causes the error.
SQL> select segment_name
2 from dba_extents
3 where
4 segment_name like %TTT%';
segment_name like %TTT%'
*
ERROR at line 4:
ORA-00911: invalid character
The ORA-00913 error can occur when the number of columns specified in an INSERT statement is less than the number of column values, as shown in this example:
SQL> insert into Product
2 (Product_ID, Manufacturer_ID, Date_of_Manufacture)
3 values
4 ('A2003', 'SEN101', '21-JUN-91', 100.2);
values
*
ERROR at line 3:
ORA-00913: too many values
If Oracle is expecting a comma in a SQL statement that doesn't have one, you get the following error message:
SQL> insert into Product
2 (Product_ID Manufacturer_ID)
3 values
4 ('BBB222', 'MNM123');
(Product_ID Manufacturer_ID)
*
ERROR at line 2:
ORA-00917: missing comma
Sometimes, Oracle could return a more appropriate error message, but instead it returns an ORA-00917. For the following query, a more accurate error statement would be ORA-00907 to indicate that a right parenthesis is missing.
SQL> insert into Product
2 (Product_ID, Manufacturer_ID)
3 values
4 ('ABC123','MMM999';
('ABC123','MMM999'
*
ERROR at line 4:
ORA-00917: missing comma
The ORA-00918 error occurs when a SQL statement references two or more tables. If a column that exists in more than one of the specified tables is referenced in the SQL statement without qualifying the column with its table, the column is said to be ambiguous.
SQL> select Employee_ID, Hire_Date, Relationship
2 from Employee, Employee_Dependent
3 where
4 Employee.Employee_ID = Employee_Dependent.Employee_ID;
select Employee_ID, Hire_Date, Relationship
*
ERROR at line 1:
ORA-00918: column ambiguously defined
To correct the error, qualify the column name with the assumed table name.
SQL> select Employee.Employee_ID, Hire_Date, Relationship
2 from Employee, Employee_Dependent
3 where
4 Employee.Employee_ID = Employee_Dependent.Employee_ID;
EMPLOYEE_ID HIRE_DATE RELATIONSHIP
----------- --------- ------------------------------
1001 10-APR-82 SPOUSE
1001 10-APR-82 CHILD
1007 22-SEP-92 SPOUSE
1007 22-SEP-92 CHILD
1007 22-SEP-92 CHILD
If Oracle cannot identify a relational operator in a SQL statement, it issues the following message:
SQL> select segment_name
2 from dba_extents
3 where
4 segment_name lke '%TTT%';
segment_name lke '%TTT%'
*
ERROR at line 4:
ORA-00920: invalid relational operator
As you'd expect, Oracle returns the ORA-00921 error when you submit an incomplete SQL statement.
SQL> select Patient_ID, Body_Temp_Deg_F
2 from Patient
3 where
4 Body_Temp_Deg_F >;
Body_Temp_Deg_F >
*
ERROR at line 4:
ORA-00921: unexpected end of SQL command
However, Oracle won't always return the ORA-00921 error for an incomplete SQL statement; it depends on where the statement terminates. For example, the following query is rejected with a different error message:
SQL> select Patient_ID, Body_Temp_Deg_F
2 from Patient
3 where
4 Body_Temp_Deg_F ;
Body_Temp_Deg_F
*
ERROR at line 4:
ORA-00920: invalid relational operator
Oracle returns the ORA-00932 error if you apply an operator on a column whose datatype cannot be used with the operator. For example, the following query tries to apply the LIKE operator against a LONG column. What you get is a somewhat misleading error message--;you might expect that Oracle would return ORA-00997: illegal use of LONG datatype.
SQL> create table table_with_long ( 2 record_no number(4), 3 description long); Table created. SQL> select record_no 2 from table_with_long 3 where 4 description like '%ABC%'; description like '%ABC%' * ERROR at line 4: ORA-00932: inconsistent datatypes
If a query references a group function in the WHERE or GROUP BY clause, Oracle returns the following error message:
SQL> select Product_ID
2 from Portland_Product
3 where
4 Initial_Retail_Value > Avg(Initial_Retail_Value);
Initial_Retail_Value > Avg(Initial_Retail_Value)
*
ERROR at line 4:
ORA-00934: group function is not allowed here
To compare a column value with a group function, you can use a group function in a subquery as shown:
SQL> select Product_ID
2 from Portland_Product
3 where
4 Initial_Retail_Value > (select Avg(Initial_Retail_Value)
from Portland_Product);
PRODUCT_ID
------------
C3002
The ORA-00936 error occurs when Oracle expects to see an expression. In this query a comma follows the description column, but no more columns or expressions are in the select list.
SQL> select product_id, manufacturer_id, description, 2 from product; from product * ERROR at line 2: ORA-00936: missing expression
In a way, the ORA-00936 error is sometimes the opposite of ORA-00917: missing comma --;too many commas.
A query's select list cannot contain a column and a group function unless the column is referenced in the GROUP BY clause. The following query illustrates the problem:
SQL> select Product_ID, Avg(Current_Used_Value)
2 from Product;
select Product_ID, Avg(Current_Used_Value)
*
ERROR at line 1:
ORA-00937: not a single-group group function
If you include the column in the GROUP BY clause, Oracle processes the query.
SQL> select Product_ID, Avg(Current_Used_Value) 2 from Product 3 group by Product_ID; PRODUCT_ID AVG(CURRENT_USED_VALUE) ------------ ----------------------- A903 B311 B384 B801 C2002 110.4 D301 TR901 7 rows selected.
Oracle returns the ORA-00938 error when a SQL statement calls a function with an insufficient number of arguments. For instance, the DECODE function requires at least three arguments.
SQL> select decode('ABC')
2 from user_tables;
select decode('ABC')
*
ERROR at line 1:
ORA-00938: not enough arguments for function
The ORA-00942 error usually occurs because the table or view has been misspelled. Oracle also returns this error if the table or view exists but the user has no object privileges for the table or view.
The ORA-00947 error can occur when the number of columns specified in an INSERT statement is greater than the number of column values, as shown in the following example:
SQL> insert into Product
2 (Product_ID, Manufacturer_ID, Date_of_Manufacture)
3 values
4 ('A2003', 'SEN101');
values
*
ERROR at line 3:
ORA-00947: not enough values
Oracle returns the ORA-00979 error if a column in a query's select list is contained in a GROUP BY clause and another column in the select list is not.
SQL> select Product_ID, Current_Used_Value
2 from Product
3 Group by Product_ID;
select Product_ID, Current_Used_Value
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
Once all columns in the select list also appear in the GROUP BY clause, Oracle processes the query.
SQL> select Product_ID, Current_Used_Value 2 from Product 3 Group by Product_ID, Current_Used_Value; PRODUCT_ID CURRENT_USED_VALUE ------------ ------------------ A903 B311 B384 B801 C2002 110.4 D301 TR901 7 rows selected.
Certain operations cannot be performed on a column whose datatype is LONG. One example is the use of a subquery with the INSERT statement in which a column in the select list is a LONG column.
SQL> desc Table_With_Long
Name Null? Type
------------------------------- -------- ----
RECORD_NO NUMBER(4)
DESCRIPTION LONG
SQL> desc Another_Table_With_Long
Name Null? Type
------------------------------- -------- ----
REC_NO NUMBER(4)
COMMENTS LONG
SQL> insert into Another_Table_With_Long
2 (Rec_No, Comments)
3 select Record_No, Description
4 from Table_With_Long;
select Record_No, Description
*
ERROR at line 3:
ORA-00997: illegal use of LONG datatype
The ORA-01031 error occurs when a user has been granted at least one object privilege associated with a table or view but has not been granted the privilege specified in the SQL statement. To illustrate, Oracle user FRAYED_WIRES grants the select privilege on the Security_Price table to Oracle user HGOMEZ. When HGOMEZ tries to update the Security_Price table, Oracle returns the error.
SQL> grant select on security_price to hgomez;
Grant succeeded.
SQL> connect hgomez/hgomez
Connected.
SQL> select * from frayed_wires.security_price;
COMPANY SYMBO LAST_QTR_EPS
-------------------- ----- ------------
ABC -.58
ACME 1.81
ZGEGE 2.81
SQL> update frayed_wires.security_price
2 set
3 last_qtr_eps = 2.22
4 where
5 symbol = 'ABC';
update frayed_wires.security_price
*
ERROR at line 1:
ORA-01031: insufficient privileges
Once the select privilege on the Security_Price table has been revoked from HGOMEZ, Oracle returns an ORA-00942, which prevents HGOMEZ from determining whether the table exists or whether he has no object privileges for a table that does exist.
SQL> revoke select on security_price from hgomez;
Revoke succeeded.
SQL> connect hgomez/hgomez
Connected.
SQL> select * from frayed_wires.security_price;
select * from frayed_wires.security_price
*
ERROR at line 1:
ORA-00942: table or view does not exist
You get the ORA-01400 error when inserting a row if you don't supply a value for a mandatory column.
SQL> insert into Vendor
2 (Company_Name, Street_Address)
3 values
4 ('ACME CO.','123 MAIN ST.');
insert into Vendor
*
ERROR at line 1:
ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert
If you try to assign a character string--;whether it is a constant or column expression--;and its length exceeds the targeted column, Oracle returns an error message.
SQL> insert into Portland_Product
2 (Product_ID, Manufacturer_ID, Replacement_Product)
3 values
4 ('ABC100','MMM233','Preamplifier manufactured by Bovine Products, 1993');
insert into Portland_Product
*
ERROR at line 1:
ORA-01401: inserted value too large for column
The ORA-01403 message really isn't an error at all. Instead, you can consider it an informative message that is equivalent to the predefined PL/SQL exception NO_MORE_DATA.
The ORA-01407 error occurs if an UPDATE statement tries to set the value of a mandatory column to NULL.
SQL> update Product
2 set
3 Product_ID = NULL
4 where
5 Current_Used_Value is NULL;
update Product
*
ERROR at line 1:
ORA-01407: cannot update mandatory (NOT NULL) column to NULL
If you try to create an index on a table that already has an index based on the same list of columns, Oracle returns an error. Here is an example:
SQL> create index Portfolio_SS_SIC_Code
2 on Portfolio (Stock_Symbol, SIC_Code);
Index created.
SQL> create index Portfolio_Another_Index
2 on Portfolio (Stock_Symbol, SIC_Code);
on Portfolio (Stock_Symbol, SIC_Code)
*
ERROR at line 2:
ORA-01408: such column list already indexed
However, Oracle creates another index if an additional column is added to the index's column list, as shown:
SQL> create index Portfolio_Different_Index 2 on Portfolio (Stock_Symbol, SIC_Code, Price); Index created.
If you don't specify a ROWID in the proper format, Oracle returns the ORA-01410 error message. Here is an example:
SQL> select Product_ID 2 from Product 3 where rowid = '0.00010C3.0004.0001'; ERROR: ORA-01410: invalid ROWID
If you specify the correct format for a ROWID but no row has the specified ROWID, Oracle returns the same error message.
SQL> select Product_ID 2 from Product 3 where rowid = '000010C3.0004.0002'; ERROR: ORA-01410: invalid ROWID no rows selected SQL> select Product_ID 2 from Product 3 where rowid = '000010C3.0004.0001'; PRODUCT_ID ------------ A903
The ORA-01449 error occurs when you try to alter a table to make a column mandatory but at least one row in the table has a null value for that column.
SQL> alter table Product 2 modify 3 (Replacement_Product varchar2(30) not null); (Replacement_Product varchar2(30) not null) * ERROR at line 3: ORA-01449: column contains NULL values; cannot alter to NOT NULL
Once each row has a value for Replacement_Product, the column can be altered to be mandatory.
SQL> update Product 2 set Replacement_Product = 'X1000'; 7 rows updated. SQL> alter table Product 2 modify 3 (Replacement_Product varchar2(30) not null); Table altered.
Oracle returns the ORA-01452 error if you attempt to create a unique index on a set of columns whose values aren't unique.
SQL> select * from Dup_Patient; PATIEN BODY_TEMP_DEG_F I ------ --------------- - R4321 104 Y GG9999 107.6 AB1234 116 AB1234 102.2 SQL> create unique index Dup_Patient_PK 2 on Dup_Patient (Patient_ID); on Dup_Patient (Patient_ID) * ERROR at line 2: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
The ORA-01453 error occurs when you issue the SET TRANSACTION statement after some other SQL statement, for instance:
SQL> rollback; Rollback complete. SQL> select sysdate from dual; SYSDATE --------- 30-JUN-95 SQL> set transaction read only; set transaction read only * ERROR at line 1: ORA-01453: SET TRANSACTION must be first statement of transaction
If you issue the SET TRANSACTION statement directly after a COMMIT statement, ROLLBACK statement, or new Oracle session, it will be processed successfully.
SQL> rollback; Rollback complete. SQL> set transaction read only; Transaction set.
Oracle returns the ORA-01481 error when a number format model contains undefined characters. For instance, the following query uses the TO_CHAR function to convert Initial_Retail_Value to a character string. However, the number format model accidentally contains an F.
SQL> select to_char(Initial_Retail_Value, '$9F9.99') 2 from Product; ERROR: ORA-01481: invalid number format model
Oracle issues the ORA-01722 error when a numeric value is illegal--;whether it's a literal or a column value. Here is an example:
SQL> select * from Inventory;
MODEL_NO RETAIL_PRICE OLD_RETAIL
---------- ------------ ----------
SOFABED 350.00
COUCH 740
END TABLE 2A2.00
SQL> update Inventory
2 set
3 Retail_Price = to_number(Old_Retail_Price);
Retail_Price = to_number(Old_Retail_Price)
*
ERROR at line 3:
ORA-01722: invalid number
Once you correct the bad value, Oracle successfully processes the UPDATE statement.
SQL> update Inventory 2 set 3 Old_Retail_Price = '252.00' 4 where 5 Old_Retail_Price = '2A2.00'; 1 row updated. SQL> update Inventory 2 set 3 Retail_Price = to_number(Old_Retail_Price); 3 rows updated.
The ORA-04091 error typically occurs when a database trigger issues a query against a table that was modified within the transaction. For example, trigger tab1_Update_Before is fired when an UPDATE is executed against tab1; in the trigger body, a row is inserted into table tab2. The INSERT causes trigger tab2_Insert_Before to fire; within its trigger body, an UPDATE statement contains a subquery that selects from tab1. However, tab1 is a mutating table, and the UPDATE statement fails with error ORA-04091.
SQL> create or replace trigger tab1_Update_Before before 2 update on tab1 3 for each row 4 4 declare 5 5 begin 6 6 insert into tab2 7 (col2) 8 values 9 (:old.col1); 10 10 end; 11 / Trigger created. SQL> SQL> create or replace trigger tab2_Insert_Before before 2 insert on tab2 3 for each row 4 4 declare 5 5 begin 6 6 update tab2 7 set 8 col2 = 27 9 where 10 col2 in (select col1 from tab1); 11 11 end; 12 / Trigger created. SQL> update tab1 2 set 3 col1 = 22; update tab1 * ERROR at line 1: ORA-04091: table FRAYED_WIRES.TAB1 is mutating, trigger/function may not see it ORA-06512: at line 3 ORA-04088: error during execution of trigger 'FRAYED_WIRES.TAB2_INSERT_BEFORE' ORA-06512: at line 3 ORA-04088: error during execution of trigger 'FRAYED_WIRES.TAB1_UPDATE_BEFORE'
Oracle issues the ORA-04092 message if a trigger tries to execute a COMMIT statement or ROLLBACK statement. Notice that Oracle issues the error message when the trigger fires, not when it is created.
SQL> create or replace trigger Shipment_Ins_Before before
2 insert on Shipment
3 for each row
4 declare
5
5 begin
6
6 :new.Manual_Check := 'Y';
7
7 commit;
8
8 end;
9 /
Trigger created.
SQL> insert into Shipment
2 (Shipment_ID)
3 values
4 ('ABCD1000');
insert into Shipment
*
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at line 2
ORA-04088: error during execution of trigger 'FRAYED_WIRES.SHIPMENT_INS_BEFORE'
Oracle also returns the ORA-04092 error if a trigger calls a stored procedure, function, or package subprogram in which a COMMIT statement or ROLLBACK statement is issued.
You cannot reference a LONG column in a database trigger. This restriction is just one of several restrictions regarding the use of LONG columns in SQL. Please refer to Chapter 10, "The Oracle Datatypes," for more details on the use of the LONG datatype.
SQL> create or replace trigger Table_With_Long_Ins_Before before
2 insert on Table_With_Long
3 for each row
4
4 declare
5
5 begin
6
6 :new.Description := 'This is a string literal assigned to a LONG column.';
7
7 end;
8 /
create or replace trigger Table_With_Long_Ins_Before before
*
ERROR at line 1:
ORA-04093: references to columns of type LONG are not allowed in triggers
You will see this message when you attempt to start the Personal Oracle7 database on a drive that doesn't have enough free disk space for Oracle to create a temporary file. To eliminate this message, make sure that the drive on which the Personal Oracle7 directory resides has at least 10MB of free space.