Load data from Oracle table iinto flat file positional wise

Use RPAD or LPAD, see this example:

ALEX@DEV > SELECT RPAD(DUMMY,10,' ')||RPAD(DUMMY,10,'X')||RPAD(DUMMY,10,'a') FIXED_ROW FROM DUAL;

FIXED_ROW
------------------------------
X         XXXXXXXXXXXaaaaaaaaa


How are you planning to export from Oracle?
Do you have SQL*Plus? --You can simply spool from this tool

you can extract data from oracle:
1.
from pl/sql using utl_file
2.
using sqlplus spool

both using functions like rpad, lpad, substr, ...

Follow this steps in SQL*Plus:

1. At the prompt (SQL> ) type the following: EDIT FIXED_ROW and hit ENTER
   You'll get a box telling you that that the file doesn't exist and asking you if you want to create it.
     -Click yes
     -In Notepad type this:
       SET DEFINE OFF
SET FEEDBACK OFF
SET LINESIZE 600
SET PAGESIZE 0
SET TIMING OFF
SPOOL 'C:\TEST.TXT'
SELECT RPAD(DUMMY,10,' ')||RPAD(DUMMY,10,'X')||RPAD(DUMMY,10,'a') FIXED_ROW FROM DUAL;
SPOOL OFF

To continue with the above posting:

SQL > @FIXED_ROW    --Type this at the prompt
SQL > EDIT C:\TEST.TXT --Then this to see the result

If you like the result than change the query inside tyour FIXED_ROW sql file to run yours.

Access Privileges:
     Requires SELECT privileges on DBA_TAB_COLUMNS and the table being unloaded.

Please use the below script...

SET ECHO off
REM --------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT on the given table(s)
REM --------------------------------------------------------------------------
REM PURPOSE:
REM Generates a sql*plus script to unload a table to a file and a
REM SQL*Loader script to reload the same data. Intent is to create
REM a faster alternative to export/import.
REM ---------------------------------------------------------------------------
REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT
REM supported by Oracle World Wide Technical Support.
REM The script has been tested and appears to work as intended.
REM You should always run new scripts on a test instance initially.
REM --------------------------------------------------------------------------
REM Main text of script follows:
set tab off
set heading off heading off feedback off echo off verify off space 1 pagesize 0
linesize 120
accept owner prompt 'What schema owns the table to be unloaded? '
accept table_name prompt 'What table is to be unloaded? '
accept default_precision prompt 'Total number of digits to be reserved for numbr
s w/out defined precision?'
accept default_scale prompt 'Total number of DECIMAL digits to be reserved f
or numbers w/out defined scale? '
---------------------------------------------------
-- Generate the unload script
---------------------------------------------------
spool unload_fixed2.sql
select 'SET HEADING OFF FEEDBACK OFF ECHO OFF VERIFY OFF SPACE 0 PAGESIZE 0
TERMOUT OFF'
from dual
/
--Calculate the sum of all output field lengths and set the output record size
select 'SET LINESIZE '
|| (sum(decode(data_type,
'CHAR',data_length,
'VARCHAR',data_length,
'VARCHAR2',data_length,
'DATE',14,
'NUMBER',decode(data_precision,
'',&default_precision+2,
greatest(data_precision-data_scale,1)+decode(data_scale,0,0,1)+data_scale)+1,
'FLOAT',&default_precision+2,
data_length)))
from dba_tab_columns
where owner=upper('&&owner')
and table_name=upper('&&table_name')
/
-- Generate an appropriate SQL*Plus COLUMN command to control formatting of
-- each output field
select 'COLUMN ' || rpad('"'||column_name||'"',32)
|| ' FORMAT '
|| rpad(decode(data_type,
'CHAR','A'||data_length,
'VARCHAR2','A'||data_length,
'VARCHAR','A'||data_length,'DATE','A14',
'NUMBER',decode(data_precision,'',rpad('0',&default_precision-&default_scale,'9'
)||'.'
||rpad('9',&default_scale,'9'), rpad('0',greatest(data_precision-data_scale,1),'
9') ||
decode(data_scale,0,'','.') ||
decode(data_scale,0,'',rpad('9',data_scale,'9'))),
'FLOAT',rpad('0',&default_precision-&default_scale,'9')||'.'||rpad('9',&default_
scale,'9'),
'ERROR'),40)|| ' HEADING ''X'''
from dba_tab_columns
where owner=upper('&&owner')
and table_name=upper('&&table_name')
order by column_id
/
-- Generate the actual SELECT statement to unload table data
select 'SPOOL &&owner..&&table_name..DAT'
from dual
/
column var1 noprint
column var2 noprint
select 'a' var1, 0 var2, 'SELECT '
from dual
union
select 'b', column_id, decode(column_id, 1, ' ', ' , ')||
decode(data_type,'DATE','to_char('||'"'||column_name||'"'||',''YYYYMMDDHH24MISS
'') '||'"'||column_name||'"' ,
'"'||column_name||'"')
from dba_tab_columns
where owner=upper('&&owner')
and table_name=upper('&&table_name')
union
select 'c', 0, 'FROM &&owner..&&table_name'
from dual
union
select 'd', 0, ';'
from dual
order by 1,2
/
select 'SPOOL OFF'
from dual
/
select 'SET TERMOUT ON'
from dual
/
spool off

Oracle doesn't give us a built-in utility to transfer data from an Oracle table to an ASCII file.  You can use these three options though:
1. A query in SQL*Plus with the "spool" command (This can create the file wherever you run SQL*Plus, either on a client PC or on the server.)
2. A stored procedure that uses "utl_file".  (This can create a file on the server only, not on a client PC, but it can be faster than SQL*Plus for large volumes of data.)
3. Use TOAD (or another utility) to do this task.

Hi folks,

I have Oracle SQL*Plus and i have to load the data which is currently present in a flat file into a txt file and it has fixed length and it is positional. My requirement is as follows:-

Position    FieldName     format                 Populate
1-6           OTC number  number(6)
7-9           NPA               number(3)
10-12      NXX                number(3)
13-16      Line                number(4)
17           rec_type         varchar2(1)       Enter 3
18-19     rec_sub_type  varchar2(2)      Populate with '00'
20          filer                   char(1)             populate with space
21         action_code       char(1)           i-insert, c-update, d-delete
22-24   class_of_serv    char(3)           populate with spaces, it will default to "Res"
25         BNS                    char(1)           populate with spaces, it will default to "D"
26-45   olns fields                                   populate with spaces
46        action_type         char(1)           i-insert,c-update, d-delete If C or I is used then positions 47-62            
                                                              must be populated
47        priv_indic            char(1)          a-allow,r-block name, must be populated with a or r if positions 46
                                                              or 48-62 are populated
48-62  string                  char(15)         subscriber name
63-64  for_lang              number(2)

I have 2 Oracle tables from which I have to take the data and load into a flat file in the above manner. I would appreciate it if you advice me how to go about.

Thanks

>> present in a flat file into a txt file
Are you trying to load the data from a flat file into Oracle and then output it as a fixed-position text file?

Hi Paquicuba,

No I have a Oracle table and I have to extract that data into a flat file in a fixed-position text file. I look forward for your advice.

Thanks

Or, is the data in an Oracle table (or two) now (as your question originially indicated)?

OK, then you can use either of the two options I suggested:
1. A query in SQL*Plus with the "spool" command
2. A PL\SQL procedure that uses utl_file.

Option #1 can create the file on a Windows client PC (if you run SQL*Plus on the client).  Option #2 will create the file on the server, not on a client PC.  Where do you want the file created?

Also, is this for a one-time data transfer?  Or, will this be an on-going interface?  How much data is involved?  Is it 100 records, 10,000 records, 1,000,000 records or more?

Hi markgeer,

There are 1,0000,000 records from two Oracle tables. I look forward for your advice.

Thanks and Regards

For large data volumes, I prefer to use a PL\SQL procedure that uses utl_file to write the ASCII file on the server, since that is usually faster than SQL*Plus on a client which has to pull of the data over the network before it can write it.  You did not indicate where you want the file created (client or server).  Do you have access to both?

Before you can use utl_file though, you (or a DBA) must do some setup.  Which version of Oracle do you have?  In Oracle8, you need a "utl_file_dir..." entry in the init*.ora file and a database shutdown and restart.  In Oracle9 or higher, you can use a logical directory instead, and you can create these "on-the-fly" without a database shutdown.

Have you ever used utl_file before in a PL\SQL procedure?

Hi,

No I have not used utl_file before in a PL/SQL procedure. I would appreciate it if you could let me know how to do that. The file will be at the server. I look forward for your help.

Thanks.

One other important note about logical directories: when you refer to them in PL\SQL you *MUST* use upper-case names like in this command:

log_file := utl_file.fopen('LOG',...

fastreader: http://www.wisdomforce.com/products-FastReader.html, choose option fixed length format