SQL> CREATE OR REPLACE PROCEDURE get_column_value (
  2      p_schema        IN  VARCHAR2
  3  ,   p_table_name    IN  VARCHAR2
  4  ,   p_column_number IN  NUMBER
  5  )
  6  IS
  7      l_column_name       VARCHAR2(30);
  8      l_sql               VARCHAR2(113) := 'SELECT TO_CHAR(';
  9      TYPE l_rc           IS REF CURSOR;
 10      c_cursor            l_rc;
 11      l_column_value      VARCHAR2(255);
 12  BEGIN
 13      SELECT dtc.column_name
 14      INTO   l_column_name
 15      FROM   sys.dba_tab_columns      dtc
 16      WHERE  dtc.owner      = UPPER(p_schema)
 17      AND    dtc.table_name = UPPER(p_table_name)
 18      AND    dtc.column_id  = p_column_number;
 19  
 20      l_sql := l_sql
 21               || l_column_name
 22               || ') FROM '
 23               || p_schema
 24               || '.'
 25               || p_table_name;
 26               
 27      OPEN c_cursor FOR l_sql;
 28      LOOP
 29          FETCH c_cursor INTO l_column_value;
 30          EXIT WHEN c_cursor%NOTFOUND;
 31          DBMS_OUTPUT.PUT_LINE(l_column_value);
 32      END LOOP;
 33  END get_column_value;
 34  /
  
Procedure created.
  
SQL> EXEC get_column_value('SCOTT','EMP',1);
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
  
PL/SQL procedure successfully completed.
  
SQL> EXEC get_column_value('SCOTT','DEPT',2);
ACCOUNTING
RESEARCH
SALES
OPERATIONS
  
PL/SQL procedure successfully completed.
  
SQL> EXEC get_column_value('SCOTT','EMP', 50000);
BEGIN get_column_value('SCOTT','EMP', 50000); END;
  
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SCOTT.GET_COLUMN_VALUE", line 13
ORA-06512: at line 1
  
  
SQL>