Hi all,

I have used UTL file utility before to dump table data into a comma
seperated file but this time I have a tricky problem.
I have a table called customers with the following fields
work_order_no, customer_no, contract_start_date, title, Name,
first_name, street, street_no, bank_code, name, liason_name
and have a products table with following fields
work_order_number, material_type, material_number, qty

1. I need to write the customer details + product details of the
customer into a comma seperated file as below

A
1001,
1234,
12.01.2004,
Mr. Dr.,
Alphs,
Thomas,
my street,
12,
1112,
Barclays,
Mr. Dr. Alphs Thomas,
<EOH> - is a fixed
1001,
M,
12,
1,
<EOL> is fixed
1001,
M,
12,
1,
<EOL> is fixed
1001,
M,
12,
1,
<EOF> is fixed
4 - total number of records
1 - total number of A type of records(in the first position
How do i write a Pl/Sql code which can be called from command prompt.
Thanks in advance

Ramu

Hi,

You can use this to code to generate sql which list the sqls with
comma separated for all the tables in your user. Please customize as
you need.

SQL> get a
1 declare
2 stmt varchar2(4000);
3 begin
4 for k in (select table_name from user_tables where table_name
= 'DEPT') loop
5 stmt := 'SELECT ' ;
6 for i in (select table_name,column_name from user_tab_columns
where table_name = k.table_name) l
7 stmt := stmt|| i.column_name || '||'||''','''|| '||';
8 end loop;
9 stmt := stmt ||'||' || ''',''';
10 stmt := substr(stmt,1,length(stmt)-12)||' FROM '||k.table_name
12 end loop;
13* end;

Cheers
Dhana



r_kolturu (AT) yahoo (DOT) de (ram) wrote in message news:<78318ed0.0404212332.8c30d86 (AT) posting (DOT) google.com>...