Combine SELECT from TABLE with all_tab_columns

I would like to combine below into a single statement -

1. Look for the existence of a column (or list of columns) in ALL_TAB_COLUMNS.
2. If a column exists, it should be included in the SELECT column list.
3. Execute the constructed SELECT statement and return the result.

Table:

CREATE TABLE SCHEMA.TEST 
(
    id int PRIMARY KEY, 
    name varchar(20), 
    address varchar(20)
);

The query below only outputs the columns of interest, but does not also contain the column value.

SELECT c.* FROM 
    (SELECT column_name FROM all_tab_columns where 
        owner = 'SCHEMA' AND table_name = 'TEST' and column_name IN ('ID', 'NAME')) c

** Update **

The requirement is to get the name of the column(s) as input and return the values for each column. There is a possibility that the column name in the table does not match due to a typo or dropped before the request was made. The simplest approach is to use the SELECT statement with all columns. If the query fails because the column does not exist, remove the column from the list and re-execute the SELECT statement. The complexity of the logic increases if the stored procedure/function loops to check for an error and remove the column.

Another solution is to check the input column against the all_tab_columns table and create a SELECT query with the columns present. I had written the statement as below -

DECLARE 
	colname varchar2(4000);	
BEGIN
	SELECT listagg(column_name, ',') WITHIN GROUP (ORDER BY column_name) INTO colname FROM all_tab_columns
		where owner = 'SCHEMA' AND table_name = 'TEST' and column_name IN ('ID', 'NAME', 'ADD') ;
	IF colname IS NOT NULL THEN
		EXECUTE IMMEDIATE 'SELECT ' || colname || 'FROM SCHEMA.TEST WHERE ID =  1';
	END IF;
END;

At first glance, there are three problems with the above solution -

1. How many input columns it can accumulate at most.
2. How to use it with JDBC, the result set will always be empty. This is due to EXECUTE IMMEDIATE.
3. I am not sure how to use PK into the solution. The type of PK can be any type supported in the database.

You may use Polymorphic Table Function for this task.

However, the requirement is not clear, because you may specify the required columns when you build a SQL query: your application should be able either to specify existing columns upfront or handle absent columns in the result. In both cases this logic may be translated into the query composition task.

create or replace package pkg_projection as
  function describe(tab in out dbms_tf.table_t, cols varchar2 default null)
    return dbms_tf.describe_t
  ;
end pkg_projection;
/

create or replace package body pkg_projection as
  function describe(tab in out dbms_tf.table_t, cols varchar2 default null)
    return dbms_tf.describe_t
  as
  begin

    if cols is null then
      return null;
    end if;

    for i in 1..tab.column.count() loop
      /*Mark columns to be propagated*/
      tab.column(i).pass_through := ',' || upper(cols) || ',' like '%,' || trim(both '"' from upper(tab.column(i).description.name)) || ',%';
    end loop;

    return null;
  end;
end pkg_projection;
/

create or replace function f_projection(tab table, cols varchar2 default null)
return table pipelined
row polymorphic using pkg_projection;
/

Given this sample data:

create table t(
  id,val1,val2
)
  as
select level, -level, lpad(level, 3, '0')
from dual
connect by level < 4

it returns below results:

select *
from f_projection(t, 'ID,VAL')

 ID
 --
 1 
 2 
 3 

select *
from f_projection(t, 'ID,VAL2')

 ID | VAL2
 ---|-----
 1  | 001
 2  | 002
 3  | 003

select *
from f_projection(t, null)

 ID | VAL1 | VAL2
 ---|------|----
 1  | -1   | 001
 2  | -2   | 002
 3  | -3   | 003

fiddle

You cannot do this in pure SQL.

You can use PL/SQL and dynamically build the SQL and a cursor:

DECLARE
  v_schema        VARCHAR2(30) := USER;
  v_table         VARCHAR2(30) := 'TEST';
  v_columns       identifier_list := identifier_list('ID', 'NAME');
  v_found_columns identifier_list;
  v_sql           CLOB;
  v_cursor        INTEGER;
  v_dummy         NUMBER;
  v_value         VARCHAR2(4000);
  v_row           PLS_INTEGER := 0;
BEGIN
  SELECT column_name
  BULK COLLECT INTO v_found_columns
  FROM   all_tab_columns
  WHERE  owner       = v_schema
  AND    table_name  = v_table
  AND    column_name MEMBER OF v_columns;

  IF v_found_columns IS EMPTY THEN
    RETURN;
  END IF;
  v_sql := EMPTY_CLOB() || 'SELECT "' || v_found_columns(1) || '"';
  FOR i IN 2 .. v_found_columns.COUNT LOOP
    v_sql := v_sql || ', "' || v_found_columns(i) || '"';
  END LOOP;
  v_sql := v_sql || ' FROM "' || v_schema || '"."' || v_table || '"';

  DBMS_OUTPUT.PUT_LINE(v_sql);

  v_cursor := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);

  FOR i IN 1 .. v_found_columns.COUNT LOOP
    DBMS_SQL.DEFINE_COLUMN(v_cursor, i, v_value, 4000);
  END LOOP;
  v_dummy  := DBMS_SQL.EXECUTE(v_cursor);

  LOOP
    EXIT WHEN DBMS_SQL.FETCH_ROWS(v_cursor) <= 0;
    v_row := v_row + 1;
    DBMS_OUTPUT.PUT_LINE('Row ' || v_row);
    FOR i IN 1 .. v_found_columns.COUNT LOOP
      DBMS_SQL.COLUMN_VALUE(v_cursor, i, v_value);
      DBMS_OUTPUT.PUT_LINE('  ' || RPAD(v_found_columns(i), 30, ' ') || ' = ' || v_value);
    END LOOP;
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;
/

Which uses the type:

CREATE TYPE identifier_list IS TABLE OF VARCHAR2(30);

Then if you have the table:

CREATE TABLE TEST(id, name) AS
SELECT 1, 'abc' FROM DUAL;

Then the output is:

> lang-none
> SELECT "ID", "NAME" FROM "FIDDLE_KLYCWRFYONMLIIJLPZYP"."TEST"
> Row 1
> ID = 1
> NAME = abc
>

fiddle