Extrating Table Column Names in CSV format using PL/SQL

This could be useful for writing dynamic code to generate DDL or DML.

1. Create a table Hello_world
create table hello_world(
hello_id integer,
hello_desc varchar2(30)
);

2. Insert some values [this is not important though]
insert into hello_world (hello_id, hello_desc) values (1, 'Fawad');
insert into hello_world (hello_id, hello_desc) values (2, 'NAZIR');

3. Execute the following function:

Input: table name
Output: CSV format of column names

CREATE OR REPLACE FUNCTION INSERT_STATEMENTS(TABLE_NAME_INPUT IN CLOB) RETURN VARCHAR IS
RETURN_VAL CLOB;
BEGIN
SELECT
LISTAGG (COLUMN_NAME, ',') WITHIN GROUP (ORDER BY COLUMN_NAME) INTO RETURN_VAL
FROM
ALL_TAB_COLUMNS
WHERE
TABLE_NAME LIKE TABLE_NAME_INPUT
GROUP BY
TABLE_NAME;
RETURN RETURN_VAL;
END INSERT_STATEMENTS;

4. Call the method:

SET SERVEROUTPUT ON
declare dd CLOB;
begin
SELECT INSERT_STATEMENTS(UPPER('hello_world')) into dd FROM DUAL;
DBMS_OUTPUT.put_line(dd);
end;