How to align SQL Query result?

Question

I tried to find a solution, but it seems it hasn't been asked (if it has please give me an URL)

When I try to query for example

select name, value from V$PARAMETER;

I get this messy result

I paste an image because pasting the code wasn't working pretty well

WHat are you even asking? See here -https://dba.stackexchange.com/questions/54149/how-to-make-sqlplus-output-appear-in-one-line — OldProgrammer, Mar 21 '19 at 23:37
sorry if it's not clear, what I'm trying to ask is to how to make the 2 values (name and value) go in just 1 row, as it currently is in 2 just as the values, is there a way to show it aligned properly? — Newbee4Ever, Mar 21 '19 at 23:55
Your question is really about features of the program you are using to interact with the database, not the database itself. What are you using - SQL\*Plus? Toad? SQL Developer? Something else? Each has its settings for formatting query output. — , Mar 22 '19 at 01:29
Note, however, that (on my system at least) the longest `name` in the `v$parameter` view is 43 characters long, and the longest `value` is 102 characters long. So if you want every row to be one line, you need lines of length 146 at least (to allow a space between the columns). Will that be OK? — , Mar 22 '19 at 01:31
It has been asked, quite often; [here's one example](https://stackoverflow.com/q/3006431/266304) but there are lots of others, — Alex Poole, Mar 22 '19 at 09:24

score 2 · Answer 1 · answered Mar 22 '19 at 07:40

If you are using sqlcl, you could use "set sqlformat ansiconsole", or "format aXX" eg

SQLcl: Release 4.2.0.16.153.2014 RC 

SQL> set sqlformat ansiconsole
SQL> select name, value from V$PARAMETER;
NAME                          VALUE                                                        
lock_name_space               NULL                                                         
timed_statistics              TRUE                                                         
timed_os_statistics           0                                                            
resource_limit                TRUE                                                         
license_max_sessions          0                                                            
license_sessions_warning      0                                                            
cpu_count                     2                                                            
instance_groups               NULL                                                         
event                         NULL                                                         
sga_max_size                  838860800                                                    
use_large_pages               TRUE                                                         
pre_page_sga                  TRUE 

SQL> set sqlformat default
SQL Format Cleared


SQL> col name format a40
SQL> col value format a40
SQL> select name, value from V$PARAMETER;

NAME                                     VALUE                                   
---------------------------------------- ----------------------------------------
lock_name_space                          NULL                                    
processes                                300                                     
sessions                                 472                                     
timed_statistics                         TRUE                                    
timed_os_statistics                      0                                       
resource_limit                           TRUE                                    
license_max_sessions                     0                                       
license_sessions_warning                 0                                       
cpu_count                                2                                       
instance_groups                          NULL                                    
event                                    NULL                                    
sga_max_size                             838860800 
...

See also: documentation (SQLplus, formatting columns), sqlcl examples here. SQL*Plus-style formatting seems to be working with sqlcl, too.

How to align SQL Query result?

1 Answers1