Column Formatting

Post date: 01-Jun-2009 10:10:37

You can create following procedure in your database, and use it to run on all the tables and views to format the output of the sql queries.

PROCEDURE

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

create or replace procedure formatcolumns(tname varchar2,towner varchar2)

is

len_of_cols number;

v_len_of_cols number;

str_len_of_cols varchar2(100);

command varchar2(1000);

cursor c1 is select column_name, data_type from dba_tab_columns

where table_name=tname and owner=towner;

v_column_name varchar2(100);

v_data_type varchar2(100);

begin

open c1;

dbms_output.put_line('set lines 500 pages 500');

dbms_output.put_line('set serveroutput on size 1000000');

loop

fetch c1 into v_column_name, v_data_type;

select 'select max(length('||v_column_name||')) from '||towner||'.'||tname into command from dual;

--dbms_output.put_line('CMD:'||command);

execute immediate command into len_of_cols;

select nvl(len_of_cols,1) into v_len_of_cols from dual;

if v_data_type like '%CHAR%' then

dbms_output.put_line('column '||v_column_name||' format a'||v_len_of_cols);

else

select substr('999999999999999999999999999999',1,v_len_of_cols) into str_len_of_cols from dual;

dbms_output.put_line('column '||v_column_name||' format '||str_len_of_cols);

end if;

exit when c1%notfound;

end loop;

close c1;

end;

/

USAGE

=========

SQL> 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 rdb1 rc1.myrac.com 10.2.0.4.0 01-JUN-09 OPEN YES 1 STARTED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

SQL> execute formatcolumns('V_$INSTANCE','SYS');

set lines 500 pages 500

set serveroutput on size 1000000

column ACTIVE_STATE format a6

column BLOCKED format a2

column INSTANCE_NUMBER format 9

column INSTANCE_NAME format a4

column HOST_NAME format a13

column VERSION format a10

column STARTUP_TIME format 999999999

column STATUS format a4

column PARALLEL format a3

column THREAD# format 9

column ARCHIVER format a7

column LOG_SWITCH_WAIT format a1

column LOGINS format a7

column SHUTDOWN_PENDING format a2

column DATABASE_STATUS format a6

column INSTANCE_ROLE format a16

PL/SQL procedure successfully completed.

SQL> set lines 500 pages 500