Access cursor in cursor ...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Access cursor in cursor ...

  1. #1
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865

    Question 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.
    Last edited by ggnanaraj; 02-10-2003 at 11:38 PM.

  2. #2
    Join Date
    May 2001
    Location
    Dallas, US
    Posts
    78
    Hi,
    Use the Joining conditions in the where clause for the all the tables with their primary constraints.
    RP Kumar
    You Can Win, if u believe Yourself

  3. #3
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    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.

  4. #4
    Join Date
    Dec 2000
    Posts
    138
    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

  5. #5
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    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.

  6. #6
    Join Date
    May 2001
    Location
    Dallas, US
    Posts
    78

    Check this

    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
    RP Kumar
    You Can Win, if u believe Yourself

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width