By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TYPE identifier_list IS TABLE OF VARCHAR2(30);
CREATE TABLE TEST(id, name, val1, val2) AS
SELECT 1, 'abc', 42, 3.14159 FROM DUAL;
1 rows affected
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);
1 rows affected
dbms_output:
SELECT "ID", "NAME" FROM "FIDDLE_UASBBWAYNJXAAUKQKJWO"."TEST"
Row 1
ID = 1
NAME = abc
DECLARE
v_schema VARCHAR2(30) := USER;
v_table VARCHAR2(30) := 'TEST';
v_columns identifier_list := identifier_list('ID', 'VAL2', 'VAL3');
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);
1 rows affected
dbms_output:
SELECT "ID", "VAL2" FROM "FIDDLE_UASBBWAYNJXAAUKQKJWO"."TEST"
Row 1
ID = 1
VAL2 = 3.14159