How do I order the columns of a SQL statement (not the rows)? I'm an ERP analyst, and when I find something I haven't seen in a long time, I need to jog my memory to know which column contains the data I want. I need to examine the data to do this, so a query against USER_TAB_COLS isn't sufficient. I normally just do a SELECT * and start scrolling.

The tables often have over 100 columns, and more than a handful have over 200. It's mentally exhausting to scroll to the right in my GUI tools to scan manually. If I could alphabetize the columns, I'd have an easier time finding what I need.
I came up with this solution but would definitely welcome improvements and other answers.

CREATE SEQUENCE column_sort_seq
   START WITH 0
   MINVALUE 0
   MAXVALUE 9999
   INCREMENT BY 1
   NOCACHE
   CYCLE;

CREATE OR REPLACE PACKAGE column_sort_api IS
   FUNCTION sort_query_columns(
      sql_ IN VARCHAR2) RETURN VARCHAR2;
   PROCEDURE drop_all_temporary_views;
END column_sort_api;
/

CREATE OR REPLACE PACKAGE BODY column_sort_api IS
   temporary_view_prefix_ CONSTANT VARCHAR2(30) := 'COLUMN_SORT_TEMP_VIEW_';

   FUNCTION get_next_temporary_view_name_ RETURN VARCHAR2;

   FUNCTION sort_query_columns(
      sql_ IN VARCHAR2) RETURN VARCHAR2
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
      temporary_view_name_ VARCHAR2(30) := get_next_temporary_view_name_;
      modified_sql_ VARCHAR2(32767);
      ddl_ VARCHAR2(32767);
      column_concatenation_ VARCHAR2(32767);
   BEGIN
      modified_sql_ := RTRIM(TRIM(sql_), ';');
      ddl_ := 'CREATE OR REPLACE VIEW ' || temporary_view_name_ || ' AS '
           || 'SELECT * FROM (' || modified_sql_ || ')';
      EXECUTE IMMEDIATE ddl_;
      SELECT LISTAGG(LOWER(column_name), ', ') WITHIN GROUP (ORDER BY column_name ASC)
        INTO column_concatenation_
        FROM user_tab_cols
       WHERE table_name = temporary_view_name_;
      ddl_ := 'DROP VIEW ' || temporary_view_name_;
      EXECUTE IMMEDIATE ddl_;
      RETURN 'SELECT ' || column_concatenation_ || ' FROM (' || modified_sql_ || ')';
      COMMIT;
   END sort_query_columns;

   FUNCTION get_next_temporary_view_name_ RETURN VARCHAR2
   IS
      view_name_index_ NUMBER;
      record_count_ NUMBER;
      view_name_ VARCHAR2(30);
      attempts_ NUMBER := 0;
   BEGIN
      LOOP
         IF attempts_ &gt; 10000 THEN
            drop_all_temporary_views;
         END IF;
         view_name_index_ := column_sort_seq.NEXTVAL;
         view_name_ := temporary_view_prefix_ || TO_CHAR(view_name_index_, 'FM0000');
         SELECT COUNT(*)
           INTO record_count_
           FROM user_views
          WHERE view_name = view_name_;
         attempts_ := attempts_ + 1;
         EXIT WHEN record_count_ = 0;
      END LOOP;
      RETURN view_name_;
   END get_next_temporary_view_name_;

   PROCEDURE drop_all_temporary_views
   IS
   BEGIN
      FOR rec_ IN (
         SELECT view_name
           FROM user_views
          WHERE view_name LIKE temporary_view_prefix_ || '%'
      ) LOOP
         EXECUTE IMMEDIATE 'DROP VIEW ' || rec_.view_name;
      END LOOP;
   END drop_all_temporary_views;

END column_sort_api;
/


It creates a view, then examines the metadata of that view to build a new query. To support a limited amount of concurrency, it works with a set of 10,000 possible view names. In case the system crashes, it checks to make sure the view doesn't exist before using that name. To clear any possible lint, there's a feature to remove all the views.

Here it is in action:

https://livesql.oracle.com/apex/livesql/s/hvqxaqn9pwi8yk276bg4zt5gn

SELECT column_sort_api.sort_query_columns('SELECT * FROM hr.employees WHERE rownum &lt;= 5;')
  FROM DUAL;


Output:



SELECT commission_pct, department_id, email, employee_id, first_name, hire_date, job_id, last_name, manager_id, phone_number, salary FROM (SELECT * FROM hr.employees WHERE rownum &lt;= 5)


Using that output is a matter of copying the result and pasting it back into the editor.



If you can improve this answer, I would like to know if it's possible to bypass the final copy-and-paste step and produce the data via a pipelined function.