Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Writing text file from PL/SQL block
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_at_gmail.com>
To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>
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
table2 structure
table3 structure
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
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-lReceived on Mon Sep 03 2007 - 23:13:11 CDT