I have a simple piece of SQL running against an Oracle EBS table:
SET LINESIZE 1000
SPOOL c:\temp\oracle\test.txt
SET TRIMSPOOL ON
SET TRIMOUT ON
SELECT TRIM(fu.user_name) user_name
, TRIM(fu.user_id) user_id
, TRIM(fu.description) description
, TRIM(fu.email_address) email_address
, TRIM(fu.fax) fax
, TRIM(fu.person_party_id) person_party_id
FROM applsys.fnd_user fu
WHERE user_name = 'ZZZAAAB';
SPOOL OFF
The info for the selected fields:
Name Null? Type
----------------- -------- ----------------------------
USER_ID NOT NULL NUMBER(15)
USER_NAME NOT NULL VARCHAR2(100)
DESCRIPTION VARCHAR2(240)
EMAIL_ADDRESS VARCHAR2(240)
FAX VARCHAR2(80)
PERSON_PARTY_ID NUMBER
When I look at the contents of "test.txt" they appear as follows:
USER_NAME USER_ID DESCRIPTION EMAIL_ADDRESS FAX PERSON_PARTY_ID
---------------------------------------------------------------------------------------------------- ---------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------- ----------------------------------------
ZZZAAABB 12345 Joseph Bloggs joseph.bloggs@myorganisation.com June 15 rev:SEPT REV 051214; jun15 rev 11144455
1 row selected.
There is a huge amount of padding / blank space between the fields, and the total width of the data is 746 characters. I tried TRIM, TRIMOUT and TRIMSPOOL but none make any difference.
I tried a smaller LINESIZE e.g. 700, but then the data wraps as follows:
USER_NAME USER_ID DESCRIPTION EMAIL_ADDRESS
---------------------------------------------------------------------------------------------------- ---------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
FAX PERSON_PARTY_ID
-------------------------------------------------------------------------------- ----------------------------------------
ZZZAAABB 12345 Joseph Bloggs joseph.bloggs@myorganisation.com
June 15 rev:SEPT REV 051214; jun15 rev 11144455
1 row selected.
If I use dummy data the output is nice and compact:
SET LINESIZE 200
with tbl_data AS
(SELECT 'ZZZAAAB' user_name
, 12345 user_id
, 'Joseph Bloggs' description
, 'joseph.bloggs@myorganisation.com' email_address
, 'June 15 rev:SEPT REV 051214; jun15 rev' fax
, 77778899 person_party_id FROM DUAL)
SELECT * FROM tbl_data;
USER_NAME USER_ID DESCRIPTION EMAIL_ADDRESS FAX PERSON_PARTY_ID
--------- ---------- ------------- -------------------------------- -------------------------------------- ---------------
ZZZAAAB 12345 Joseph Bloggs joseph.bloggs@myorganisation.com June 15 rev:SEPT REV 051214; jun15 rev 77778899
1 row selected.
Is this something I can't get around when selecting data from tables, due to the design of the table?