SQL*PLUS COLUMN FORMAT
I was told by client to pull out a report from one of database. But we were not allowed to use GUI tools like sql developer.. etc rather sqlplus. As we already know sqlplus commandline needs manual formatting for columns .Its odd sometimes,when we do lot of manual work, isnt it? :j
Execute the script before you run any query or report and it works for most of the queries.
All the dynamic and static view columns were pulled out from all_tab_columns
For your ease!
Filename: colformat.sql
!rm -rf /home/oracle/COLX.sql
!rm -rf /home/oracle/COLY.sql
set serveroutput on
create or replace procedure COLX as
begin
for x in (
select column_name from
all_tab_columns where data_type <> 'NUMBER')
loop
DBMS_OUTPUT.PUT_LINE('column ' || x.column_name || ' format a15 justify left' );
end loop;
end;
/
create or replace procedure COLY as
begin
for y in (
select column_name from
all_tab_columns where data_type='NUMBER')
loop
DBMS_OUTPUT.PUT_LINE('column ' || y.column_name || ' format 999999999 justify center' );
end loop;
end;
/
set heading off
set termout off
set echo off
spool COLX.sql
exec COLX
spool off
spool COLY.sql
exec COLY
spool off
set lines 500
set pages 1000
set numwidth 50
set long 1000000000
set heading on
@COLX.sql
@COLY.sql