Hi Guys,
I have a situation where I need to access the column values from rowtype variable. However, the column names are dynamic.
below is sample code:
declare
Cursor c1 is select * from emp;
Cursor c2 is select column_name from xyztable;
v_c2 c2%rowtype;
v_str varchar2
v_value varchar2(200);
begin
for rec in c1
loop
open c2;---this cursor has column names like EMPLOYEE_ID, FIRST_NAME, LAST_NAME etc.
loop
fetch c2 into v_c2;
exit when c2%notfound;
/* now lets say i want to access value of LAST_NAME from cursor c1, so I am writing below code, however it does not work as expected */
v_str:= 'rec.'|| v_c2.column_name; -- this will give me string like "rec.EMPLOYEE_ID"
v_value:=v_str;
end loop;
end loop;
end;
/
Plz help ASAP.Thanks.
- 2 Answers
- 2872 Views
- I also Faced
- E-Mail Answers
Answer Posted / ajitnayak
DECLARE
CURSOR C1 IS SELECT *
FROM DEPT;
CURSOR C2 IS SELECT *
FROM EMP;
v_c2 C2%ROWTYPE;
v_str VARCHAR2(30);
v_res VARCHAR2(35);
BEGIN
OPEN C2;
FOR REC IN C1
LOOP
FETCH C2 INTO v_c2;
EXIT WHEN C2%NOTFOUND;
v_str := 'REC'||v_c2.ENAME;
v_res := V_STR;
DBMS_OUTPUT.PUT_LINE('NAME'||v_c2.ENAME);
END LOOP;
CLOSE C2;
END;
| Is This Answer Correct ? | 1 Yes | 0 No |
Post New Answer View All Answers
What are the benefits of stored procedures?
Does truncate need commit?
Write a query to find the names of users that begin with "um" in sql?
What is a data manipulation language?
How does sql*loader handles newline characters in a record? : aql loader
what is column? : Sql dba
What is difference between procedure and trigger?
What is blind sql injection?
how to convert dates to character strings? : Sql dba
Explain the components of sql?
How do I make my sql query run faster?
How do I view tables in mysql?
what are the maximum number of rows that can be constructed by inserting rows directly in value list? : Transact sql
What is relationship? How many types of relationship are there?
explain mysql aggregate functions. : Sql dba
