How to Resolve ORA-01730: invalid number of column names specified

  • by

ORA-01730

ORA-01730 means that the number of listed columns between the target (the view) and the source (the base table) in the CREATE VIEW statement does not match. Let's some cases:

The first case shows the number of defined columns is more than the number of retrieved columns.

SQL> conn hr/hr
Connected.
SQL> create or replace view v_emp_names (f_name, l_name, salary) as select first_name, last_name from employees;
create or replace view v_emp_names (f_name, l_name, salary) as select first_name, last_name from employees
                                    *
ERROR at line 1:
ORA-01730: invalid number of column names specified

As we can see, the view wants 3 columns to be defined, but the base table provides only 2 columns.

The second case shows the number of defined columns is less than the number of retrieved columns.

SQL> create or replace view v_emp_names (f_name, l_name) as select first_name, last_name, salary from employees;
create or replace view v_emp_names (f_name, l_name) as select first_name, last_name, salary from employees
                                    *
ERROR at line 1:
ORA-01730: invalid number of column names specified

As we can see, the view wants 2 columns to be defined, but the base table provides 3 columns.

Solution

The first way to solve ORA-01730 is to make the view and the base table match each other.

SQL> create or replace view v_emp_names (f_name, l_name, salary) as select first_name, last_name, salary from employees;

View created.

Or you can take whatever the base table provides without specifying columns for the view.

SQL> create or replace view v_emp_names as select first_name, last_name, salary from employees;

View created.

As you can see, we don't rename the columns retrieved form the base table in the view so as to we prevent ORA-01730 in the beginning.

After views created, you may find that compile errors like ORA-04063: view has errors is ambiguous and hard to troubleshoot. This is one nature of view.

Leave a Reply

Your email address will not be published. Required fields are marked *