0

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.

1

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

0

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.

0

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.