This content has been marked as final. Show 3 replies
William who is also a regular here on the forum has a nice article about setting up sql*plus: http://www.williamrobertson.net/documents/sqlplus_setup.html
I suggest not to use sql*plus, but to use oracle sql developer. It is free and has a modern look and feel. However sometimes there is still the need to do something in sql*plus. So there is no harm learning more about the tool. E.g. when using it to spool data to a file, etc.
To increase how text is printed I usually set linesize AND longchuck size to a higher value.
Seems to work for me at least.
The size of a column depends on the possible size not upon the actual data that is in it.
set linesize 400 set longc 400 set pagesize 100
So a varchar2(30) column will be rendern with 30 blank chars if there is nothing in it.
A Varchar2(300) column will have 300 blanks. This really messes up the screen.
An option is to format the column for a query in a way you want it.
The syntax would be
This would format any column "USER_NAME" with a size of 10 alphanumerical chars. Doc: http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve013.htm#SQPUG034
col user_name format "A10"
Edited by: Sven W. on Oct 11, 2012 7:58 PM
Edited by: Sven W. on Oct 11, 2012 7:59 PM
Edited by: Sven W. on Oct 11, 2012 8:05 PM -- added doc link for column command
You can control how much horizontal space SQL*Plus uses to display each column using the `COLUMN` command, i.e.
This will allocate 20 spaces for users_name and users_email, 10 spaces for users_dob, and display up to 8 digits of users_id. Of course, this may force the data on a particular row to wrap around to the next row if it exceeds whatever value you specified.
SQL> column users_id format 99999999 SQL> column users_name format a20 SQL> column users_email format a20 SQL> column users_dob format a10
In general, though, it is probably easier to use SQL Developer if you are just trying to interactively run some queries. A GUI like SQL Developer is generally going to present the data in a much more human-friendly format with much less work on your part to adjust the formatting.
CarlosinFL wrote:What you are seeing is simple line wrap. It's caused by either or both of
Learning how Oracle works has been a huge feat for me but now I'm getting frustrated with using SQL*Plus. Currently I have no experience with SQL*Plus and I have read the documentation:
My question is I'm looking for advice and or tips on how to set up SQL*Plus to render data I retrieve with the SELECT statement more coherently. I'm using 220.127.116.11 and from a default installation, it doesn't seem to have a rhyme or reason for how it formats the text on my SQL*Plus client. It's just a mess and extremely hard for me to read or understand. For example:
As you can see the coorisponding data doesn't flow underneath the column heading for whatever reason and it makes this hard to read once tables get larger in size. I was told to do the following:
SQL> SELECT * 2 FROM CMENNENS.USERS 3 WHERE USERS_NAME = 'CARLOS MENNENS' 4 ; USERS_ID USERS_NAME ---------- -------------------------------------------------- USERS_EMAIL -------------------------------------------------------------------------------- USERS_DOB --------- 1000 CARLOS MENNENS CARLOS@TEST.TLD
The above helped a little but it still looks weird or just not formatted as I expect it to. I really am no expect obviously so reading the docs on something like this is hard. It's not like I want to accomplish 'X' so I read the docs and configure the system to do 'X'. I have no idea what 90% of those functions in the doc ID listed actually will do or break and or their default values. Does anyone know exactly what I should change or look at in order to get some thing that looks as follows:
SQL> set linesize 32765; SQL> set pagesize 50;
zoo=# SELECT * zoo-# FROM users zoo-# WHERE users_name LIKE 'Carlos%' zoo-# ORDER BY users_id, users_name zoo-# ; users_id | users_name | users_email | users_dob ----------+----------------+-----------------+------------ 1001 | Carlos Mennens | firstname.lastname@example.org | 1979-05-25 (1 row)
1) the LINESIZE setting in sqlpus
2) the dimensions of the terminal/window in which you are running sqlplus.
As others have pointed out, you could use sql developer. I'm a bit of a a dinosaur in that I still use sqlplus for most stuff. The nice thing about that is it is not trying to be smarter than you. You get exactly what you ask for. I handle this line wrap issue in two ways:
1) use of the COLUMN ... FORMAT, as others have alluded to. If you have a column that is much wider than the typical data in it, you can really shorten it up. For instance, when querying dba_tables, OWNER is 30 bytes, but I seldom have an OWNER value actually over 10, so "COL OWNER FORMAT A10" shortens that column to 10 characters in my output. Anything that is longer, and the individual column will wrap, not the entire line.
2) always spool my output then view the spooled file. All of my ad-hoc sql files look like this
then within sqlplus I simply
-- file: doit.sql set trimspool on set linesize 512 set pages 90 spool doit.log -- -- put my query here -- spool off edit doit.log
The query runs and the result immediately pops up in the system default editor. This works a bit better on Windows where the editor is notepad. That gives me a scalable and scrollable window with no enforced line wrap. Just be aware that while you are looking at the spooled file, sqlplus is still processing your script, waiting for you to exit the editor so it (sqlplus) can process the next line of the script. If you actually edit and save the script before you exit the spool file, you can cause sqlplus to find a 'next' line when you didn't intend for it to.