Prev: copying tables from one database to another
Next: Mixet.se® - International Business Advertising
From: FB on 20 Jan 2010 10:10
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

From: Shakespeare on 20 Jan 2010 12:11
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
From: ddf on 20 Jan 2010 14:53
On Jan 20, 10:10 am, FB <franklinbr...(a)gmail.com> wrote:
> 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

What, exactly, is your requirement? It has been mentioned that you
are creating a string, not a column reference for the declared type,
which explains your current output. Using execute immediate may not
work as that involves a context switch and once that happens the SQL
session created to execute your dynamic statement has no 'knowledge'
of empRowType, much less its contents.

Knowing your 'marching orders' will help us tremendously in assisting
you.


David Fitzjarrell
From: Gerard H. Pille on 20 Jan 2010 15:57
FB wrote:
> 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
>

You will have to use another programming language that allows enumerating the columns.
 | 
Pages: 1
Prev: copying tables from one database to another
Next: Mixet.se® - International Business Advertising