Solved

Export To Fixed Length File

Posted on 2002-07-11
23
1,987 Views
Last Modified: 2012-06-22

I want to take data in 4 tables, and export the data to a fixed lenght flat file format.  Then have that file sent to an email address. How can I do this?  Please provide example.

Thanks in advance for your help.
0
Comment
Question by:aj85
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 5
  • +2
23 Comments
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 7149681
There are multiple ways of doing this, but none are totally automated using just Oracle software.  Here are two options:

1. Use SQL*Plus to select the data from each table and spool it to four separate ASCII files.  Then open your e-mail program, address the message and attach the four files you created with SQL*Plus.  If you use this apporach watch out for date and number columns.  Depending on your default settings, you may lose precision in number columns and/or lose the time portion of dates, unless you first set the column formats for each number column and set the nls_date_format to one that will include the time portion.

2. Write a PL\SQL procedure for each table that uses a cursor loop and utl_file to create an ASCII file (on the server) for each table.  Then open your e-mail program, address the message and attach the four files (from the server!) that you created with PL\SQL.

It is possible to have PL\SQL send mail, so if this is something you plan to do regularly, you may want to use the PL\SQL approach.

It may be possible to automate this with Java or Access also.
0
 
LVL 2

Expert Comment

by:NetoMan
ID: 7149998
what is tour operating system ? depending on that we can give you more ideas to fully automate this process.

If it is unix or solaris, you can use OS batchs programs to ejecute the PL/SQL procedure that will create the files and also email it when process is done with mail instructions from the OS.

If NT or Windows based system, you can use any front end languaje to do that (VBasic, Delphi, Java, VBScript, etc) or do something like I mentioned for Unix with task programmer and email based routines.

NetoMan :)
0
 

Author Comment

by:aj85
ID: 7150977
markgeer,

Please provide an example of your #2 suggestion.  


NetoMan,

Please provide an example using Unix.

Thanks to both of you.
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 27

Expert Comment

by:kretzschmar
ID: 7153658
i usually use delphi for this ;-)
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 7154400
If you have (and are familiar with) any of the tools listed by Netoman, that would likely be the simplest way to do this, but if you want to do this with PL\SQL, that is possible.  

If you haven't ever used utl_file before, you will need to add at least one "utl_file_dir=..." entry to your init*.ora file, then shutdown and restart the database, otherwise PL\SQL will not be able to write ASCII files.

Here's a simple example of a PL\SQL procedure to create an ASCII file:

create or replace procedure [procedure_name] as
  out_file   utl_file.file_type;
  cursor c1 is select [column(s)]
  from [your table]
  order by [whatever];
  -- declare a rowtype variable or individual variables for each column to be selected
begin
  out_file := utl_file.fopen(out_file,'C:\temp.txt','A');
  open c1;
  loop
    fetch c1 into [variable(s)];
    exit when c1%notfound;
    utl_file.put_line(out_file,[variables]);
  end loop;
  close c1;
  utl_file.fclose(out_file);
end;
/

You would need to replace the text in brackets [...] with values for your tables.  Also, this assumes that you have a utl_file_dir entry of:
utl_file_dir=C:\temp
in your init*.ora file.  This is typical for Oracle on Windows.  If you O/S in Unix, etc. just change 'C:\temp' to a valid value for your server.

Getting PL\SQL to send mail automatically is possible, but not so simple, especially if you want to include attachments.
0
 

Author Comment

by:aj85
ID: 7158490
Ok, so how would I write the file out and have for example. The Name field write to position 1-5, Address 6-20, blanks spaces, then position 35-45 City etc., ending with a maxium length of 80 bytes or charaters.  How can this be done?  

Thanks in advance.  
0
 
LVL 2

Accepted Solution

by:
NetoMan earned 75 total points
ID: 7159563

you can control the positions ordering the data fields and restrict their size in the output line that will be written on file.

Example :

create or replace procedure myProc as
 myfile   utl_file.file_type;

 cursor myCursor is
 select name,city, amount
 from MyTable
 order by name;

 vMyDir     VARCHAR2(20):= '/transfer/';
 MyFileLine VARCHAR2(80);

begin  

 myfile := UTL_FILE.fopen(vMyDir,'export.txt','W');
 
 for i in myCursor loop

  myFileLine :=
     RPAD(NVL(name),' '),5,' ') -- 1 to 5 field name
     ||LPAD(' ',15,' ') -- blank spaces 6-20 (15 spaces)
        ...  --  the positions are controled by
        ...  --  size of fields and LPAD or RPAD functions
     ||LPAD(TO_CHAR(NVL(amount,0)),10,'0');-- int value
       
   UTL_FILE.put_line(myFile,myFileLine);

 end loop;

 utl_file.fclose(MyFile);

end;

In that example you write the file to Operating system.
Now from unix you can use a cron (programed task) to run every hour or every day to run a script like this or run manually. supose this is a file called script.txt in Unix or Solaris :

 mail export.txt < aj85@mycompany.com


you can simple run it by calling it

> script.txt


this will send the file export.txt to the nail address

Note: you must ask to your Solaris or Unix Administrator to have the asocciated links and configurations in order to the operating system could send the mail.



0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 7159979
Which operating system and e-mail system do you have?
0
 

Author Comment

by:aj85
ID: 7161008
Netoman,

Thanks for that clear explaination!  I am going to try this and get back with you soon.

Markgeer,

I hope that I can split the point between the two of you when I get this to work.  You both have been very helpful.

Thanks!
aj85
0
 

Author Comment

by:aj85
ID: 7164012
NetoMan,

Well, my DBA will not let me use UTL_FILE, what can I do next?  Any suggestions?

Thanks,
aj85
0
 
LVL 2

Expert Comment

by:NetoMan
ID: 7164776
maybe you can use an export of the tables, but that means download all its contents. first check if you have the privileges to run the export utility.

at prompt run :

> exp

another options are using 3rd party tools like TOAD from quest software. You can do wat you want from this tool. export to an ASCII file with or without formatted text from a table or a sql statement.

NetoMan :)


0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7164804
toad is a delphi written tool ;-)
0
 
LVL 2

Expert Comment

by:NetoMan
ID: 7164872
yes it's true kretzschmar, that's the way is a great Tool :)

aj85 : if you have Delphi or another Development suite maybe we can help you writting a tool of your own.

NetoMan :)
0
 

Author Comment

by:aj85
ID: 7166156
Well, I need to automate this process, so I can't use a tool like TOAD.  Is there a way to do this with a shell script?  I can query the data with the procedure, but I can't get it in the needed flat file format.  My plan was to have this run nightly to get the data with a cron job. But now, I am not sure how to proceed.

Thanks,
aj85
0
 

Author Comment

by:aj85
ID: 7166159
Netoman,

I don't have permission to use export utility.

Thanks,
aj85
0
 
LVL 2

Expert Comment

by:NetoMan
ID: 7169479
can you use Pro*C++ ? maybe not, you are too restricted.

0
 
LVL 2

Expert Comment

by:NetoMan
ID: 7169493
with pro*C++  you can write a program to connect to Oracle, run a procedure or execute a select statement and then use print or write functions to make the flat file.
this program can be called in a shell script. you must find if you have this compiler installed.

With out UTL_FILE permission or exp utility, I don't see another options.

NetoMan :)


0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 7172062
With SQL*Plus it is easy to generate fixed-length ASCII files from any table, and you can run SQL*Plus from a shell script.  I'm not a Unix expert, but I think you can automate sending e-mail too via a shell script, so you should be able do what you need via a shell script and a *.sql file for each table.  Remember the note in my first comment about date and number columns though.  When you select them in SQL*Plus you may lose precision on number columns and lose the time portion of dates unless you format them appropriately.
0
 
LVL 2

Expert Comment

by:NetoMan
ID: 7172106
yes, it's true that is a valid option markgeer.

aj85, this is a simple example of a unix script using SQL plus and mail, can be executed from a cron task or manually from prompt. you must convert the follow lines to adjust to your Oracle server configuration and path installation :

ORACLE_HOME=/opt/oracle/product/817
export ORACLE_HOME
ORACLE_SID=SOLIDA
export ORACLE_SID
PATH=/usr/bin:/usr/ucb:/etc:/opt/oracle/product/817/bin:/bin:/usr/ccs/bin:.
export PATH
ORA_NLS33=/opt/oracle/product/817/ocommon/nls/admin/data
export ORA_NLS33

sqlplus myUser/myPassword  <<EOF
spool test.txt;
select myData from myTable where rownum <=10;
exit;
mail test.txt > myUser@myCompany.com
EOF  


NetoMan :)

0
 

Author Comment

by:aj85
ID: 7176179
Thanks for the additional information, I will get back with you all shortly.

Thanks again!
aj85
0
 

Author Comment

by:aj85
ID: 7183062

Ok, I have used "Spool" to accomplish part of this task.  But I am now faced with another task.  The query I have  gets the data and creates the flat file.  However, I need to create the flat file in a one to many format, meaning that the first line is going to be the company information for example, then the second, third, forth, etc. lines will be all the invoices associated with that company.  I hope this makes a little sense.  The problem I have is that my query retrieves the company information, then an invoice, then the company information again, followed by an invoice.  This continues to the end of the file.  So how can I accomplish getting only the company once followed by the associating invoices?

I also have an autosys job, that uses "Sendmail" to send the email file. But my file is appearing in the body of the email, and not as an attachment.  How can I send an attachment using "Sendmail"?

Thanks again!
aj85

0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 75 total points
ID: 7185503
Simple SQL statements in SQL*Plus can reliably create ASCII files that contain the data in an Oracle table or tables, but if you want something other than one line in the ASCII file for each record in the database, you will need to do some more SQL or PL\SQL work.  You may be able to create a view that makes the records look like you want them to look, then you can simply select from the view to create the ASCII file.  Your other option is to use a PL\SQL procedure (that uses utl_file) to create the ASCII file.  That gives you tremendous flexibility in formatting the output any way you wish, but then you have to do the work of writing and testing the PL\SQL procedure to get it to do what you want.

I don't know the Unix "sendmail" program/command so I can't help you with that part of the problem.
0
 
LVL 3

Expert Comment

by:patelgokul
ID: 9392151
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
split between netoman and markgeer
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

patelgokul
EE Cleanup Volunteer
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

690 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