Solved

Export To Fixed Length File

Posted on 2002-07-11
23
1,961 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
  • 8
  • 7
  • 5
  • +2
23 Comments
 
LVL 34

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
 
LVL 27

Expert Comment

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

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 34

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 34

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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Cannot open form error 6 48
Oracle Database Upgrade 13 41
Migrate database to ASM disks. 1 28
PL/SQL - Leading zeros 7 41
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo 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

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now