| How to spool data to a fixed fomat file? [message #20248] |
Wed, 08 May 2002 16:53  |
JAW
Messages: 3
Registered: May 2002
|
Junior Member |
|
|
Hi,
Can anyone tell me if there is a way to spool data from an Oracle table to a fixed format text file? I'm currently concatenating the fields (not using a comma). Null fields are not populating my output file, thereby disrupting the spacing. I need the spaces from the null fields included in the output file.
Thanks for any help.
|
|
|
|
| Re: How to spool data to a fixed fomat file? [message #20252 is a reply to message #20248] |
Thu, 09 May 2002 02:09  |
Frank Chileshe
Messages: 7
Registered: November 2001
|
Junior Member |
|
|
Hello Jaw,
try to use the null value function in your select
statement that is spooling the data to your flat file.
1. For character fields > nvl(field_name,'****')
where **** are blanks spaces making up the precision
of the field_name. For instance if the field_name
is six characters, you should use
nvl(field_name,' ')
2. For other datatype fields, convert them to character
(if applicable) and apply the null value functions
as above.
I have done a simple test with this table tst_fmt
SQL> desc tst_fmt
Name Null? Type
------------------------------- -------- ----
FLD1 VARCHAR2(4)
FLD2 NUMBER(8,2)
FLD3 DATE
FLD4 NUMBER(6)
my query is :
set heading off
spool tst.txt
select NVL(FLD1,'****')||
TO_CHAR(NVL(FLD2,0),'00000.09')||
NVL(TO_CHAR(FLD3,'DD-MON-YYYY'),'***********')||
TO_CHAR(NVL(FLD4,0),'000009')
from tst_fmt;
spool off
In my example I have use '*' instead of ' '.
Hope that helps.
Regards
Frank
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
