By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE addressdata AS
WITH t(addr1, addr2, addr3, addr4, addr5) AS
(
SELECT 'Unit 84884 Block 2-2', 'Jalan 84884', 'Taman 84884', 'REGION 4-a CALABARZON', 'SAN PASCUAL' FROM dual
)
SELECT *
FROM t
1 rows affected
DECLARE
v_str VARCHAR2(1500);
v_table VARCHAR2(100):='ADDRESSDATA';
BEGIN
SELECT LISTAGG(c.column_name,'||'',''||CHR(13)||') WITHIN GROUP (ORDER BY c.column_id)
INTO v_str
FROM user_tab_cols c
WHERE c.table_name = v_table
AND c.column_name LIKE 'ADDR%';
EXECUTE IMMEDIATE 'SELECT '||v_str||
' FROM '||v_table INTO v_str;
DBMS_OUTPUT.PUT_LINE(v_str);
END;
/
1 rows affected
dbms_output:
Unit 84884 Block 2-2,
Jalan 84884,
Taman 84884,
REGION 4-a CALABARZON,
SAN PASCUAL