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.
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 is the starting oracle error number? What is meant by forward declaration in functions?
how to enter numeric values as hex numbers? : Sql dba
What is the maximum size of sqlite database?
What is application trigger?
Can you selectively load only those records that you need? : aql loader
What is attribute indicator in pl sql?
Is foreign key mandatory?
Is nosql relational?
how to fetch alternate records from a table? : Sql dba
What is the best sql course?
List and explain the different types of join clauses supported in ansi-standard sql?
What is sql and explain its components?
What is dcl in sql?
What is a loop in sql?
What does (*) mean in sql?