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.