passing dynamic values to FOR loop

[pando]

Hi

you have to use dynamic sql

SQL> CREATE OR REPLACE PROCEDURE DYNAMIC(P_TABLE_NAME VARCHAR2, P_COLUMN_NAME VARCHAR2)
  2  IS
  3    VAR_COL_1          VARCHAR2(100);
  4    TYPE REF_CURSOR IS REF CURSOR;
  5    C1                 REF_CURSOR;
  6    VAR_ENAME          VARCHAR2(36);
  7  BEGIN
  8    SELECT COLUMN_NAME 
  9      INTO VAR_COL_1 
 10      FROM USER_TAB_COLUMNS 
 11     WHERE TABLE_NAME=P_TABLE_NAME
 12       AND COLUMN_NAME=P_COLUMN_NAME;
 13    OPEN C1 FOR 'SELECT '||VAR_COL_1||' FROM '||P_TABLE_NAME;
 14    LOOP
 15    FETCH C1 INTO VAR_ENAME;
 16    EXIT WHEN C1%NOTFOUND;
 17    DBMS_OUTPUT.PUT_LINE(VAR_ENAME);
 18    END LOOP; 
 19    CLOSE C1;
 20  END;
 21  /

Procedure created.

SQL> EXEC DYNAMIC('EMP', 'ENAME')
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL procedure successfully completed.

SQL> EXEC DYNAMIC('EMP', 'EMPNO')
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

PL/SQL procedure successfully completed.

However I think you dont need th SELECT into part since we are passing column name as well

CREATE OR REPLACE PROCEDURE DYNAMIC(P_TABLE_NAME VARCHAR2, P_COLUMN_NAME VARCHAR2)
IS
  TYPE REF_CURSOR IS REF CURSOR;
  C1                 REF_CURSOR;
  VAR_ENAME          VARCHAR2(36);
BEGIN
  OPEN C1 FOR 'SELECT '||P_COLUMN_NAME||' FROM '||P_TABLE_NAME;
  LOOP
  FETCH C1 INTO VAR_ENAME;
  EXIT WHEN C1%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE(VAR_ENAME);
  END LOOP; 
  CLOSE C1;
END;
/

regards