CODEWITHSUNDEEP
sqllinuxoracle-database
Uzi
Uzi

Reputation: 453

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

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:

enter image description here

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.

Upvotes: 0

Views: 3077

Answers (3)

TenG
TenG

Reputation: 4004

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.

Upvotes: 1

Gergely Bacso
Gergely Bacso

Reputation: 14651

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.

Upvotes: 0

Giggs
Giggs

Reputation: 77

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

Upvotes: 0

Related Questions