<b>
Question:</b>
I wana create dynamic SQL, i mean create function in which i pass
parameters ColumnName and Table name and it Return me maximum Number
<b>Answer:</b>
SQL> CREATE OR REPLACE function test(p_colName in varchar2,
2 p_tabname in varchar2) return number
3 is
4 v_maxnum Number(10);
5 v_sql VARCHAR2(100);
6 begin
7 v_sql := 'Select MAx('|| p_ColName || ') from '|| p_TabName ;
8
9 EXECUTE IMMEDIATE v_sql INTO v_maxnum;
10
11 return(v_MaxNum);
12 end;
13 /
Function created.
SQL> select test('empno','emp') from dual;
TEST('EMPNO','EMP')
-------------------
7934
SQL> select test('sal','emp') from dual;
TEST('SAL','EMP')
-----------------
5000
SQL>
Max value using dynamic SQL of any column
Who is online
Users browsing this forum: No registered users and 6 guests