Utility: Pivot with column names

Today, I was querying v$instance and got the following output:

select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
              1 oel1
oel1
11.2.0.2.0        12-NOV-10 OPEN         NO           1 STARTED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO
1 row selected.

It seemed to me that it would be nice to have a pivoted output with column names.
This is the result for v$instance:

@pivot v$instance
. INSTANCE_NUMBER: 1
.   INSTANCE_NAME: oel1
.       HOST_NAME: oel1
.         VERSION: 11.2.0.2.0
.    STARTUP_TIME: 12-NOV-10
.          STATUS: OPEN
.        PARALLEL: NO
.         THREAD#: 1
.        ARCHIVER: STARTED
. LOG_SWITCH_WAIT:
.          LOGINS: ALLOWED
.SHUTDOWN_PENDING: NO
. DATABASE_STATUS: ACTIVE
.   INSTANCE_ROLE: PRIMARY_INSTANCE
.    ACTIVE_STATE: NORMAL
.         BLOCKED: NO

This is the result for emp:

@pivot emp
.   EMPNO: 7369, 7499, 7521, 7566, 7654, 7698, 7782, 7788, 7839, 7844, 7876, 7900, 7902, 7934
.   ENAME: SMITH, ALLEN, WARD, JONES, MARTIN, BLAKE, CLARK, SCOTT, KING, TURNER, ADAMS, JAMES, FORD, MILLER
.     JOB: CLERK, SALESMAN, SALESMAN, MANAGER, SALESMAN, MANAGER, MANAGER, ANALYST, PRESIDENT, SALESMAN, CLERK, CLERK, ANALYST, CLERK
.     MGR: 7902, 7698, 7698, 7839, 7698, 7839, 7839, 7566,  , 7698, 7788, 7698, 7566, 7782
.HIREDATE: 17-DEC-80, 20-FEB-81, 22-FEB-81, 02-APR-81, 28-SEP-81, 01-MAY-81, 09-JUN-81, 09-DEC-82, 17-NOV-81, 08-SEP-81, 12-JAN-83, 03-DEC-81, 03-DEC-81, 23-JAN-82
.     SAL: 800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300
.    COMM:  , 300, 500,  , 1400,  ,  ,  ,  , 0,  ,  ,  ,
.  DEPTNO: 20, 30, 30, 20, 30, 30, 10, 20, 10, 30, 20, 30, 20, 10

This is accomplished by two sql files and a c:\tmp directory for temporary files.
pivot.sql:

set feedback off
set heading off
set verify off
set timing off
set termout off
set trimspool on

define session=Bad
column sess new_value session noprint
select 'c:\tmp\pivot_'||trim(userenv('SESSIONID'))||'.sql' sess from dual
/

define max_col=Bad
column max_c new_value max_col noprint
select max(length( column_name )) max_c from  all_tab_columns atc
 where atc.table_name like replace( upper('&1'), 'V$', 'V_$')
/

column column_id_noprint noprint
SPOOL &session
select column_id column_id_noprint, '@pivotone '||column_name||' &1 &max_col' cmd
  from all_tab_columns atc
 where atc.table_name like replace( upper('&1'), 'V$', 'V_$')
   and decode(owner,'SYS',user,owner) = user
order by 1
/
SPOOL OFF
set trimspool off
set termout on
set heading on
set feedback off

@&session

host rm &session

set feedback on
set heading on
set pagesiz 50000

Above is the main file.
Note especially the following:
Line 23: To query column names for V$ views, I need to query V_$ (V$ are synonyms, actual views begin with v_$).
Line 24: Also, I have tables like “emp” in more than on schema. “and decode(owner,’SYS’,user,owner) = user” will ensure that the table pivoted is in my current schema or belongs to SYS.

pivotone.sql:

set heading off
set verify off
set termout off
define this_line=Bad
column this_l new_value this_line noprint
select '.'||lpad('&1',&3)||': '||listagg(nvl(to_char(&1),' '),', ')
         within group (order by rownum) this_l
  from &2
/
set termout on
begin
  dbms_output.put_line( substr('&this_line',1) );
end;
/
set verify on
set heading on

Initially I was just using the select to screen. This was leaving a blank line between each output line. So I added a lot of code here to use dbms_output.put_line.

Line 6: I wanted to leftpad the column names so that the output would be easier to read. Unfortunately dbms_output.put_line seems to trim leading spaces. So the ‘.’|| preserves the indenting. A tab character also works but would indent several more character spaces

Also on Line 6 note the nvl(to_char(&1),’ ‘). This was required so that nulls would show up in the list of values. Without the nvl, listagg would only list the non-null values. The Emp table, comm field is a good illustration of this.

I hope this code benefits someone. Also I am eager to hear on how it can be further improved

Hello world!

My intention is to highlight oracle related content that I find interesting. Also as time allows, I intend to blog on Oracle topics sharing my discoveries. I have had great personal satisfaction in sharing technical insights in the past. I hope that I can continue this by blogging.