Description

Building SQL on the Fly

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from  w  w  w  .ja v a 2  s .  c o m
SQL> create table emp(
  2    empno    number(4,0),
  3    ename    varchar2(10),
  4    job      varchar2(9),
  5    mgr      number(4,0),
  6    hiredate date,
  7    sal      number(7,2),
  8    comm     number(7,2),
  9    deptno   number(2,0)
 10  );

Table created.
SQL>
SQL> insert into emp values(7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10);
SQL> insert into emp values(7698, 'BLAKE', 'MANAGER', 7839,to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30);
SQL>
SQL> create or replace function f_getEmp_tx(i_empNo NUMBER, i_column_tx VARCHAR2)
  2  return VARCHAR2
  3  is
  4       v_out_tx VARCHAR2(2000);
  5       v_sql_tx VARCHAR2(2000);
  6  begin
  7       v_sql_tx :=
  8           'select '||i_column_tx||
  9           ' from emp ' ||
 10           'where empNo='||i_empNo;
 11
 12       execute immediate v_sql_tx
 13        into v_out_tx;
 14       return v_out_tx;
 15  exception
 16       when others then return null;
 17  end;
 18  /

Function created.

SQL>

Related Topic