Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Formatting Ouput - Select Statement

Re: Formatting Ouput - Select Statement

From: <xmark.powell_at_eds.com.x>
Date: 9 Apr 2001 14:20:48 GMT
Message-ID: <9asgg0$agi$1@news.netmar.com>

In article <tcrp3ct86gb6ea_at_corp.supernews.com>, Jennmar Corp <here_at_work.com> writes:
>I have to pull information out of the database and format it to fit the
>standard layout for an upload file.
>
>Example:
>Format number len=5 Left Justified A####
> filler len=2 Spaces
> dept len=3 Left Justified A##
> filler len=2 Spaces
> etc...
>Output
>12345..123..Next Element
>
>Can I do this within the context of a select statement or is there an easier
>way to retrieve and then format the output?
>
>select number, dept, etc. from table1 ?
>
>Thanks!
>
>

By default character fields are left justified and numbers are right justified so if you want to create a fixed format output file you could do something like this:

select to_char(number_col,'00009')||' '||rpad(dept,3,' ')||' ' etc....

You might want to check the SQL*Plus manual under the set command to see the formatting commands for pagesize, linesize, heading, feedback, etc... You use these commands to eliminate pagebreaks and headings, put all columns on one record, eliminate headings when pagesize not 0, and eliminate the feedback line from your output spool file or screen.

The column command is where you normally set the format and sometime heading for columns, column dept format a3 heading Department.

             column cost format 9,999.99

Received on Mon Apr 09 2001 - 09:20:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US