I have one request from database development team. They want to spool out or export out 50 tables in one schema to 50 separate files. Each file must contains column name and data under column. data can be delimited by comma or tab and so on. I think it can be done by using Oracle UTL_FILE. But UTL_FILE will write all table data into one huge file. My development team doesn't want to see this. So I want to try PL/SQL procedure to spool each table out one by one into csv file with column name and data. I have write some basic block to spool out file. it looks ok and need to be tweaked.
I want to get advise from Gurus here. How to get this job done to meet requirement. If you can provide some hints or code, that will be greatly appreciated. My basic PL/SQL is posted here. This only export one table. I can write it as stored procedure with parameter. Then write another procedure to call this one with table name as parameter. But I want to seek some more efficient way to write this procedure. Then using shell script to call procedure to reach my goal. Please input your idea and help. Thanks.
***basic code, Not real code because of security****
declare
cursor emp_rec is select e.emp_code, e.emp_name, to_char(e.date_of_birth, 'mm/dd/yyyy') data_of_birth, s.pay_month_year, sum(s.gross_pay) gross_pay
from employee_master e, employee_salary s
where s.emp_id=e.emp_id and rownum<=100
group by e.emp_code, e.emp_name, to_char(e.date_of_birth, 'mm/dd/yyyy'), s.pay_month_year;
begin
dbms_output.enable(100000);
dbms_output.put_line ('emp_code, emp_name, date_of_birth, pay_month_yera, gross_pay');
for i in emp_rec loop
dbms_output.put_line (i.emp_code||','||i.emp_name||','||i.date_of_birth||','||i.pay_month_yera||','||i.gross_pay);
end loop;
end;
-------execute script
SQL> set serveroutput on;
SQL> spool d:\emp_rec.csv
SQL>@d:\exp_emp.sql