Solved

Creating Fixed length Text file from Oracle 9i table.

Posted on 2008-10-30
4
965 Views
Last Modified: 2013-12-19
Hi,
  I am trying to generate a fixed length text file from Oracle 9i table.
Ex:
  My expected output is
  Positon       Field
  1-10           First Name
  11-20         Last Name
   20-40        Address
My output is going to look like this:
Steveson  Cathy     Washington DC
Robbs       Valles    Maryland

What is best way i can do, UTL_FILE or Spool or some other approach? If so how to do?

Thanks.
0
Comment
Question by:jainulap
4 Comments
 
LVL 27

Accepted Solution

by:
sujith80 earned 30 total points
ID: 22847497
spool approach.

set heading off
set pages 0
set echo off

select rpad(firstName,10)||rpad(lastName,10)||rpad(address,20)
from <table>;
0
 
LVL 9

Expert Comment

by:MarkusId
ID: 22848449
Or you can define columns (also spool, the rpad-approach wou8ld also work with utl_file)

set heading off
set pages 0
set echo off
set trimspool on
col firstName format a10
col lastName format a10
col address format a20

spool filename

select firstName, lastName, address
from <table>;

spool off

Have also a look at http://www.psoug.org/reference/sqlplus.html about the possibilities of column-formatting in Oracle
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question