I have a quick question. I have a history table where I store the contents of a generated EDI flat file. The 3 columns are
EDI_RECORD VARCHAR2(500)
REC_LEN NUMBER(5)
SEQ# NUMBER(35)
Since each record is a separate space padded length (rec_len) that was padded to the right is there anyway to use spool to dump the records into a flat file while having the variable length records maintain their padding?
I tried the following
set pagesize 0
set feedback off
set linesize 20000
set trimspool on
set termout off
spool c:\temp\test.txt
SELECT rpad(edi_record,rec_len,' ') string
FROM CUSTOM.EDI_HISTORY_TRACKING
order by seq#;
spool off
and it removed all the trailing spaces in the test.txt file.
I then tried the following
set pagesize 0
set feedback off
set linesize 20000
set trimspool off
set termout off
spool c:\temp\test.txt
SELECT rpad(edi_record,rec_len,' ') string
FROM CUSTOM.EDI_HISTORY_TRACKING
order by seq#;
spool off
and it made every line the same 20000. If you don't make the linesize big enough it will insert line breaks into the file at the linesize length.
I realize that I can easily get around this by using utl_file and regenerate the file out on the server using rpad and utl_file.put_line However using spool would be simple and sweet. Does anyone have a sqlplus trick that will handle this task? Any help would be appreciated.