Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Load data from Oracle table iinto flat file positional wise

Posted on 2007-10-02
17
Medium Priority
?
7,186 Views
Last Modified: 2013-12-19
Hi Folks,

I need to load data from Oracle tables into a flat file which is a fixed positional flat file i.e each column should be in a particular position and it should be fixed. My OS is Windows. I would appreciate your help ASAP.

Thanks and Regards,

pagesheetal
0
Comment
Question by:pagesheetal
  • 6
  • 4
  • 4
  • +3
17 Comments
 
LVL 23

Expert Comment

by:paquicuba
ID: 20000308
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
0
 
LVL 9

Expert Comment

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

both using functions like rpad, lpad, substr, ...
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 20000366
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 23

Expert Comment

by:paquicuba
ID: 20000380
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.
0
 
LVL 18

Expert Comment

by:sventhan
ID: 20000773
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


0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 20001739
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.
0
 

Author Comment

by:pagesheetal
ID: 20002194
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
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 20002613
>> 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?
0
 

Author Comment

by:pagesheetal
ID: 20005825
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
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 20005830
Or, is the data in an Oracle table (or two) now (as your question originially indicated)?
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 20005858
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?
0
 

Author Comment

by:pagesheetal
ID: 20013417
Hi markgeer,

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

Thanks and Regards
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 20013483
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?
0
 

Author Comment

by:pagesheetal
ID: 20018013
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.
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 1500 total points
ID: 20021513
You first need to create a "logical directory" using an Oracle login account that has DBA (or "create directory") privileges.  Here is as example of a command to create a logical directory:
CREATE OR REPLACE DIRECTORY TEMP AS '/tmp';

(Note: the physical directory,"/tmp" in this example, must be a valid physical directory on the server that the O/S user account that runs Oracle has read/write permissions on.)

Then you need to grant permission on that logical directory to the schema owner of the PL\SQL procedure that will use logical directory, like this:
grant read, write on directory TEMP to [schema_name];

Then you need to write a PL\SQL procedure that gathers the data you need (probably with a cursor loop) and uses procedures in the package: utl_file to write the data to the ASCII file.

Here is the structure of a procedure that you can use:

create or replace procedure [procedure_name] as
  log_file   utl_file.file_type;
-- set up additional variables here to hold the values to be selected
  cursor c1 is select [columns...]
begin
  log_file := utl_file.fopen('LOG','[procedure name]_'||to_char(start_tm,'MMDD')||'.log','A');
  open c1;
  loop
    fetch c1 into [variable(s)];
    exit when c1%notfound;
    utl_file.put_line(log_file,[variables...]);
  end loop;
  close c1;
  utl_file.fclose(log_file);
end;
/
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 20021540
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',...
0
 

Expert Comment

by:upss
ID: 21790789
fastreader: http://www.wisdomforce.com/products-FastReader.html, choose option fixed length format
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Suggested Courses

572 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