How do I order the columns of a SQL statement (not the rows)?

Question

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.

Answer 1

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_ > 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 <= 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 <= 5)

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

hr.employees with columns sorted

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.

Answer 2

What Column is for What

In order to "jog your memory", take a look at the COMMENTS for the columns. If all of the comments are NULL, yell at the department that is responsible for that table.

In SQL*Developer

• Put your cursor on the table name within your SELECT statement
• Press Shift+F4 to pull up a window that lists the Column Names.
• double click on the "COLUMN_NAME" title to sort by column name.
• Highlight all of the column name
• drag+drop onto the Worksheet.
• press CTRL+F7 to format the code.

enter image description here

enter image description here

Other IDE

RTFM (because I have no idea if they can do something similar)

If your tool does not contain the features that you need, get a new tool.

• durette: "yell at the department that is responsible for that table" It's a third-party vendor. I wish I could.

Answer 3

You can do the same with just using listagg function query and output needs to be copy paste

undefine table_name -- to change table name and prompt for subsequent run since &&table_name was used in query
SELECT 'SELECT ' ||column_name|| ' FROM &&table_name;' col from 
 (SELECT LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_name)column_name
FROM user_tab_cols WHERE table_name=UPPER('&table_name'));

enter image description here

Note:-I don't have enough points to add as a comment

• durette: Thanks for that, but the queries are usually more complicated than just a single view or table.