How to check if a stored procedure exist?

Question

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 name='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.

Answer 1

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'

Answer 2

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'

Answer 3

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.

Answer 4

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; "

Answer 5

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.

Answer 6

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;  

Answer 7

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