Home » SQL & PL/SQL » SQL & PL/SQL » Leading space when spooling to text file
Leading space when spooling to text file [message #222285] Fri, 02 March 2007 12:01 Go to next message
ORA_USR
Messages: 1
Registered: February 2006
Junior Member
I have an Oracle script which reads data from a table and spools the output to a text file, creating one line in the file for each record in the table.
My problem is this - When the text file is created, each line starts with a leading space, that is, the data starts from Column 2 instead of column 1.

Below are the initial parameters I am setting in this script.

SET TERMOUT OFF;
SET VERIFY OFF;
SET HEADING OFF;
SET FEEDBACK OFF;
SET LINESIZE 500;
SET PAGESIZE 0;

SPOOL &1;

Am I missing out on something here?
Re: Leading space when spooling to text file [message #222292 is a reply to message #222285] Fri, 02 March 2007 12:37 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
try with
trimspool on
Re: Leading space when spooling to text file [message #222651 is a reply to message #222285] Mon, 05 March 2007 15:43 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
trimspool will remove spaces from the end of the line. Please paste your query and some sample data.
Re: Leading space when spooling to text file [message #222724 is a reply to message #222651] Tue, 06 March 2007 03:15 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If the first column is a number, then you probably need to add a 'FM' into the format mask, as by default a number mask leaves a leading space so that negative and positive numbers will format the same.
Eg:
SQL> select to_char(num,'0000') from (select level num from dual connect by level <= 5);

TO_CH
-----
 0001
 0002
 0003
 0004
 0005

SQL> select to_char(num,'fm0000') from (select level num from dual connect by level <= 5);

TO_CH
-----
0001
0002
0003
0004
0005
Previous Topic: How to return recordset from procedure
Next Topic: stored procedure
Goto Forum:
  


Current Time: Sun Dec 04 19:04:19 CST 2016

Total time taken to generate the page: 0.07759 seconds