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.