blog:commalistofcolumns

Table column list

How to get a CSV list of columns for a table?

/** Supplied with a table name return a comma separated list of its columns in ascending order.
*/
FUNCTION getColumnList (fromuser VARCHAR2, ptablename VARCHAR2) RETURN VARCHAR2 IS
  columnArray dbms_utility.uncl_array;
  csvLen BINARY_INTEGER;
  csvList VARCHAR2(4000);
BEGIN
  SELECT column_name
  BULK COLLECT INTO columnArray
  FROM all_tab_cols
  WHERE table_name = ptablename
  AND owner = fromuser
  AND hidden_column = 'NO'
  ORDER BY column_name;
 
  dbms_utility.table_to_comma(columnArray, csvLen, csvList);
 
  RETURN csvList;
END;
  • blog/commalistofcolumns.txt
  • Last modified: 2009/11/27 17:53
  • by 127.0.0.1