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 with each other. Let's see 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.