Access cursor in cursor ...

[ggnanaraj]

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...

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.

[Kumar_RP]

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

[ggnanaraj]

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.

[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

[ggnanaraj]

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.

[Kumar_RP]

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