I'm working with SQLPlus at the moment and whenever I query the Database, the result is in a complete mess. In other words, it is hard to read. I wonder if anyone knows how to format the output of queries ( columns, tables.. etc) in SQLPlus that is running on Unix server. But, I am accessing the server from my windows.
And, Could anyone tell me where I can get SQLPlus for Ubuntu from?
Cheers,
Okay, start with this in SQL Plus:
SET LINESIZE 20000 TRIM ON TRIMSPOOL ON
SPOOL output.txt
-- run your queries here
SPOOL OFF
EXIT
Also, René Nyffenegger has a whole section dedicated to Beautifying SQL*Plus Output and additional resources on SQL*Plus.
The biggest culprits for messy sql output are long character columns, i.e. varchar2(360). sqlplus allocates exactly that much space for the output, even though you may only be using 20 at most. You can change this by using the column directive. Assume that employees table has an address column with 360 characters:
column address format A20
select address from employees;--sqlplus will allocate only 20 characters for the address width
Given that I work with a database with lots of (unnecessarily) long character columns this works perfectly for me.
The answer from Benoit is what you are looking for. To install SQLPlus in Ubuntu, just download the compressed files from Oracle (otn) and follow the instructions.
Your result is in a complete mess because of two things:
You are using a shell
You screen is small thus output lines are wrapped.
What to do?
Since it is oracle, forget about ubuntu/windows. Download Oracle SQL Developer or Aqua data studio.
They are easy to use, readable output format, and savable sql scripts. It will save you time reading/analyzing query outputs.
