Writing text file from PL/SQL block

From: Anju Bala <oraclebala_at_gmail.com>
Date: Mon, 3 Sep 2007 15:05:51 +0530
Message-ID: <887a5be30709030235x279af450y919cc5bb7ec52adc@mail.gmail.com>

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

Received on Mon Sep 03 2007 - 04:35:51 CDT