How to check if a stored procedure exist?

I have searched the net and I've found a post that uses the following snippet to check if a stored procedure exists:

select * 
  from USER_SOURCE 
 where type='PROCEDURE' 
   and n开发者_如何学Came='my_stored_procedure.' 

Is there any other way to check if a procedure exists?

Edited to add:

Before posting SQL Server ways, please I'm looking for ORACLE ways.

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'

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'

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.

Execute the query below in SQL*PLUS, ODBC Test,...

SELECT text FROM all_source WHERE name='MY_PROCEDURE' ORDER BY line

where MY_PROCEDURE is the stored procedure name.

Below is sample output:

Get Data All: "TEXT" "PROCEDURE Usp_Get_Blob
"(
"P_DOC_ID INT,
"P_DOC_TEXT OUT BLOB)
"as
"begin
" select B1 into p_doc_text
" from blobtest
" where ID = p_doc_id;
"end; "

I was not able to find stored procedure with any of the methods above.

Reason: stored procedure was inside package. Oracle uses packages to gather several stored procedures in one module.

This will display the stored procedure and its contents stored in the table.

select
   name c1,
   text c2
from
   dba_source
where
   name = upper('procedure_name')
order by
   line asc;  

select * from USER_SOURCE where type='PROCEDURE' and name='my_stored_procedure.'