Good Evening,

I finally see the that there is a way to set sqlplus to present a more readable approach at presenting the results. I saw this was output by postinstall.sql after apply a OJVM PSU. It output the results like this:

SQL> select * from registry$history order by id;

22-MAR-16 04.56.19.770164 PM
APPLY SERVER
11.2.0.4 8
PSU 11.2.0.4.8
PSU

02-JUN-16 06.07.15.527617 PM
APPLY SERVER
11.2.0.4 160419
PSU 11.2.0.4.160419
PSU

02-JUN-16 06.17.36.254981 PM
APPLY
22674697
Patch 22674697 applied

3 rows selected.


When I query the same view, I get this spaced out version:
select * from registry$history order by id;

ACTION_TIME ACTION NAMESPACE VERSION ID
--------------------------------------------------------------------------- ------------------------------ ------------- ----------------- ------------------------------ ----------
COMMENTS
------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------
BUNDLE_SERIES
------------------------------
01-APR-16 11.17.01.841570 AM APPLY SERVER 11.2.0.4 8
PSU 11.2.0.4.8
PSU

02-JUN-16 06.07.33.375473 PM APPLY SERVER 11.2.0.4 160419
PSU 11.2.0.4.160419
PSU

02-JUN-16 06.18.00.923581 PM APPLY 22674697
Patch 22674697 applied

Any idea how to reproduce the postinstall.sql output? I think that the biggest problem is that some columns are insanely large. Is there a way I can set my sqlplus environment to not show so much of the large columns, without having to set "column format ..." or "substr(column) for all of the columns?

I looked at the script and extracted all of the settings that I could find, even if I set that, I still don't get the same condensed output. The best I can get is this:

set verify off
set feedback off
SET PAGESIZE 10
set head off


SQL> select * from registry$history order by id;

01-APR-16 11.17.01.841570 AM APPLY SERVER 11.2.0.4 8
PSU 11.2.0.4.8
PSU

02-JUN-16 06.07.33.375473 PM APPLY SERVER 11.2.0.4 160419
PSU 11.2.0.4.160419
PSU

02-JUN-16 06.18.00.923581 PM APPLY 22674697
Patch 22674697 applied


Here is the script:
cat postinstall.sql
rem
rem Name: postinstall.sql
rem
rem Bug: 22674697
rem

VARIABLE rdbmsLogDir VARCHAR2(500);
VARIABLE sqlLogDir VARCHAR2(500);
VARIABLE createDirCmd VARCHAR2(600);
VARIABLE kolfuseslf VARCHAR2(50);

REM Init the variable to its default value
BEGIN :kolfuseslf := 'FALSE'; END;
/

-- Returns TRUE if the specified directory exists and is writable.
-- If there are any errors encountered while opening the directory and writing
-- a test file (using utl_file) then false is returned.
-- Note that only one session should call this at a time because the directory
-- is not session specific.
-- Added for bug 17343514.
CREATE OR REPLACE FUNCTION dir_exists_and_is_writable(dirname IN VARCHAR2)
RETURN BOOLEAN
IS
PRAGMA AUTONOMOUS_TRANSACTION; -- executes DDL

fp UTL_FILE.FILE_TYPE;

BEGIN
-- Try to create directory object
BEGIN
EXECUTE IMMEDIATE
'CREATE OR REPLACE DIRECTORY dbms_registry_testdir AS ' ||
dbms_assert.enquote_literal(dirname);
EXCEPTION
WHEN OTHERS THEN
-- We want to just quit here since the directory object can't be created
RETURN FALSE;
END;

-- Attempt to open a file, close it, and then delete it.
fp := UTL_FILE.FOPEN('DBMS_REGISTRY_TESTDIR',
'dbms_registry_testfile.txt', 'a');
UTL_FILE.FCLOSE(fp);
UTL_FILE.FREMOVE('DBMS_REGISTRY_TESTDIR', 'dbms_registry_testfile.txt');

EXECUTE IMMEDIATE 'DROP DIRECTORY dbms_registry_testdir';

-- If we get here we're good
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
-- Try to drop the directory first
BEGIN
EXECUTE IMMEDIATE 'DROP DIRECTORY dbms_registry_testdir';
EXCEPTION
WHEN OTHERS THEN null;
END;

-- And return FALSE. We don't care what the error was.
RETURN FALSE;
END dir_exists_and_is_writable;
/

REM Determine the names of interesting directories and create them if
REM necessary.

DECLARE
platform v$database.platform_name%TYPE;
homeDir VARCHAR2(500);
baseDir VARCHAR2(500);
useDir VARCHAR2(500);

BEGIN
-- Determine ORACLE_HOME value and admin dir
SELECT NLS_UPPER(platform_name)
INTO platform
FROM v$database;

DBMS_SYSTEM.GET_ENV('ORACLE_BASE', baseDir);
DBMS_SYSTEM.GET_ENV('ORACLE_HOME', homeDir);

IF baseDir IS NOT NULL THEN
useDir := baseDir;
ELSE
useDir := homeDir;
END IF;

IF INSTR(platform, 'WINDOWS') != 0 THEN
-- Windows, use '\'
useDir := RTRIM(useDir, '\'); -- Remove any trailing slashes
:sqlLogDir := useDir || '\cfgtoollogs\postinstall\';
:createDirCmd := 'mkdir ' || :sqlLogDir;
:rdbmsLogDir := homeDir || '\rdbms\log\';
ELSIF INSTR(platform, 'VMS') != 0 THEN
-- VMS, use [] and .
:sqlLogDir := REPLACE(useDir || '[cfgtoollogs.postinstall]', '][', '.');
:createDirCmd := 'CREATE/DIR ' || :sqlLogDir;
:rdbmsLogDir := REPLACE(homeDir || '[rdbms.log]', '][', '.');
ELSE
-- Unix and z/OS, '/'
useDir := RTRIM(useDir, '/'); -- Remove any trailing slashes
:sqlLogDir := useDir || '/cfgtoollogs/postinstall/';
:createDirCmd := 'mkdir -p ' || :sqlLogDir;
:rdbmsLogDir := homeDir || '/rdbms/log/';
END IF;

IF dir_exists_and_is_writable(:sqlLogDir) THEN
-- Log directory already exists, we don't need to create it
:createDirCmd := 'exit';
END IF;

EXCEPTION
WHEN OTHERS THEN
null;
END;
/
set verify off
set feedback off

COLUMN create_cmd NEW_VALUE create_cmd NOPRINT
SELECT :createDirCmd AS create_cmd FROM dual;
HOST &create_cmd

REM Turn spooling on
COLUMN postinstall_logfile NEW_VALUE postinstall_logfile NOPRINT
SELECT NULL AS postinstall_logfile FROM DUAL WHERE 1 = 0;
SELECT NVL( q'{&postinstall_logfile}',
:sqlLogDir || 'postinstall_' || name ||
TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss',
'NLS_DATE_LANGUAGE=''AMERICAN''') ||
'.log' )
AS postinstall_logfile
FROM v$database;

set verify on
set feedback on

SPOOL &postinstall_logfile APPEND
PROMPT Check &postinstall_logfile for errors

rem Create registry$history

ALTER SESSION SET CURRENT_SCHEMA = SYS;

declare
lv_create_stmt varchar2(1000) := 'create table registry$history (action_time timestamp(6), action varchar2(30), namespace varchar2(30), version varchar2(30), id number, comments varchar2(255), bundle_series varchar2(30))';
begin
execute immediate lv_create_stmt;
exception
when others then
null;
end;
/



ALTER SESSION SET CURRENT_SCHEMA = SYS;

SET PAGESIZE 0
SELECT 'Calling sqlpatch/22674697/prereqcheck.sql on ' || SYSTIMESTAMP FROM dual;
SET PAGESIZE 10

@?/sqlpatch/22674697/prereqcheck.sql

SET PAGESIZE 0
SELECT 'Calling javavm/install/jvmpsupi.sql on ' || SYSTIMESTAMP FROM dual;
SET PAGESIZE 10

@?/javavm/install/jvmpsupi.sql


rem Update registry$history

ALTER SESSION SET CURRENT_SCHEMA = SYS;
declare
lv_insert_stmt varchar2(1000) := 'insert into registry$history (action_time, action, id, comments) values (systimestamp, ''APPLY'', 22674697, ''Patch 22674697 applied'')';
begin
execute immediate lv_insert_stmt;
exception
when others then
null;
end;
/
commit;
set head off
select * from registry$history order by id;

=============================================================
.

-- Notes:   This script is based on Tom Kyte's original printtbl code ( http://asktom.oracle.com )
--          For coding simplicity (read: lazyness) I'm using custom quotation marks ( q'\ ) so 
--          this script works only from Oracle 10gR2 onwards

def _pr_tmpfile=c:\tmp\pr.out

set termout off
store set &_pr_tmpfile.set replace
set termout on

set serverout on size 1000000 termout off
save &_pr_tmpfile replace
set termout on

0 c clob := q'\
0 declare

999999      \';;
999999      l_theCursor     integer default dbms_sql.open_cursor;;
999999      l_columnValue   varchar2(4000);;
999999      l_status        integer;;
999999      l_descTbl       dbms_sql.desc_tab;;
999999      l_colCnt        number;;
999999  begin
999999      dbms_sql.parse(  l_theCursor, c, dbms_sql.native );;
999999      dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );;
999999      for i in 1 .. l_colCnt loop
999999          dbms_sql.define_column( l_theCursor, i,
999999                                  l_columnValue, 4000 );;
999999      end loop;;
999999      l_status := dbms_sql.execute(l_theCursor);;
999999      while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
999999          dbms_output.put_line( '==============================' );;
999999          for i in 1 .. l_colCnt loop
999999                  dbms_sql.column_value( l_theCursor, i,
999999                                         l_columnValue );;
999999                  dbms_output.put_line
999999                      ( rpad( l_descTbl(i).col_name,
999999                        30 ) || ': ' || l_columnValue );;
999999          end loop;;
999999      end loop;;
999999  exception
999999      when others then
999999          dbms_output.put_line(dbms_utility.format_error_backtrace);;
999999          raise;;
999999 end;;
/

set termout off
@&_pr_tmpfile.set
set termout on

get &_pr_tmpfile nolist
host del &_pr_tmpfile 


=============================================================

SQL> select * from tab
  2  @pr

==============================
TNAME                         : A
TABTYPE                       : TABLE
CLUSTERID                     :
==============================
TNAME                         : ACCOUNTS
TABTYPE                       : TABLE
CLUSTERID                     :
==============================
TNAME                         : ACCOUNT_EXTRACT
TABTYPE                       : TABLE
CLUSTERID                     :
==============================
TNAME                         : ADG
TABTYPE                       : TABLE
CLUSTERID                     :
==============================
TNAME                         : AMOUNT_NUMBER
TABTYPE                       : TABLE
CLUSTERID                     :
==============================
TNAME                         : AUDIT_TABLE
TABTYPE                       : TABLE
CLUSTERID                     :
==============================
TNAME                         : userdetails627_TAB
TABTYPE                       : TABLE
CLUSTERID                     :

PL/SQL procedure successfully completed.


SQL> l
  1* select * from tab
SQL>