cancel
Showing results for 
Search instead for 
Did you mean: 

Ordering Output of a select

Sirius Black
Regular Advisor

Ordering Output of a select

Hi all,
I'm doing a query via SQLPLUS on my Oracle
db and I re-direct the output to a file.
The output is full of blank and tab charset
how can I obtain an human output from my select ??
Thanks
11 REPLIES
Muthukumar_5
Honored Contributor

Re: Ordering Output of a select

You have to check for NULL condition with select query. Few columns may be allowed to use "" or NULL so that select is sending and to output file.

Or else you can write a script to remove spaces, tabs and make human readable format as your own.

Post select query and output file to give script.

~regards
Easy to suggest when don't know about the problem!
Piergiacomo Perini
Trusted Contributor

Re: Ordering Output of a select

Hi,
you can start from this set of lines:

SET NEWPAGE 0
SET LINESIZE 10
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF

column FIRST format a4
column SECOND format a5

select FIRST, SECOND from TABLE;


Hope that help
regards
Yogeeraj_1
Honored Contributor

Re: Ordering Output of a select

hi,

did you run "spool filename.lis" before running your sql statement?

if yes, can you confirm if you get the output on the screen when running the select statement without the spool command?

revert

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Sirius Black
Regular Advisor

Re: Ordering Output of a select

For Piergiacomo:
I try to run it with your set statement but unfortunatelly they've not the effect that we want.
I run before the select statement the spool whit the file and in the spool file I've the same output with blank and tab charset..

This is one row of my output

XXXXX AAA BBB

XXXXX

ACCUMALE

PIAZZA SAN
DAMIANO, 1

XX-XXXXX

RO

XX.XXX.XXX.XX
FR

ROU
TER

XXXXXX XX_XX_XXXXXXXXX:XXXXXX XXX.XX.XXX.X ACCUMALE

FALSO
XX.XXX.XXX.XXX FALSO N

Thanks
Fabrizio
Patti Johnson
Respected Contributor

Re: Ordering Output of a select

SqlPlus was never meant as a reporting tool, therefore to get the output you want you may have to redefine column formats, etc as suggested by others.
Another option may be using iSqlplus which display the output in a more readable grid format.

Or you could try
SET MARKUP HTML ON SPOOL ON
from sqlplus and output your data in html.

If you post your sql query and table definitions it may help to resolve your problem.
Patti
Piergiacomo Perini
Trusted Contributor

Re: Ordering Output of a select

Hi Alleva,
i beg your pardon but
i get some mess with my "SET" suggestions!
(as you posted ;-))
As Patti said, if you want use Sqlplus u
have to "re-draw" output with
SET and COLUMN
(for example :
column xxxx format a4
column yyyyy format a5,
to put data in 4 char. or in 5 char. space
column);
better, as Patti wrote, try with "SET
MARKUP HTML" (but again blanks could
find place in your output file).

regards
Sirius Black
Regular Advisor

Re: Ordering Output of a select

Ok,
HTML Is a nice output there's a way to do it
in XML ??
Thanks a lot
Fabrizio
Sandman!
Honored Contributor

Re: Ordering Output of a select

In SQL*Plus set two variables for removing blanks and tabs from your spooled file...

SQL> set tab off
SQL> set trimspool on
Yogeeraj_1
Honored Contributor

Re: Ordering Output of a select

hi fabrizio,

can you try the attached function that will allow you to dump your data in cvs format?

you can thus do the following:

create or replace procedure table_dump_csv
as
l_rows number;
begin
l_rows := dump_csv( 'select *
from your_table
where ...,
',', '/tmp', 'test.dat' );
end;
/

(assumes that the INIT.ORA parameter utl_file_dir is already set)

hope this helps!

regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Ordering Output of a select

hi again,

You can also try using the Oracle XML
Developer's Kits (XDK's).

With the XDK, you can generate XML from SQL queries using PL/SQL, Java, C or C++. The following command on the command line generates an XML document containing the first 4 rows of the SCOTT.EMP table:
$ java OracleXML getXML -user "scott/tiger" "select * from emp where rownum < 5"
> emp.xml

hope this helps too!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Arturo Galbiati
Esteemed Contributor

Re: Ordering Output of a select

Hi,
try this:
set pages 0 trims on tab off lines 80 feed off

take care to set correctly the linesize (i put as default 80)

HTH,
Art