ORA-00054: resource busy and acquire with NOWAIT specified

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.

ORA-00901: invalid CREATE command

 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

ORA-00902: invalid 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

ORA-00903: invalid table name

 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

 ORA-00904: invalid column 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

ORA-00906: missing left parenthesis

 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

 ORA-00907: missing right parenthesis

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

ORA-00910: specified length too long for its datatype

If the length of a particular datatype exceeds its allowed maximum length, Oracle returns an error

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

ORA-00911: invalid character

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

ORA-00913: too many values

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);

*

ERROR at line 3:

ORA-00913: too many values

ORA-00917: missing comma

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

ORA-00918: column ambiguously defined

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 employee.Employee_ID = Employee_Dependent.Employee_ID;

select Employee_ID, Hire_Date, Relationship

ERROR at line 1:

ORA-00918: column ambiguously defined

ORA-00920: invalid relational operator

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

ORA-00921: unexpected end of SQL command

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

ORA-00932: inconsistent datatypes

Oracle returns the ORA-00932 error if you apply an operator on a column whose data type cannot be used with the operator.

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

ORA-00934: group function is not allowed here

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

ORA-00936: missing expression

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

ORA-00937: not a single-group group function

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

ORA-00938: not enough arguments for function

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

ORA-00942: table or view does not exist

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.

ORA-00979: not a GROUP BY expression

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

ORA-00997: illegal use of LONG datatype

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.

ORA-01031: insufficient privileges

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.

 ORA-01400: primary key or mandatory

(NOT NULL) column is missing or NULL during insert. 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

ORA-01401: inserted value too large for column

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 , 1993');

insert into Portland_Product

*

ERROR at line 1:

ORA-01401: inserted value too large for column

ORA-01403: no data found

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.

ORA-01408: such column list already indexed

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_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

ORA-01410: invalid ROWID

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

ORA-01449: column contains NULL values

Cannot alter to NOT NULL. 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

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> 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

ORA-01453: SET TRANSACTION

The ORA-01453 error occurs when you issue the SET TRANSACTION statement after some other SQL statement, for instance:

SQL> rollback;

Rollback complete.

ORA-01453: SET TRANSACTION must be first statement of transaction

ORA-01481: invalid number format model

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.

SQL> select to_char(Initial_Retail_Value, '$9F9.99')

2 from Product;

ERROR:

ORA-01481: invalid number format model

ORA-09242: unable to startup Oracle