spooling LONG columns to fixed-width -- how?
I am using SQL*Plus to SPOOL table contents to flat file in fixed-width format (via a straight SELECT statement).
In order to do fixed-width, I must control exactly how many characters each column displays. I am doing this using SQL*Plus COLUMN statements that define the length of the output for each column (such as A15).
But this only works on character-based data types.
I need to spool a table that contains a LONG column.
I can use SET LONG xxx to set the maximum length of the LONG output, but it does not enforce a minimum length.
I can't apply RPAD to fill out the entire column width because RPAD, like other string functions, doesn't work on LONG data types.
How can I accomplish this?
I have searched this forum but the only references I found for converting LONG to VARCHAR2 involve PL/SQL--I'd have to process the table in a cursor, one record at a time, so I can apply PL/SQL conversions to the values, but then how can I SPOOL all the records to a file?
Any help is appreciated!