Overview

When querying a single table with many columns, there is the inconvenience of having to check each column's description one by one and then rewrite the SELECT statement.

To reduce this inconvenience, you can create a separate procedure to query data in a more readable format.

Method

Code

CREATE OR REPLACE PROCEDURE SYS.SP_PRINT_DATA(
    IN_QUERY IN VARCHAR2    /* Query */
)
AUTHID CURRENT_USER
IS
/***********************************************************************************
    Program Name : SP_PRINT_DATA

    Purpose : Vertically align long row data for better readability
    How to Use
        SQL> set serveroutput on ;
        SQL> exec sp_print_data('select * from v$session') ; 
        SQL> exec sp_print_table('select * from v$database where STATUS=''RUNNING''') ; -- When using a WHERE clause
 ***********************************************************************************/
    V_CURSOR    INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
    V_COL_VAL   VARCHAR2(4000);
    V_STATUS    INTEGER;
    V_DESC_TB   DBMS_SQL.DESC_TAB ;
    V_COL_CNT   NUMBER;
BEGIN
    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''' ;

    DBMS_SQL.PARSE(V_CURSOR, IN_QUERY, DBMS_SQL.NATIVE) ;
    DBMS_SQL.DESCRIBE_COLUMNS(V_CURSOR, V_COL_CNT, V_DESC_TB ) ;
    
    FOR I IN 1 .. V_COL_CNT LOOP
        DBMS_SQL.DEFINE_COLUMN(V_CURSOR, I, V_COL_VAL, 4000) ;
    END LOOP ;
    
    V_STATUS := DBMS_SQL.EXECUTE(V_CURSOR);
    DBMS_OUTPUT.PUT_LINE( '-------------------------------------------------------------------------' );    
    WHILE ( DBMS_SQL.FETCH_ROWS(V_CURSOR) > 0 ) LOOP
        FOR I IN 1 .. V_COL_CNT LOOP
            DBMS_SQL.COLUMN_VALUE( V_CURSOR, I, V_COL_VAL );
            --DBMS_OUTPUT.PUT_LINE( RPAD( V_DESC_TB(I).COL_NAME, 30 ) || ' : ' || V_COL_VAL );
            DBMS_OUTPUT.PUT_LINE( RPAD( V_DESC_TB(I).COL_NAME, 30 ) || ' : ' || REPLACE(REPLACE(V_COL_VAL, CHR(10), ''), CHR(13), '') );
        END LOOP;
        DBMS_OUTPUT.PUT_LINE( '-------------------------------------------------------------------------' );
    END LOOP;
    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''';
EXCEPTION
    WHEN OTHERS THEN
      EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''';
      RAISE;
END;
/

CREATE PUBLIC SYNONYM SP_PRINT_DATA FOR SYS.SP_PRINT_DATA;
GRANT EXECUTE ON SYS.SP_PRINT_DATA TO PUBLIC;

Existing Query Method

When querying the dba_tables object, which has many columns, it looks as follows.

SQL> select * from dba_tables where rownum < 2;

OWNER
--------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME                                                                                                                    PCT_FREE
-------------------------------------------------------------------------------------------------------------------------------- ----------
 INI_TRANS LOGGING   NUM_ROWS     BLOCKS AVG_ROW_LEN     DEGREE SAMPLE_SIZE
---------- ------- ---------- ---------- ----------- ---------- -----------
LAST_ANALYZED                                                                                                                    PARTITIONED
-------------------------------------------------------------------------------------------------------------------------------- -----------
BUFFER_POOL ROW_MOVEMENT DURATION    COMPRESSION COMPRESS_FOR DROPPED READ_ONLY TEMPORARY MAX_EXTENTS IOT_TYPE     INITIAL_EXTENT NEXT_EXTENT
----------- ------------ ----------- ----------- ------------ ------- --------- --------- ----------- ------------ -------------- -----------
MIN_EXTENTS IS_VIRTUAL INMEMORY INMEMORY_PRIORITY INMEMORY_DISTRIBUTE INMEMORY_COMPRESSION INMEMORY_DUPLICATE
----------- ---------- -------- ----------------- ------------------- -------------------- ------------------
SYS
ALL_DML_INDEX_STAT$
SYSTEM                                                                                                                                   10
         2 YES            566         16  22.1166078          0         566
2025/12/09                                                                                                                       NO
DEFAULT     DISABLED                 NO                       NO      NO        NO         4294967295                      131072      131072
          1 N          DISABLED


1 row selected.

Query Using the Readability Procedure

SQL> set serveroutput on ;
SQL> exec sp_print_data('select * from dba_tables where rownum < 2') ; 
-------------------------------------------------------------------------
OWNER                          : SYS
TABLE_NAME                     : ALL_DML_INDEX_STAT$
TABLESPACE_NAME                : SYSTEM
PCT_FREE                       : 10
INI_TRANS                      : 2
LOGGING                        : YES
NUM_ROWS                       : 566
BLOCKS                         : 16
AVG_ROW_LEN                    : 22.1166077738515901060070671378091872792
DEGREE                         : 0
SAMPLE_SIZE                    : 566
LAST_ANALYZED                  : 2025-12-09 12:54:30
PARTITIONED                    : NO
BUFFER_POOL                    : DEFAULT
ROW_MOVEMENT                   : DISABLED
DURATION                       :
COMPRESSION                    : NO
COMPRESS_FOR                   :
DROPPED                        : NO
READ_ONLY                      : NO
TEMPORARY                      : NO
MAX_EXTENTS                    : 4294967295
IOT_TYPE                       :
INITIAL_EXTENT                 : 131072
NEXT_EXTENT                    : 131072
MIN_EXTENTS                    : 1
IS_VIRTUAL                     : N
INMEMORY                       : DISABLED
INMEMORY_PRIORITY              :
INMEMORY_DISTRIBUTE            :
INMEMORY_COMPRESSION           :
INMEMORY_DUPLICATE             :
-------------------------------------------------------------------------

PSM completed.

Querying Multiple Rows

You can also query multiple rows, not just a single row.

SQL> set serveroutput on ;
SQL> exec sp_print_data('select * from dba_tables where rownum < 3') ; 
-------------------------------------------------------------------------
OWNER                          : SYS
TABLE_NAME                     : ALL_DML_INDEX_STAT$
TABLESPACE_NAME                : SYSTEM
PCT_FREE                       : 10
INI_TRANS                      : 2
LOGGING                        : YES
NUM_ROWS                       : 566
BLOCKS                         : 16
AVG_ROW_LEN                    : 22.1166077738515901060070671378091872792
DEGREE                         : 0
SAMPLE_SIZE                    : 566
LAST_ANALYZED                  : 2025-12-09 12:54:30
PARTITIONED                    : NO
BUFFER_POOL                    : DEFAULT
ROW_MOVEMENT                   : DISABLED
DURATION                       :
COMPRESSION                    : NO
COMPRESS_FOR                   :
DROPPED                        : NO
READ_ONLY                      : NO
TEMPORARY                      : NO
MAX_EXTENTS                    : 4294967295
IOT_TYPE                       :
INITIAL_EXTENT                 : 131072
NEXT_EXTENT                    : 131072
MIN_EXTENTS                    : 1
IS_VIRTUAL                     : N
INMEMORY                       : DISABLED
INMEMORY_PRIORITY              :
INMEMORY_DISTRIBUTE            :
INMEMORY_COMPRESSION           :
INMEMORY_DUPLICATE             :
-------------------------------------------------------------------------
OWNER                          : SYS
TABLE_NAME                     : ALL_DML_INDEX_STAT_HISTORY
TABLESPACE_NAME                : SYSTEM
PCT_FREE                       : 10
INI_TRANS                      : 2
LOGGING                        : YES
NUM_ROWS                       : 0
BLOCKS                         : 0
AVG_ROW_LEN                    : 0
DEGREE                         : 0
SAMPLE_SIZE                    :
LAST_ANALYZED                  : 2025-12-09 12:54:30
PARTITIONED                    : NO
BUFFER_POOL                    : DEFAULT
ROW_MOVEMENT                   : DISABLED
DURATION                       :
COMPRESSION                    : NO
COMPRESS_FOR                   :
DROPPED                        : NO
READ_ONLY                      : NO
TEMPORARY                      : NO
MAX_EXTENTS                    : 4294967295
IOT_TYPE                       :
INITIAL_EXTENT                 : 131072
NEXT_EXTENT                    : 131072
MIN_EXTENTS                    : 1
IS_VIRTUAL                     : N
INMEMORY                       : DISABLED
INMEMORY_PRIORITY              :
INMEMORY_DISTRIBUTE            :
INMEMORY_COMPRESSION           :
INMEMORY_DUPLICATE             :
-------------------------------------------------------------------------

PSM completed.