How to run an oracle query in linux with a table like output

Question

I'm totally new in running sql queries in linux and I'm having a hard time dealing with it's output.

So I managed to access my database in oracle in linux and trying to run a simple query right now:

SELECT IN_01, OUT_BD_01 FROM TRANSLATION_ROW WHERE IN_01 = 'LS3K5GB';

I'm expecting it to be in a table-like output but instead i got this:

Any Help would be much appreciated. By the way, I'm accessing my Oracle server through putty. I don't know if that helps in anything.

--forgot to mention that I also use sqlplus. Don't know if that would make any difference

Thanks in advance.

Your values are wrapping, that is all. This is a table format; the first column is just very long. — Gordon Linoff
– Gordon Linoff, Commented Oct 30, 2015 at 10:47
Duplicate of how to display table data more clearly, but that question has lost it's picture of the wrapped output... — Alex Poole
– Alex Poole, Commented Oct 30, 2015 at 10:54

HoldOffHunger · Accepted Answer · 2020-10-23 16:41:44Z

1

Welcome to the weird and wonderful world of Oracle.

Viewing large amounts of data (especially "wide" data) through sqlplus has always been less than pretty. Even back in the 1990s Oracle rival Ingres had a rather nice isql which made a much better fist of this, although the flipside of that was using isql to spool to a data file (no headers and trimmings, etc) was slightly harder. I think the rather primitive nature of SQLPLus is why TOAD/SQL*Developer etc have become popular.

To make the output easier to read, you need to learn the basics of sqlplus formatting, in particular SET LINES, PAGES, TRIMSPOOL, TAB, and the COLUMN formatting command.

Use COLUMN to control the formatting of each column.

One possible option is to use SET MARKUP and spool to a file, which formats the output as HTML table, but then you need a HTML viewer/browser to view the results.

On PuTTY your options are limited, but if you have xterm and can invoke the browser on Linux, you might find something like a shell script:

#!/bin/bash

sqlplus un/pw @the_file
firefox the_output.html

Contents of the_file.sql:

SET MARKUP ON
spool the_output.html
SELECT * FROM user_objects;
spool off
quit

If you have a share between the Linux system where the the_output.html resides and can mount that on WIndows, you could run the query on Linux with MARKUP oN, spool to the share, then click refresh on the Browser.

Clunky, and not really what you want, but try it and see what you get.

edited Oct 23, 2020 at 16:41

HoldOffHunger

21.3k11 gold badges123 silver badges147 bronze badges

answered Oct 31, 2015 at 14:23

TenG

4,0343 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Uzi Over a year ago

thanks for this. guess I'll try to check this out later on.

Giggs · Accepted Answer · 2015-10-30 10:51:16Z

0

It displays the entire column that's it. You can format your column before running the query with the below:

e.g.: format my column to display 10 characters only

column IN_01 format a10

answered Oct 30, 2015 at 10:51

Giggs

774 bronze badges

Comments

Gergely Bacso · Accepted Answer · 2015-10-30 10:55:15Z

0

There are some basic configuration tricks that you should apply when using SQLplus. A basic set of parameters would be something like this:

set pagesize 50000
set linesize 135
set long 50000
set trimspool on
set tab off

All these should be placed in a login.sql file which should be in the directory you are launching sqlplus from.

This will solve your current problem, but for further reading I suggest checking out this page: Configuring sqlplus.

answered Oct 30, 2015 at 10:55

Gergely Bacso

14.7k2 gold badges46 silver badges65 bronze badges

Collectives™ on Stack Overflow

How to run an oracle query in linux with a table like output

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related