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