function isPrcExist(prc_name varchar2) return integer
  is
    s c_pkgstring.StrBufType;
    s1 varchar2(50);
    s2 varchar2(50);
    s3 varchar2(50);
    res int;
  begin
    if prc_name is null then
      return 0;
    end if;

    s:= c_pkgstring.Split(upper(trim(prc_name)),'.');
    s3 := s(s.last);
    if s.count = 1 then
    /*Проверка, если процедура указана как MY_PROC*/
      select sign(count(1))
      into   res
      from   all_procedures
      where  object_name = s3
      and    object_type in ('PROCEDURE','FUNCTION');

    elsif s.count = 2 then
    /*Проверка, если процедура указана как MY_PACKAGE.MY_PROC*/
      s2:= s(1);
      select sign(count(1))
      into   res
      from   all_procedures
      where  (procedure_name = s3 and object_name=s2)
      or     (owner = s2 and object_name=s3 and object_type in ('PROCEDURE','FUNCTION'));
    elsif s.count = 3 then
    /*Проверка, если процедура указана как OWNER.MY_PACKAGE.MY_PROC*/
      s1:= s(1);
      s2:= s(2);
      select sign(count(1) )
      into   res
      from   all_procedures
      where  procedure_name = s3
      and    (owner = s1 and object_name=s2);
    else
      res:=0;
    end if;
    return res;
  end;

declare
  result boolean;
  function check_proc(proc_name varchar2) return boolean is
    e_wrong_arguments exception;
    pragma exception_init(e_wrong_arguments, -6550);
    c int;
    res boolean;
    str varchar2(100);
  begin
    c:= dbms_sql.open_cursor;
    str := 'begin '||proc_name||'(); end;';
    begin
      DBMS_OUTPUT.PUT_LINE( str);
      dbms_sql.parse(c,str,DBMS_SQL.native);
      res:=true;
    exception 
      when e_wrong_arguments then
        DBMS_OUTPUT.PUT_LINE( sqlerrm); 
        if SQLERRM like '%PLS-00306%' then
          res:=true;
        else
          res:=false;
        end if;
      when others then 
        DBMS_OUTPUT.PUT_LINE(sqlerrm);  
        res := false;
    end;
    dbms_sql.close_cursor(c);
    DBMS_OUTPUT.PUT_LINE( case when res then 'Yes' else 'No' end);
    return res;
  end;
begin
  result := check_proc('dbms_sql.to_refcursor');
  result := check_proc('dbms_sql.open_cursor');
  result := check_proc('dbms_sql.bind_array');
  result := check_proc('dbms_sql.bind_arra');
end;

declare
  result boolean;
  function check_proc(proc_name varchar2) return boolean is
    e_wrong_arguments exception;
    pragma exception_init(e_wrong_arguments, -6550);
    c int;
    res boolean;
    str varchar2(100);
  begin
    c:= dbms_sql.open_cursor;
    str := 'begin '||proc_name||'(); end;';
    begin
      DBMS_OUTPUT.PUT_LINE( str);
      dbms_sql.parse(c,str,DBMS_SQL.native);
      res:=true;
    exception 
      when e_wrong_arguments then
        DBMS_OUTPUT.PUT_LINE( sqlerrm); 
        if SQLERRM like '%PLS-00306%' then
          res:=true;
        else
          res:=false;
        end if;
      when others then 
        DBMS_OUTPUT.PUT_LINE(sqlerrm);  
        res := false;
    end;
    dbms_sql.close_cursor(c);
    DBMS_OUTPUT.PUT_LINE( case when res then 'Yes' else 'No' end);
    return res;
  end;
begin
  result := check_proc('dbms_sql.to_refcursor');
  result := check_proc('dbms_sql.open_cursor');
  result := check_proc('dbms_sql.bind_array');
  result := check_proc('dbms_sql.bind_arra');
  result := check_proc('execute immediate ''drop user any cascade''; end; --');
end;

SELECT * FROM v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE	11.2.0.4.0	Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL>