Oracle - Check the presence of columns (yes/no) in a table

I am trying to write an Oracle query that accepts multiple columns of a table and returns rows for each column with a flag whether the input column exists or not?

Table DDL -

CREATE TABLE orders
    ( 
       id           NUMBER(10),
      order_mode     VARCHAR2(8),
      cust_id        NUMBER(6),
      status       NUMBER(2)
   )

To find out which column is present in the table, I execute below query. But it fails with the error message "SQL Error [936][42000]: ORA -00936: missing expression" fail.

SELECT column_name,
       exists (
          SELECT 1
          FROM all_tab_columns
          WHERE table_name = 'ORDERS'
          AND column_name IN ('ORDER_MODE', 'CUST_ID', 'ABC')
       ) AS exists_one_zero
FROM dual;

The below query also fails with "SQL Error [904] [42000]: ORA-00904: COLUMN_NAME: invalid identifier".

SELECT column_name,
       CASE WHEN exists (
          SELECT 1
          FROM all_tab_columns
          WHERE table_name = 'ORDERS'
          AND column_name IN ('ORDER_MODE', 'CUST_ID', 'ABC')
       ) THEN 'Yes' ELSE 'No' END AS exists_yes_no
FROM dual;

May I know what is the problem in above queries ?

Your first query fails because Oracle does not allows a predicate such as exists in the select clause like that; some database would turn it to a boolean value, but in Oracle you need to wrap the predicate in a case expression (it will work in 23c though).

In the second query, the problem is that column_name is not defined in the outer select, which is from dual (only the inner select sees that column).

Overall, I would recommend changing the logic to generate a fixed list of columns as rows, then check their presence in all_tab_columns with exists:

select c.column_name, 
    case when exists (
        select 1
        from all_tab_columns t
        where t.table_name = 'ORDERS' and t.column_name = c.column_name
    ) then 'YES' else 'NO' end exists_yes_no
from (
    select 'ORDER_MODE' as column_name from dual
    union all select 'CUST_ID'         from dual
    union all select 'ABC'             from dual
) c

You could took this one step forward and make the table name a variable as well, so you could perform the same check across tables:

select c.column_name, 
    case when exists (
        select 1
        from all_tab_columns t
        where t.table_name = c.table_name and t.column_name = c.column_name
    ) then 'YES' else 'NO' end exists_yes_no
from (
    select 'ORDERS' table_name, 'ORDER_MODE' as column_name from dual
    union all select 'CUSTOMERS', 'CUST_ID' from dual
) c

Side note: I would recommend adding column OWNER to the WHERE clause of the ALL_TAB_COLUMNS lookup, since different tables may have the same name in different schemas. Alternatively consider querying USER_TAB_COLUMNS, which lets you access your own schema only.

You don't need to use EXISTS. Instead you could Left Join two queries - the one that defines your list of column names to be tested against a table and the other fetching column names of your table from all_tab_columns. Check the existance using Case expression

Select  
    l.COLUMN_NAME, 
    CASE WHEN c.COLUMN_NAME Is Null THEN 'NO' ELSE 'YES' END "EXISTS"  

From      
    (   Select 'ORDER_MODE' "COLUMN_NAME" From Dual Union All
        Select 'CUST_ID'     From Dual Union All
        Select 'SOME_COL'    From Dual  ) l

Left Join    
    ( Select COLUMN_NAME 
      From all_tab_columns 
      Where TABLE_NAME = 'ORDERS' And
            OWNER = 'TAB_OWNER_NAME') c ON(c.COLUMN_NAME = l.COLUMN_NAME)

--  
--  R e s u l t :       
--  COLUMN_NAME EXISTS
--  ----------- ------
--  ORDER_MODE  YES
--  CUST_ID     YES
--  SOME_COL    NO