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
Sean's Oracle Share 