Thursday, June 23, 2011

beautify sqlplus output

when runing following code in sql plus,we get a very messay output because of column data type and it's width;
--var rc refcursor
--exec tmp_pkg.get_cmts_bychassis_cursor(14,:rc)
--print rc

here is the output:
CMTS_SEQ
----------
CMTS_NAME
--------------------------------------------------------------------------------
CHASSIS_SEQ
-----------
CHASSIS_NAME
--------------------------------------------------------------------------------
CMTS_TYPE_NAME
--------------------------------------------------------------------------------
MODEL
--------------------------------------------------------------------------------
14

CMTS_SEQ
----------
CMTS_NAME
--------------------------------------------------------------------------------
CHASSIS_SEQ
-----------
CHASSIS_NAME
--------------------------------------------------------------------------------
CMTS_TYPE_NAME
--------------------------------------------------------------------------------
MODEL
--------------------------------------------------------------------------------
cmts11.nmkt

CMTS_SEQ
----------
CMTS_NAME
--------------------------------------------------------------------------------
CHASSIS_SEQ
-----------
CHASSIS_NAME
--------------------------------------------------------------------------------
CMTS_TYPE_NAME
--------------------------------------------------------------------------------
MODEL
--------------------------------------------------------------------------------
14

CMTS_SEQ
----------
CMTS_NAME
--------------------------------------------------------------------------------
CHASSIS_SEQ
-----------
CHASSIS_NAME
--------------------------------------------------------------------------------
CMTS_TYPE_NAME
--------------------------------------------------------------------------------
MODEL
--------------------------------------------------------------------------------
cmts11.nmkt

CMTS_SEQ
----------
CMTS_NAME
--------------------------------------------------------------------------------
CHASSIS_SEQ
-----------
CHASSIS_NAME
--------------------------------------------------------------------------------
CMTS_TYPE_NAME
--------------------------------------------------------------------------------
MODEL
--------------------------------------------------------------------------------
i-cmts

CMTS_SEQ
----------
CMTS_NAME
--------------------------------------------------------------------------------
CHASSIS_SEQ
-----------
CHASSIS_NAME
--------------------------------------------------------------------------------
CMTS_TYPE_NAME
--------------------------------------------------------------------------------
MODEL
--------------------------------------------------------------------------------
ubr10012

CMTS_SEQ
----------
CMTS_NAME
--------------------------------------------------------------------------------
CHASSIS_SEQ
-----------
CHASSIS_NAME
--------------------------------------------------------------------------------
CMTS_TYPE_NAME
--------------------------------------------------------------------------------
MODEL
--------------------------------------------------------------------------------
we need make it tide and neat:
first format those columns that are too long
column CMTS_SEQ format 999
column CMTS_NAME format a12
...
then rerun the code and print cursor
we will get follwing output

SQL> print rc

CMTS_SEQ CMTS_NAME CHASSIS_SEQ CHASSIS_NAME CMTS_TYPE_NAME MODEL
-------- ------------ ----------- ------------ ------------ ------------
14 cmts11.nmkt 14 cmts11.nmkt i-cmts ubr10012

cheers!

No comments:

Post a Comment