FreeLists / oracle-l / RE: Writing text file from PL/SQL block

[Amar Kumar Padhi <amar.padhi@xxxxxxxxx>]

Hi Anju,
You can format Col Output before sending it to file. Here is one Sample I just 
tried on 10g. Let us know if you are getting stuck somewhere. One point to 
highlight: the files are generated on database server, security (if you have 
one) may Object to this. In our env. Interfaces are to be dealt with on 
Mid-tier. 

AM01:AMAR> create or replace directory tmp as '/tmp/';
Directory created.

create or replace procedure utl_file_io is
 l_fil utl_file.file_type;
 l_buf varchar2(200);
begin
 l_fil := utl_file.fopen('TMP', 'amar_test.t', 'W');
 utl_file.put_line(l_fil, 'This is a sample text file.');
 utl_file.put_line(l_fil, 'Generated from Oracle utl_file package.');
 utl_file.put_line(l_fil, 'Below is the content of am1010 table.');

 for rec in (select col1, col2 from am1000) loop
 utl_file.put_line(l_fil, rpad(rec.col1, 15, ' ') || rec.col2);
 end loop;
 utl_file.put_line(l_fil, 'End of File');
 utl_file.fclose(l_fil);

 l_fil := utl_file.fopen('TMP', 'amar_test.t', 'R');
 loop
 utl_file.get_line(l_fil, l_buf);
 dbms_output.put_line(l_buf);
 end loop;
exception
 when no_data_found then
 utl_file.fclose(l_fil);
end;
/


AM01:AMAR> exec utl_file_io
This is a sample text file.
Generated from Oracle utl_file package.
Below is the content of am1010 table.
1 this
2 is
3 testing
4 output
End of File

PL/SQL procedure successfully completed.



-----Original Message-----
From: "Anju Bala" <oraclebala@xxxxxxxxx>
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Sent: 3/09/07 2:12 PM
Subject: Writing text file from PL/SQL block

hi list,

I have a strange but interesting problem. I have three tables with below
structure , I need to generate a text file using a PL/SQL block on the basis
of below criteria:

Table1 structure
****************
dealerid number;name varchar2(100);age number(3);sex char(1);

table2 structure
*******************
feed_id number(10);feed_name varchar(20);feed_format clob;

table3 structure
******************
feed_id number(10);column_name varchar2(100); -- to store the column name of
table1
column_position number(2); -- to store at what position column of table1
should be written
application_id number(4); -- to store what application it is

scenario1
************

i have to generate a text file from PL/SQL block wherein table1 column data
should be written based on the position mentioned in table3. for example if
dealerid is in 3 position,name is in 1 and age is in 2 position then my text
file should contain data something like :

name,age,position

Scenario2
************
if table3 doesnt contain any record for any column of table1 then that
column should not be written.


I know it can only be done using UTL_FILE package but dont know how to
achieve it? I would really be obliged if anyone can provide with a sample
block to achive this to me.

Please

--
http://www.freelists.org/webpage/oracle-l