In my opinion, there's one (packaged) function too many - you don't need it as everything can be done
right here.
Here's an example based on Scott's schema. First, create a function which accepts employee number (EMPNO) and column name as parameters and returns that column's value.
Code:
SQL> create or replace function get_value
2 (par_empno in number, par_col_name in char)
3 return char
4 is
5 l_str varchar2(500);
6 retval varchar2(30);
7 begin
8 l_str := 'select ' || par_col_name || ' from emp where empno = ' || par_empno;
9 execute immediate (l_str) into retval;
10 return (retval);
11 end;
12 /
Function created.
SQL>
A few examples:
Code:
SQL> select get_value(7369, 'ENAME') result from dual;
RESULT
--------------------------------------------------------------------------------------
SMITH
SQL> select get_value(7369, 'SAL') result from dual;
RESULT
--------------------------------------------------------------------------------------
800
SQL> select get_value(7369, 'HIREDATE') result from dual;
RESULT
--------------------------------------------------------------------------------------
17.12.1980
SQL>
See? You don't have to select the whole row in order to get just a part of it.