topic Re: Ordering Output of a select in Operating System - HP-UX

Ordering Output of a select

Sirius Black — Fri, 04 Nov 2005 06:23:03 GMT

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

Re: Ordering Output of a select

Muthukumar_5 — Fri, 04 Nov 2005 06:30:54 GMT

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

Re: Ordering Output of a select

Piergiacomo Perini — Fri, 04 Nov 2005 06:47:38 GMT

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

Re: Ordering Output of a select

Yogeeraj_1 — Fri, 04 Nov 2005 06:53:24 GMT

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

Re: Ordering Output of a select

Sirius Black — Fri, 04 Nov 2005 08:24:39 GMT

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

Re: Ordering Output of a select

Patti Johnson — Fri, 04 Nov 2005 08:53:24 GMT

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

Re: Ordering Output of a select

Piergiacomo Perini — Fri, 04 Nov 2005 09:12:30 GMT

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

Re: Ordering Output of a select

Sirius Black — Fri, 04 Nov 2005 10:01:10 GMT

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

Re: Ordering Output of a select

Sandman! — Fri, 04 Nov 2005 17:08:06 GMT

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

SQL> set tab off
SQL> set trimspool on

Re: Ordering Output of a select

Yogeeraj_1 — Fri, 04 Nov 2005 23:58:47 GMT

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

Re: Ordering Output of a select

Yogeeraj_1 — Sat, 05 Nov 2005 00:34:48 GMT

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

Re: Ordering Output of a select

Arturo Galbiati — Mon, 07 Nov 2005 04:46:30 GMT

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