Re: oracle RowType issue

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 20 Jan 2010 18:11:19 +0100
Message-ID: <4b573931$0$22933$e4fe514c_at_news.xs4all.nl>

Op 20-1-2010 16:10, FB schreef:
> 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
>

With your output, create a sql statement and use dynamic SQL (look for execute immediate) to run your query. z is a string, not a variable name.

Shakespeare Received on Wed Jan 20 2010 - 11:11:19 CST