Query on formatting data in a specific way in Oracle

[andhibis]

Hi all,
This is my first post in oraclepassport,thanks for giving me the opportunity to share my problems with you all.

I am currently doing a project where i need to write a stored procedure which will be doing the following-

i)it will retrieve multiple columns from multiple tables in a single database(through join) based on certain conditions

II)then it will store the entire data in a certain field(File_data) of staging table

inside file_data a header and a trailer will be present with the records.also the field values will be pipe separated and a new record will start in a new line.

So,the data inside the file_data of staging table will look like this-

H|v1000
transdate|ordnmb|deposite_amt|order_status....
12-nov-09|123456|23.8|C...
4-dec-07|234567|67.7|R...
..........
7-jan-04|567890|54.7|x.....
T|234(record count)

I am new to oracle(new to IT industry as well  i just have graduated),i did this formatting using java, but my project leader wants me to do the formatting using SP,and wants me to use staging table, so please help.

[sibanjan]

Hi andhibis,
   Welcome to oraclepassport. I have prepared a stored procedure for you using the employees and departments table present in HR schema of oracle.
Just try this one out and if you require any changes please feel free to post them in our forum.
 
create table file_date(varchar2(200));
/

   CREATE OR REPLACE PROCEDURE xxcust_ft
is
cursor c1 is select e.employee_id,e.hire_date,d.department_id,d.department_name
             from employees e,departments d
             where e.department_id=d.department_id;
emp_rec c1%rowtype;
str varchar2(100);
v_count varchar2(100);
v_header varchar2(100);
BEGIN
 open c1;
    v_header:='employee_id | hire_date | department_id | department_name';
    dbms_output.put_line(v_header);
    insert into file_data values(v_header); 
 loop
    fetch c1 into emp_rec;
     str:=emp_rec.employee_id||'|'||emp_rec.hire_date||'|'||emp_rec.department_id||'|'||emp_rec.department_name;
    dbms_output.put(str);
    insert into file_data values(str);
    dbms_output.put_line('-------- 1 row inserted');
 exit when c1%notfound;
 end loop;
    v_count:='TOTAL NUMBER OF ROWS INSERTED '||to_char(c1%rowcount);
    dbms_output.put_line(v_count);
    insert into file_data values(v_count);
 close c1;
END;
/
execute xxcust_ft;

[dtech-team]

Good job Sibanjan!! Thanks.