Oracle: ORA-00995 Error Message

ORA-00995 Error Message

Learn the cause and how to resolve the ORA-00995 error message in Oracle.

Description

When you encounter an ORA-00995 error, the following error message will appear:

• ORA-00995: missing or invalid synonym identifier

Cause

You tried to CREATE or DROP a synonym, but the name of the synonym that you used was either missing or invalid.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Correct the name of the synonym.

Synonym names must meet the following criteria:

• Must begin with a letter.
• Must consist of alphanumeric characters or the following special characters: $, _, and #.
• Can not be reserved words.
• Must be no more than 30 characters in length.

Oracle: ORA-00985 Error Message

ORA-00985 Error Message

Learn the cause and how to resolve the ORA-00985 error message in Oracle.

Description

When you encounter an ORA-00985 error, the following error message will appear:

• ORA-00985: invalid program name

Cause

You tried to execute an operation that probably contained a syntax error.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

You need to correct the syntax error and re-execute the operation.

Oracle: ORA-00984 Error Message

ORA-00984 Error Message

Learn the cause and how to resolve the ORA-00984 error message in Oracle.

Description

When you encounter an ORA-00984 error, the following error message will appear:

• ORA-00984: column not allowed here

Cause

You tried to execute a SQL statement that included a column name where it was not permitted.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

This error most commonly occurs when you try to include a column name in the VALUES clause of a INSERT statement.

For example, if you had tried to use the column named customers in an INSERT statement as follows:

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(1, customer_name);

You would receive the following error message:

You could correct the INSERT statement by including a character value, instead of the column name as follows:

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(1, 'IBM');

Or if you needed to include a column name, you could rewrite the INSERT statement with a sub-select as follows:

INSERT INTO supplier
(supplier_id, supplier_name)
SELECT account_no, customer_name
FROM customers
WHERE city = 'Newark';

Oracle: ORA-00980 Error Message

ORA-00980 Error Message

Learn the cause and how to resolve the ORA-00980 error message in Oracle.

Description

When you encounter an ORA-00980 error, the following error message will appear:

• ORA-00980: synonym translation is no longer valid

Cause

You tried to reference a synonym that no longer exists.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Reference the object directly (include schema name, if appropriate).

Option #2

Re-create the synonym.

Oracle: ORA-00979 Error Message

ORA-00979 Error Message

Learn the cause and how to resolve the ORA-00979 error message in Oracle.

Description

When you encounter an ORA-00979 error, the following error message will appear:

• ORA-00979: not a GROUP BY expression

Cause

You tried to execute a SELECT statement that included a GROUP BY function (ie: MIN Function, MAX Function, SUM Function, COUNT Function) and an expression in the SELECT list that was not in the GROUP BY clause.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Rewrite the SELECT statement so that the column or expression listed in the SELECT list is also found in the GROUP BY clause.

Option #2

Remove the GROUP BY function (ie: MIN Function, MAX Function, SUM Function, COUNT Function) from the SELECT statement.

Option #3

Remove the expression from the SELECT list that was not in the GROUP BY clause.

For example, if you had tried to execute the following SELECT statement:

SELECT department, company, MIN(salary) AS "Lowest salary"
FROM employees
GROUP BY department;

You would receive the following error message:

You could correct this by including company in the GROUP BY clause as follows:

SELECT department, company, MIN(salary) AS "Lowest salary"
FROM employees
GROUP BY department, company;

Oracle: ORA-00975 Error Message

ORA-00975 Error Message

Learn the cause and how to resolve the ORA-00975 error message in Oracle.

Description

When you encounter an ORA-00975 error, the following error message will appear:

• ORA-00975: date + date not allowed

Cause

You tried to add two dates together. This is not allowed. You can only add numeric values to dates.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Re-write your expression so that a numeric value is added to the date.

For example, if you tried to execute the following SQL statement:

SELECT sysdate + sysdate
FROM dual;

You would receive the following error message:

You could correct this error by substituting one date with a numeric value such as:

SELECT sysdate + 365
FROM dual;

Oracle: ORA-00972 Error Message

ORA-00972 Error Message

Learn the cause and how to resolve the ORA-00972 error message in Oracle.

Description

When you encounter an ORA-00972 error, the following error message will appear:

• ORA-00972: identifier is too long

Cause

You tried to reference a table, cluster, view, index, synonym, tablespace, or username with a value that was longer than 30 characters.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Names for tables, clusters, views, indexes, synonyms, tablespaces, and usernames must be 30 characters or less. You must shorten the name to no more than 30 characters for these objects.

Oracle: ORA-00971 Error Message

ORA-00971 Error Message

Learn the cause and how to resolve the ORA-00971 error message in Oracle.

Description

When you encounter an ORA-00971 error, the following error message will appear:

• ORA-00971: missing SET keyword

Cause

You tried to execute a UPDATE statement, but you missed the SET keyword.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Re-write your UPDATE statement to include the SET clause.

For example, if you tried to execute the following UPDATE statement:

UPDATE suppliers
supplier_id = 1000;

You would receive the following error message:

You could correct this error by including the SET keyword as follows:

UPDATE suppliers
SET supplier_id = 1000;

Oracle: ORA-00962 Error Message

ORA-00962 Error Message

Learn the cause and how to resolve the ORA-00962 error message in Oracle.

Description

When you encounter an ORA-00962 error, the following error message will appear:

• ORA-00962: too many group-by or order-by expressions

Cause

You tried to execute a SQL statement that contained more than 255 GROUP BY or ORDER BY expressions.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Re-write your SQL statement so that there are no more than 255 Group By or Order By expressions.

Oracle: ORA-00960 Error Message

ORA-00960 Error Message

Learn the cause and how to resolve the ORA-00960 error message in Oracle.

Description

When you encounter an ORA-00960 error, the following error message will appear:

• ORA-00960: ambiguous column naming in select list

Cause

You tried to execute a SELECT statement where a column with the same name is listed in the SELECT list and then ambiguously defined within the ORDER BY clause.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Try prefixing the column that is ambiguously defined in the ORDER BY clause with the table name.

For example, if you tried to execute the following SELECT statement:

SELECT suppliers.supplier_id, orders.supplier_id
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
ORDER BY supplier_id;

You would receive the following error message:

You could correct this error by prefixing the supplier_id column in the ORDER BY clause with either supplier or orders.

For example:

SELECT suppliers.supplier_id, orders.supplier_id
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
ORDER BY suppliers.supplier_id;

OR

SELECT suppliers.supplier_id, orders.supplier_id
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
ORDER BY orders.supplier_id;

Oracle: ORA-00957 Error Message

ORA-00957 Error Message

Learn the cause and how to resolve the ORA-00957 error message in Oracle.

Description

When you encounter an ORA-00957 error, the following error message will appear:

• ORA-00957: duplicate column name

Cause

You tried to execute either a CREATE TABLE or INSERT statement where the same column name was listed more than once.

Or you tried to execute a UPDATE statement where the same column name was listed more than once in the SET clause.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

If this error occurred during a CREATE TABLE, correct the statement so that each column listed in the CREATE TABLE statement is unique.

For example, if you tried to create the following table:

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  supplier_id numeric(8)
);

You would receive the following error message:

Since the supplier_id field appears twice, the CREATE TABLE statement will fail. You need to ensure that each column name is unique.

You could correct the statement as follows:

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  supplier_id2 numeric(8)
);

Option #2

If this error occurred during an INSERT statement, correct the statement so that each column listed is unique.

For example, if you tried to execute the following INSERT statement:

INSERT INTO supplier
(supplier_id, supplier_name, supplier_id)
VALUES
(1000, 'IBM', '1000');

You would receive the following error message:

Since the supplier_id field appears twice, the INSERT statement will fail. You can correct this by removing the duplicate column name as follows:

INSERT INTO supplier
(supplier_id, supplier_name)
VALUES
(1000, 'IBM');

Option #3

If this error occurred during an UPDATE statement, correct the statement so that each column listed in the SET clause is unique.

For example, if you tried to execute the following UPDATE statement:

UPDATE suppliers
SET supplier_id = 1000,
    supplier_id = 1000
WHERE supplier_id = 1;

You would receive the following error message:

Since the supplier_id field appears twice in the SET clause, the UPDATE statement will fail. You can correct this by removing the duplicate column name as follows:

UPDATE suppliers
SET supplier_id = 1000
WHERE supplier_id = 1;

Oracle: ORA-00955 Error Message

ORA-00955 Error Message

Learn the cause and how to resolve the ORA-00955 error message in Oracle.

Description

When you encounter an ORA-00955 error, the following error message will appear:

• ORA-00955: name is already used by an existing object

Cause

You tried to create a table, VIEW, index, synonym or other object with a name that is already in use.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Rename your object that you are trying to create so that it is unique.

Option #2

Drop the existing object and create the new object.

For a listing of objects with a particular name, you can run the following query:

SELECT *
FROM all_objects
WHERE object_name = 'NAME';

For example, if you wanted to find the objects whose name is SUPPLIERS, you could run the following SQL:

SELECT *
FROM all_objects
WHERE object_name = 'SUPPLIERS';

Please note that object_name's are stored in the all_objects table in uppercase.

Oracle: ORA-00948 Error Message

ORA-00948 Error Message

Learn the cause and how to resolve the ORA-00948 error message in Oracle.

Description

When you encounter an ORA-00948 error, the following error message will appear:

• ORA-00948: ALTER CLUSTER statement no longer supported

Cause

You tried to execute an ALTER CLUSTER statement, but this statement is no longer supported.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Try adding your cluster from an existing table.

For example:

CREATE TABLE new_table
  SELECT *
  FROM old_table CLUSTER cluster_name;

DROP old_table;

RENAME TABLE new_table old_table;

Oracle: ORA-00947 Error Message

ORA-00947 Error Message

Learn the cause and how to resolve the ORA-00947 error message in Oracle.

Description

When you encounter an ORA-00947 error, the following error message will appear:

• ORA-00947: not enough values

Cause

You tried to execute a SQL statement that required two equal sets of values, but the second set contains fewer values than the first set.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

This error can occur when you are performing an INSERT and the values entered are less in number than the columns that you are inserting into.

For example, if you tried to execute the following INSERT statement:

INSERT INTO suppliers
(supplier_id, supplier_name, contact_name)
VALUES
(1000, 'Microsoft');

You would receive the following error message:

You could correct this error by reducing the number of columns:

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(1000, 'Microsoft');

Or by increasing the number of values to insert:

INSERT INTO suppliers
(supplier_id, supplier_name, contact_name)
VALUES
(1000, 'Microsoft', 'Bill Gates');

Option #2

This error can also occur when you perform a sub-select in a WHERE clause or HAVING clause but the sub-select returns too few columns.

For example, if you tried to execute the following:

SELECT *
FROM suppliers
WHERE (supplier_id, contact_name) IN (SELECT supplier_id
                                      FROM orders);

You would receive the following error message:

You could correct this error by returning two columns in the sub-select as follows:

SELECT *
FROM suppliers
WHERE (supplier_id, contact_name) IN (SELECT supplier_id, order_contact
                                      FROM orders);

Oracle: ORA-00946 Error Message

ORA-00946 Error Message

Learn the cause and how to resolve the ORA-00946 error message in Oracle.

Description

When you encounter an ORA-00946 error, the following error message will appear:

• ORA-00946: missing TO keyword

Cause

You tried to execute a GRANT command and missed the TO keyword.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Try adding the missing TO keyword and re-execute the command.

Oracle: ORA-00942 Error Message

ORA-00942 Error Message

Learn the cause and how to resolve the ORA-00942 error message in Oracle.

Description

When you encounter an ORA-00942 error, the following error message will appear:

• ORA-00942: table or view does not exist

Cause

You tried to execute a SQL statement that references a table or view that either does not exist, that you do not have access to, or that belongs to another schema and you didn't reference the table by the schema name.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

If this error occurred because the table or view does not exist, you will need to create the table or view.

You can check to see if the table exists in Oracle by executing the following SQL statement:

SELECT *
FROM all_objects
WHERE object_type IN ('TABLE','VIEW')
AND object_name = 'OBJECT_NAME';

For example, if you are looking for a suppliers table, you would execute:

SELECT *
FROM all_objects
WHERE object_type IN ('TABLE','VIEW')
AND object_name = 'SUPPLIERS';

Option #2

If this error occurred because you do not have access to the table or view, you will need to have the owner of the table/view, or a DBA grant you the appropriate privileges to this object.

Option #3

If this error occurred because the table/view belongs to another schema and you didn't reference the table by the schema name, you will need to rewrite your SQL to include the schema name.

For example, you may have executed the following SQL statement:

SELECT *
FROM suppliers;

But the suppliers table is not owned by you, but rather, it is owned by a schema called app, you could fix your SQL as follows:

SELECT *
FROM app.suppliers;

If you do not know what schema the suppliers table/view belongs to, you can execute the following SQL to find out:

SELECT owner
FROM all_objects
WHERE object_type IN ('TABLE','VIEW')
AND object_name = 'SUPPLIERS';

This will return the schema name who owns the suppliers table.

Oracle: ORA-00939 Error Message

ORA-00939 Error Message

Learn the cause and how to resolve the ORA-00939 error message in Oracle.

Description

When you encounter an ORA-00939 error, the following error message will appear:

• ORA-00939: too many arguments for function

Cause

You tried to call an Oracle function, but you supplied too many arguments for the function.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

This error can be corrected by providing the correct number of arguments when calling the Oracle function.

Oracle: ORA-00938 Error Message

ORA-00938 Error Message

Learn the cause and how to resolve the ORA-00938 error message in Oracle.

Description

When you encounter an ORA-00938 error, the following error message will appear:

• ORA-00938: not enough arguments for function

Cause

You tried to call an Oracle function, but you supplied too few arguments for the function.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

This error can be corrected by providing the correct number of arguments when calling the Oracle function.

Oracle: ORA-00937 Error Message

ORA-00937 Error Message

Learn the cause and how to resolve the ORA-00937 error message in Oracle.

Description

When you encounter an ORA-00937 error, the following error message will appear:

• ORA-00937: not a single-group group function

Cause

You tried to execute a SELECT statement that included a GROUP BY function (ie: MIN Function, MAX Function, SUM Function, COUNT Function), but was missing the GROUP BY clause.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Rewrite the SELECT statement so that the column or expression listed in the SELECT list is also found in the GROUP BY clause.

Option #2

Remove the GROUP BY function (ie: MIN Function, MAX Function, SUM Function, COUNT Function) from the SELECT statement.

Option #3

Remove the expression from the SELECT list that was not in the GROUP BY clause.

For example, if you had tried to execute the following SELECT statement:

SELECT department, MIN(salary) AS "Lowest salary"
FROM employees;

You would receive the following error message:

You could correct this by including department in the GROUP BY clause as follows:

SELECT department, MIN(salary) AS "Lowest salary"
FROM employees
GROUP BY department;

Oracle: ORA-00936 Error Message

ORA-00936 Error Message

Learn the cause and how to resolve the ORA-00936 error message in Oracle.

Description

When you encounter an ORA-00936 error, the following error message will appear:

• ORA-00936: missing expression

Cause

You tried to execute a SQL statement but you omitted a part of the syntax.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

This error most commonly occurs when you try to execute a SELECT statement and forget to the list of the columns in the SELECT statement.

For example, you tried to execute the following SELECT statement:

SELECT
FROM suppliers;

You would receive the following error message:

You can correct the SELECT statement by including columns after the SELECT keyword. For example:

SELECT supplier_id, supplier_name
FROM suppliers;

Oracle: ORA-00935 Error Message

ORA-00935 Error Message

Learn the cause and how to resolve the ORA-00935 error message in Oracle.

Description

When you encounter an ORA-00935 error, the following error message will appear:

• ORA-00935: group function is nested too deeply

Cause

This is an internal error message.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Contact customer support.

Oracle: ORA-00934 Error Message

ORA-00934 Error Message

Learn the cause and how to resolve the ORA-00934 error message in Oracle.

Description

When you encounter an ORA-00934 error, the following error message will appear:

• ORA-00934: group function is not allowed here

Cause

You tried to execute a SQL statement that included one of the group functions (ie: MIN Function, MAX Function, SUM Function, COUNT Function) in either the WHERE clause or the GROUP BY clause.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Try removing the group function from the WHERE clause or GROUP BY clause. If required, you can move the group function to the HAVING clause.

For example, if you tried to execute the following SQL statement:

SELECT department, SUM(sales) AS "Total sales"
FROM order_details
WHERE SUM(sales) > 1000
GROUP BY department;

You would receive the following error message:

You could correct this statement by using the HAVING clause as follows:

SELECT department, SUM(sales) AS "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;

Option #2

You could also try moving the group by function to a SQL subquery.

For example, if you tried to execute the following SQL statement:

SELECT department, SUM(sales) AS "Total sales"
FROM order_details
WHERE SUM(sales) > 1000
GROUP BY department;

You would receive the following error message:

You could correct this statement by using a subquery as follows:

SELECT order_details.department,
SUM(order_details.sales) AS "Total sales"
FROM order_details, (SELECT department, SUM(sales) AS "Sales_compare"
                     FROM order_details
                     GROUP BY department) subquery1
WHERE order_details.department = subquery1.department
AND subquery1.Sales_compare > 1000
GROUP BY order_details.department;

Oracle: ORA-00933 Error Message

ORA-00933 Error Message

Learn the cause and how to resolve the ORA-00933 error message in Oracle.

Description

When you encounter an ORA-00933 error, the following error message will appear:

• ORA-00933: SQL command not properly ended

Cause

You tried to execute a SQL statement with an inappropriate clause.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

You may have executed a INSERT statement with a ORDER BY clause. To resolve this, remove the ORDER BY clause and re-execute the INSERT statement.

For example, you tried to execute the following INSERT statement:

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES (24553, 'IBM')
ORDER BY supplier_id;

You can correct the INSERT statement by removing the ORDER BY clause as follows:

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES (24553, 'IBM');

Option #2

You may have tried to execute a DELETE statement with a ORDER BY clause. To resolve this, remove the ORDER BY clause and re-execute the DELETE statement.

For example, you tried to execute the following DELETE statement:

DELETE FROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY supplier_id;

You can correct the DELETE statement by removing the ORDER BY clause as follows:

DELETE FROM suppliers
WHERE supplier_name = 'IBM';

Oracle: ORA-00932 Error Message

ORA-00932 Error Message

Learn the cause and how to resolve the ORA-00932 error message in Oracle.

Description

When you encounter an ORA-00932 error, the following error message will appear:

• ORA-00932: inconsistent datatypes

Cause

You tried to perform an operation between two different datatypes, but the datatypes are not compatible.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Correct the operation so that the datatypes are compatible. You may want to use a conversion function such as: TO_DATE function, TO_NUMBER function, or TO_CHAR function. 

One example of this error is if you try to use the LIKE condition with a LONG datatype.

For example, if you created the following table:

CREATE TABLE suppliers
( supplier_id numeric(10) not null,
  supplier_name long not null,
  contact_name varchar2(50)
);

And then you tried to use the LIKE condition on the supplier_name column which as defined as a LONG data type:

SELECT *
FROM suppliers
WHERE supplier_name LIKE 'IBM%';

You would receive the following error message:

Unfortunately, you can not use the LIKE condition on a LONG data type.

To correct this error, you can do one of the following:

• Not use the LIKE condition in your SQL (against the LONG datatype field).
• Consider modifying your table so that the supplier_name field is either a VARCHAR2 or CHAR field.
• Try writing a custom PLSQL function to convert a LONG to a VARCHAR2.

Option #2

This error can also occur if you try to use an Oracle function on a LONG datatype.

For example, if you created the following table:

CREATE TABLE suppliers
( supplier_id numeric(10) not null,
  supplier_name long not null,
  contact_name varchar2(50)
);

And then you tried to use the TO_CHAR function on the supplier_name column which as defined as a LONG data type:

SELECT upper(supplier_name)
FROM suppliers;

You would receive the following error message:

Unfortunately, you can not use Oracle functions on a LONG data type.

To correct this error, you can do one of the following:

• Not use Oracle functions in your SQL (against the LONG datatype field).
• Consider modifying your table so that the supplier_name field is either a VARCHAR2 or CHAR field.
• Try writing a custom PLSQL function to convert a LONG to a VARCHAR2.