Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create function check_ref_value
(
    table_name varchar2,
    code_value number,
    code_name  varchar2
) return number is
    l_query varchar2(32000 char);
    l_res   number;
begin
    l_query := 'select sign(count(1))'
            || ' from  ' || DBMS_ASSERT.SIMPLE_SQL_NAME(table_name)
            || ' where ' || DBMS_ASSERT.SIMPLE_SQL_NAME(code_name)
            || ' = :code_value';
    execute immediate l_query INTO l_res USING code_value;

    return l_res;
end;
/
CREATE TABLE abc (a, b, c) AS
SELECT 1, 42, 3.14159 FROM DUAL;
1 rows affected

create or replace function check_ref_value
(
    table_name varchar2,
    code_value number,
    code_name  varchar2
) return number is
    l_query varchar2(32000 char);
    l_res   number;
begin
    l_query := '
      BEGIN
        select sign(count(1))
        into :l_res
        from '|| DBMS_ASSERT.SIMPLE_SQL_NAME(table_name) ||'
        where '|| DBMS_ASSERT.SIMPLE_SQL_NAME(code_name) ||' = :code_value;
      END;
    ';

    execute immediate l_query
    using out l_res, in code_value;

    return l_res;
end;
/
SELECT CHECK_REF_VALUE('abc', 42, 'b') AS chk FROM DUAL;


CHK


1


SELECT CHECK_REF_VALUE('abc', 1.41421, 'c') AS chk FROM DUAL;


CHK


0


SELECT CHECK_REF_VALUE('abc', 42, '1 = 1 OR b') AS chk FROM DUAL;
ORA-44003: invalid SQL name
ORA-06512: at "SYS.DBMS_ASSERT", line 160
ORA-06512: at "FIDDLE_MWIIHBJWNLDQZKCRGALA.CHECK_REF_VALUE", line 10