f_table_access

f_table_access.sql

declare
  function f_table_access(fi_tbl varchar2,
                          fi_owner varchar2 default null)
  return varchar2 as
    v_table   varchar2(100);
    v_retval  varchar2(1);
    v_command varchar2(100);
  begin
    if fi_owner is not null then
      v_table := fi_owner||'.'||fi_tbl;
    else
      v_table := fi_tbl;
    end if;
  
    v_command := 'begin select ''Y'' into :v_retval from '||v_table||' where rownum=1; end;';
  
    begin
      execute immediate v_command using out v_retval;
    exception
      when others then
        v_retval := 'N';
    end;
    
    return v_retval;
  end f_table_access;
  
  procedure p_test is
    v_status  varchar2(1);
  begin
    v_status := f_table_access('NAMES');
    dbms_output.put_line(v_status);

    v_status := f_table_access('NAMES', 'TURBOV21');
    dbms_output.put_line(v_status);

    v_status := f_table_access('HELP');
    dbms_output.put_line(v_status);

    v_status := f_table_access('HELP', 'SYSTEM');
    dbms_output.put_line(v_status);
  end p_test;
begin
  p_test();
end;