Access cursor in cursor ...

Hi

The requirement is as follows:

Have a table (l_valid) that has 5 columns, of which 4 columns hold the name of related tables and 1 column has a key.

Now, I need to access these 4 tables using the key in a cursor. (this I have done).

Furthermore, after getting the names of 4 tables using the key, I need to iterate through the columns of each of these 4 tables in a cursor. How do I do this?

I done the following till now...

Code:

---

CURSOR c1 IS
                SELECT  hrank a, dbv b, dgv c, bbv d, fbv e, abv f, rbv g
                from l_valid
                where dbv is not null or
                fbv is not null or
                dgv is not null or
                bbv is not null or
                abv is not null or
                rbv is not null;

        CURSOR c2(p_b tab.tname%type) IS
                SELECT tname h
                FROM tab
                WHERE tname = p_b;

---

Thanks for your input.

Hi,
Use the Joining conditions in the where clause for the all the tables with their primary constraints.

Quote:

---

Originally posted by Kumar_RP
Hi,
Use the Joining conditions in the where clause for the all the tables with their primary constraints.

---

afraid the above won't do!

I need to do a lot of processes after I get the multi-row result set.

I'm currently thinking of using a call to another procedure, to get this multi-row result set.

Please give your input.

Thanks.

If i got it right if the table name is going to be dynamic, try using a weak ref cursor, which would solve ur problem.
HTH
-dharma

Quote:

---

Originally posted by dharma
If i got it right if the table name is going to be dynamic, try using a weak ref cursor, which would solve ur problem.
HTH
-dharma

---

Hi

Thanks. Can you eloborate further about how I can do this? links if possible.

Appreciate it.

Hi,
Check this..

DECLARE
CURSOR dept_cur IS
SELECT deptno
FROM dept
ORDER BY deptno;
-- Employee cursor all employees for a dept number
CURSOR emp_cur (v_dept_no DEPT.DEPTNO%TYPE) IS
SELECT ename
FROM emp
WHERE deptno = v_dept_no;
BEGIN
FOR dept_rec IN dept_cur LOOP
dbms_output.put_line('Employees in Department '||TO_CHAR(dept_rec.deptno));
FOR emp_rec in emp_cur(dept_rec.deptno) LOOP
dbms_output.put_line('...Employee is '||emp_rec.ename);
END LOOP;
END LOOP;
END;
/

Try like this