Geek Answers Handbook

How to format SQL text output for SQL queries only

I use Oracle SQL (in SQLDeveloper, so I don’t have access to SQLPLUS commands like COLUMN) to execute a query that looks something like this:

select assigner_staff_id as staff_id, active_flag, assign_date,
  complete_date, mod_date
from work where assigner_staff_id = '2096';

The results that he gave me look something like this:

STAFF_ID ACTIVE_FLAG ASSIGN_DATE COMPLETE_DATE MOD_DATE                  
---------------------- ----------- ----------------- -------- ------------------------- ----------------- -------- 
2096 F 25-SEP-08 27-SEP-08 27-SEP-08 02.27.30.642959000 PM 
2096 F 25-SEP-08 25-SEP-08 25-SEP-08 01.41.02.517321000 AM 

2 rows selected

It can very easily create a very wide and bulky text report when I try to paste the results as a beautifully formatted quick-n-dirty text block into the report by email or problem, etc. What is the best way to get rid of all the extra space in the output columns when I use plain-vanilla Oracle SQL? Until now, all of my searches on the Internet haven’t shown much since all the web search results show me how to do this using formatting commands like COLUMN in SQLPLUS (which I don’t have).

+5
source share
6 answers

? , , , SQL * PLUS. , , , .

Oracle , - .

, , , substr(), .

select substr(assigner_staff_id, 8) as staff_id, 
      active_flag as Flag, 
      to_char(assign_date, 'DD/MM/YY'),
      to_char(complete_date, 'DD/MM/YY'), 
      mod_date
from work where assigner_staff_id = '2096';
+2

, :

select /*csv*/ col1, col2 from table;
select /*Delimited*/ col1, col2 from table;

, xml, html, text, loader ..

"" > "" > " " > "" > ""

Run Script, Run Statement.

* Oracle SQL Developer v3.2

+2

sql, . SQL Plus , .

- excel . .

+1

. .

, , SQL , .

. ( , - ):

select substring( convert(varchar(4), assigner_staff_id), 1, 4 ) as id, 
       active_flag as act, -- use shorter column name

       -- etc. 

from work where assigner_staff_id = '2096';

?
unix/linux, awk script.

, , , , :)

0

, Tom Kytes print_table.

SQL> set serveroutput on 
SQL> execute print_table('select * from all_objects where rownum < 3');
OWNER                         : SYS
OBJECT_NAME                   : /1005bd30_LnkdConstant
SUBOBJECT_NAME                :
OBJECT_ID                     : 27574
DATA_OBJECT_ID                :
OBJECT_TYPE                   : JAVA CLASS
CREATED                       : 22-may-2008 11:41:13
LAST_DDL_TIME                 : 22-may-2008 11:41:13
TIMESTAMP                     : 2008-05-22:11:41:13
STATUS                        : VALID
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N
-----------------
OWNER                         : SYS
OBJECT_NAME                   : /10076b23_OraCustomDatumClosur
SUBOBJECT_NAME                :
OBJECT_ID                     : 22390
DATA_OBJECT_ID                :
OBJECT_TYPE                   : JAVA CLASS
CREATED                       : 22-may-2008 11:38:34
LAST_DDL_TIME                 : 22-may-2008 11:38:34
TIMESTAMP                     : 2008-05-22:11:38:34
STATUS                        : VALID
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N
-----------------

PL/SQL procedure successfully completed.

SQL>

, SQL Developer xls, - - excel.

0

"cast"?

select 
(cast(assigner_staff_id as VARCHAR2(4)) AS STAFF_ID,
(cast(active_flag as VARCHAR2(1))) AS A,
(cast(assign_date as VARCHAR2(10))) AS ASSIGN_DATE,
(cast(COMPLETE_date as VARCHAR2(10))) AS COMPLETE_DATE,
(cast(mod_date as VARCHAR2(10))) AS MOD_DATE
from work where assigner_staff_id = '2096';
0

More articles:


All Articles