Oracle / PLSQL: ORA-01417 Error Message

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

Description

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

• ORA-01417: a table may be outer joined to at most one other table

Cause

You tried to perform an outer join to the same table from more than one other table.

Resolution

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

Option #1

Correct your SQL so that you are only performing an outer join to the same table from only one other table.

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

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_id
FROM suppliers, orders, products
WHERE suppliers.supplier_id = orders.supplier_id(+)
AND products.supplier_id = orders.supplier_id(+);

You would receive the following error message:

You've tried to perform an outer join to the orders table from both the suppliers and products tables.

You could correct this SQL statement by removing the second outer join. For example:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_id
FROM suppliers, orders, products
WHERE suppliers.supplier_id = orders.supplier_id(+)
AND products.supplier_id = orders.supplier_id;