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)
AS
    v_result VARCHAR2(2000);
BEGIN
    SELECT listagg (column_name, ', ') WITHIN GROUP (ORDER BY column_id)
    INTO v_result
    FROM user_tab_cols
    WHERE table_name=UPPER(p_table);

    DBMS_OUTPUT.PUT_LINE(LOWER(v_result));
END;
/
SQL> exec sp_get_column_line('emp')

PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON
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: https://www.facebook.com/groups/sqlpatterns/

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

Advertisements

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: james_l_oliver@hotmail.com

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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