how to take second column if first column not found in table in oracle DB

Question

I'm expecting to write a query which takes second column if first column not found in table in oracle DB. In my case 'name' column is not present in table 'employees'

NOTE : I'm using reference cursor

I tried below,

query1:='select id or name,age from employees';

when I execute above statement, getting error

ORA-00904 "name": invalid identifier

ORA-06512 : at "employees", line 21

Answer 1

Explicitly, I don't think you can (as you saw).

Though, you can select * from employees and it'll work:

SQL> declare
  2    l_rc    sys_refcursor;
  3    l_row   dept%rowtype;
  4  begin
  5    open l_rc for select * from dept;
  6    fetch l_rc into l_row;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>

---

Alternatively, did you consider creating a select statement dynamically, by querying user_tab_columns? Something like this:

SQL> declare
  2    l_str   varchar2(500);
  3    l_rc    sys_refcursor;
  4  begin
  5    for cur_r in (select column_name from user_tab_columns
  6                  where table_name = 'DEPT'
  7                 )
  8    loop
  9      l_str := l_str || ', '|| cur_r.column_name;
 10    end loop;
 11
 12    l_str := 'select ' || ltrim(l_str, ', ') || ' from dept';
 13
 14    dbms_output.put_line(l_str);
 15
 16    open l_rc for l_str;
 17  end;
 18  /
select DEPTNO, DNAME, LOC from dept           --> this is the SELECT statement 

PL/SQL procedure successfully completed.

SQL>