oracle RowType issue

From: FB <franklinbruce_at_gmail.com>
Date: Wed, 20 Jan 2010 07:10:08 -0800 (PST)
Message-ID: <deccce4b-b0e3-4564-82b6-d5a08a48ade1_at_h2g2000yqj.googlegroups.com>

Hi All,
Im new to Oracle.
I have a Rowtype and i want to Loop through that Rowtype without mentioning the Column name.

My code as below :

declare
  empRowType Emp%ROWTYPE;
begin
SELECT * INTO empRowType FROM Emp

        WHERE Emp_id = 101 ;

       for x in ( select column_name from user_tab_columns where table_name = 'Emp')

       loop
                   i:=i+1;
                   y:= x.column_name;
                   z:= 'empRowType.'|| x.column_name;
                   dbms_output.put_line (z);
       end loop;

end

Steps :

1. First Im declaring a RowType of Employee table.
2. Assigning value to RowType by making a select statement.
3. I want to get all the column name value printed so im selecting the column names of the table by using USER_TAB_COLUMNS table and running a for loop.
4. Im concatenating RowTypeName.Column name (ex: z:= 'empRowType.'|| x.column_name;)
5. Print the Value

I get a O/P as String as :

empRowType.Emp_ID
empRowType.Emp_Name
empRowType.Emp_Department

but i want the O/P as

"101"
"John Peter"
"Marketing Department"

Pls someone help. This is a sample i have created to put in this website. I cannot use RowType column to meet the requirement.

Thanks in advance,

FB Received on Wed Jan 20 2010 - 09:10:08 CST