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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s