All geek questions in one place

Formatting query output in SQLPlus

I am working with SQLPlus at the moment, and whenever I query the database, the result is completely messed up. In other words, it is difficult to read. I wonder if anyone knows how to format the query output (columns, tables, etc.) in SQLPlus that runs on a Unix server. But I access the server from my windows.

And, can someone tell me where can I get SQLPlus for Ubuntu?

Greetings

+6
source share
4 answers

Ok, start with this in SQL Plus:

SET LINESIZE 20000 TRIM ON TRIMSPOOL ON SPOOL output.txt -- run your queries here SPOOL OFF EXIT

In addition, René Nyffenegger has an entire section on SQL * Plus Output Enhancement and additional resources for SQL * Plus .

+4
source

The biggest culprits of promiscuous SQL output are long columns of characters, i.e. varchar2 (360). sqlplus allocates exactly as much space for output, even if you can use a maximum of 20. You can change this using the column directive. Suppose an employee 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 am working with a database with a large number of (unnecessary) columns with long characters, this works fine for me.

+3
source

Your result is in complete disarray due to two things:
You use shell
The screen is small, so the output lines are wrapped.

What to do?
Since this is an 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. This will save you time on reading / analyzing query outputs.

0
source

The answer from Benoit is what you are looking for. To install SQLPlus on Ubuntu, just download the compressed files from Oracle (otn) and follow the instructions.

0
source

Source: https://habr.com/ru/post/899203/

More articles:


All Articles