Eliminate spaces in spooled text file

[dkwong]

I can successfully spool query results to a text file. I want each column to be a fixed length (as shown below) and the fields CANNOT be separated by any spaces. I've tried many suggestions, but I can't get rid of the extra padding. Thanks.

SET ECHO      OFF
SET HEADING   OFF
SET PAGESIZE    0
SET NEWPAGE     0
SET TRIMSPOOL  ON
SET SPACE       0
SET LINESIZE  115
SET FEEDBACK  OFF
SET TERMOUT   OFF

SPOOL c:/spooltest.txt

COLUMN PR_NUMBER_TX   	FORMAT A9
COLUMN LINE_ITEM      	FORMAT A2
COLUMN RECORD_SEQ     	FORMAT A2
COLUMN PR_CORP_CODE_TX 	FORMAT A5
COLUMN PR_DATE_DT	FORMAT A6

SELECT pr.pr_number_tx,
       ' 0' line_item,
       ' 0' record_seq,
       pr.pr_corp_code_tx,
       pr.pr_date_dt
FROM   purchase_requisition pr
ORDER
BY     pr.pr_number_tx;

SPOOL OFF

EXIT

 

[sem]

SET COLSEP ''

 

[dkwong]

Thanks, but that setting does not change anything. Am I missing something?

 

[Turkbear]

If the COLSEP '' does not give you the results you want try:

select rpad(PR_NUMBER_TX,9,' ')||rpad(LINE_ITEM,2,' ')||..

In other words concatenate each field with the returned value padded to the fixed length - there will be spaces, however, if any of the data is not the same length as the format, but each field will start at the same position for every record.

 

[dkwong]

Thanks, TurkBear. I forgot to mention that I want to run the spooling in PL/SQL, not SQLPlus. RPAD works in PL/SQL.

 

[sem]

You can not use ANY of SET statements given above in pl/sql, because the're sql*plus specific (as well as my advice).
In pl/sql you should [l|r]trim or substr or [l|r]pad to get fixed width character strings and explicit format masks for dates and numbers. Then you may concatenate the values.