Cursor in a cursor???? Is it possible??

[supermega]

I have 2 tables,
EMP(empno, phone, dept, sal, hire_date, q_val1, qval2, qval3)
EMP_COLS(col_name, col_id)

Table EMP_COLS col_name field contains all of the column names in EMP.

I need to grab the first col_name from EMP_COLS and then loop thru every record in the corresponding column in EMP and if there is a value != 0 or != to null then copy that value and empno(from that row) and the column name into at datawarehouse table. Then do the same thing for all of the rest of the columns in the table. I was thinking of the following but im not sure if I can reference GetColsCurs in the 2nd cursor. Also I have no idea how I would then do the loop. Any help would be GREATLY appreciated!!!
Thanks,
SM

CURSOR GetColsCurs IS
SELECT COL_NAME
FROM EMP_COLS;
GetColsCursRec GetColsCurs%TYPE;

CURSOR GetDataCurs IS
SELECT GetColsCurs, EMP_NO
FROM EMP
Where GetColsCurs != '0'
AND Where GetColsCurs IS NOT NULL;
GetDataCursRec GetDataCurs%Rowtype;

[carp]

I usually pass values into the second cursor. It's not really necessary, but it makes the code easier for me to maintain.

CURSOR GetColsCurs IS
SELECT col_name, empno
FROM emp_cols;

CURSOR GetDataCurs(p_val NUMBER) IS
SELECT GetColsCurs, EMP_NO
FROM EMP
Where p_val != '0'
AND p_val IS NOT NULL;

BEGIN
FOR a IN GetColsCurs LOOP
FOR b IN GetDataCurs(a.col_name) LOOP
INSERT INTO my_table VALUES (a.the_column, a.empno);
COMMIT;
END LOOP;
END LOOP;
END:

Of course, if this is all you're doing, you really don't need to use a second cursor:

CURSOR GetColsCurs IS
SELECT col_name, empno
FROM EMP_COLS;

BEGIN
FOR a IN GetColsCurs LOOP
IF (a.col_name != 0 OR a.col_name IS NOT NULL) THEN
INSERT INTO my_table VALUES (a.col_name, a.empno);
COMMIT;
END IF;
END LOOP;
END;

[akkerend]

I don't think you can use a cursor in a cursor.
With a REF CURSOR and 2 loops it is possible:

DECLARE
TYPE cur_typ IS REF CURSOR;
c cur_typ;
query_str VARCHAR2(1000);

CURSOR GetColsCurs IS
SELECT col_name, empno
FROM emp_cols;

rval VARCHAR2(1000);
rempno NUMBER;
...

BEGIN

FOR GetColsCursRec IN GetColsCurs
LOOP
query_str := 'SELECT '||GetColsCursRec.col_name||
', empno FROM emp WHERE '||
GetColsCursRec.col_name||' !=0 AND '||
GetColsCursRec.col_name||' IS NOT NULL';
OPEN c FOR query_str
LOOP
FETCH c INTO rval, rempno;
EXIT WHEN c%NOTFOUND;
INSERT INTO dwh_table
VALUES(GetColsCursRec.col_name, rval, rempno);
END LOOP;
END LOOP;

END;