SQL> CREATE OR REPLACE FUNCTION search_value (
  2      p_column_name     IN  sys.user_tab_columns.column_name%TYPE
  3  ,   p_table_name      IN  sys.user_tables.table_name%TYPE
  4  ,   p_column_value    IN  NUMBER
  5  )
  6  RETURN VARCHAR2
  7  IS
  8      l_dummy         VARCHAR2(1);
  9      l_sql           VARCHAR2(4000) := 'SELECT ''T'' FROM sys.dual '
 10                                     || 'WHERE EXISTS ('
 11                                     || 'SELECT NULL FROM # WHERE ! = :b1)';
 12      l_return_value  VARCHAR2(1);
 13  BEGIN
 14      BEGIN
 15          SELECT NULL
 16          INTO   l_dummy
 17          FROM   sys.user_tables      ut
 18          WHERE  ut.table_name = UPPER(p_table_name);
 19      EXCEPTION
 20          WHEN NO_DATA_FOUND THEN
 21              RAISE_APPLICATION_ERROR (-20202
 22              ,                        'Table "'
 23                                       || USER
 24                                       || '"."'
 25                                       || UPPER(p_table_name)
 26                                       || '" does not exist.');
 27      END;
 28
 29      BEGIN
 30          SELECT NULL
 31          INTO   l_dummy
 32          FROM   sys.user_tab_columns utc
 33          WHERE  utc.table_name = UPPER(p_table_name)
 34          AND    utc.column_name = UPPER(p_column_name);
 35      EXCEPTION
 36          WHEN NO_DATA_FOUND THEN
 37              RAISE_APPLICATION_ERROR (-20204
 38              ,                        'Column "'
 39                                       || UPPER(p_column_name)
 40                                       || '" does not exist in table "'
 41                                       || USER
 42                                       || '"."'
 43                                       || UPPER(p_table_name)
 44                                       || '".');
 45      END;
 46
 47      l_sql := REPLACE(l_sql,'#',USER || '.' || p_table_name);
 48      l_sql := REPLACE(l_sql,'!',p_column_name);
 49      BEGIN
 50          EXECUTE IMMEDIATE l_sql
 51                       INTO l_return_value
 52                      USING p_column_value;
 53      EXCEPTION
 54          WHEN NO_DATA_FOUND THEN
 55              l_return_value := 'F';
 56      END;
 57      RETURN (l_return_value);
 58  END search_value;
 59  /
 
Function created.
 
SQL> COL does_exist FOR A10
SQL> SELECT search_value('empno','emp',7788) does_exist FROM DUAL
  2  /
 
DOES_EXIST
----------
T
 
SQL> SELECT search_value('empno','emp',12345) does_exist FROM DUAL
  2  /
 
DOES_EXIST
----------
F
 
SQL> SELECT search_value('empno','nonexistent_table',7788) does_exist FROM DUAL
  2  /
SELECT search_value('empno','nonexistent_table',7788) does_exist FROM DUAL
       *
ERROR at line 1:
ORA-20202: Table "SCOTT"."NONEXISTENT_TABLE" does not exist.
ORA-06512: at "SCOTT.SEARCH_VALUE", line 21
 
 
SQL> SELECT search_value('nonexistent_column','emp',7788) does_exist FROM DUAL
  2  /
SELECT search_value('nonexistent_column','emp',7788) does_exist FROM DUAL
       *
ERROR at line 1:
ORA-20204: Column "NONEXISTENT_COLUMN" does not exist in table "SCOTT"."EMP".
ORA-06512: at "SCOTT.SEARCH_VALUE", line 37
 
 
SQL>

create or replace function search_value(eno,tbl,num) return NUMBER as 

strQry VARCHAR2(150);
cntVar NUMBER;

begin

  strQry := 'select count(*) from '||tbl||' where '||eno||' = '||num;
  EXECUTE IMMEDIATE strQry into cntVar;

  return(cntVar);
end search_value;