Get a comma separated line of Oracle table column names

When you have to manually put together an insert statement into a table with large number of columns you may feel some pain – it is tedious to concatenate all the columns in a comma delimited line. The following small procedure may save you some time and effort.

CREATE OR REPLACE PROCEDURE sp_get_column_line(p_table VARCHAR2)
    v_result VARCHAR2(2000);
    SELECT listagg (column_name, ', ') WITHIN GROUP (ORDER BY column_id)
    INTO v_result
    FROM user_tab_cols
    WHERE table_name=UPPER(p_table);

SQL> exec sp_get_column_line('emp')

PL/SQL procedure successfully completed.

SQL> exec sp_get_column_line('emp')
empno, ename, job, mgr, hiredate, sal, comm, deptno

PL/SQL procedure successfully completed.

If you like this post, you may want to join my new Oracle group on Facebook:

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.


2 thoughts on “Get a comma separated line of Oracle table column names

  1. jolivercomblog May 10, 2016 / 12:11 pm

    Mr. Hilkevich:Can recommend a good book to learn PL/SQL? I will be needing a book that can take me from beginning to intermediate/advanced. Thanks in advance, JamesO

    Date: Thu, 5 May 2016 16:37:38 +0000 To:

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s