How to check if a stored procedure exist?

Something that worked for me!

SELECT text
FROM all_source
WHERE name = 'MY_SP_NAME'
ORDER BY line;

Alternatively you can try calling SP like this:

CALL MY_SP_NAME();

You might end up error like this, but that confirms you have SP defined there:

OCI Statement Execution failure.ORA-06553: PLS-306: wrong number or types of arguments in call to 'MY_SP_NAME'

Alternatives:

USER_PROCEDURES:

SELECT *
  FROM USER_PROCEDURES
 WHERE object_name = 'MY_STORED_PROCEDURE'

USER_OBJECTS:

SELECT *
  FROM USER_OBJECTS
 WHERE object_type = 'PROCEDURE'
   AND object_name = 'MY_STORED_PROCEDURE'

The only way to see if a procedure exists in the database is though querying DBA_OBJECTS. The disadvantage here is that only a dba has access to this view. Second best is using all_objects. ALL_OBJECTS shows you the objects for which you have somehow a privilege. USER_OBJECTS only shows you your own objects.