Check to see if a procedure already exists 1

[lawlerpat]

Need to be able to check if a Stored procedure already exists.
I am familiar with SQL Server code as in
Begin
If Exists (select name from sysObjects where
name = 'procName' and type = 'P)
Drop procedure 'procName'
End
Can I replicate that in concept Oracle.
I don't plan to replace the proc so "Create or Replace" will not work.

Thanks in advance.

 

[lewisp]

You can do the same with USER_OBJECTS or ALL_OBJECTS.

 

[lawlerpat]

OK, thanks that table user_objects table will allow me to determine if the SP exists but now I have found that I am unable to drop a procedure from within an IF block.
Here is more or less what I would like to do.
*****************
Declare v_exists numeric;
Begin
Select 1 into v_exists
From user_objects
Where object_name = 'my_proc_name'
If v_exists = 1 then
dbms.dropProc 'my_prop_name';
End if;
End;

I have made up the dbms.dropProc function? does it exist by another name.

 

[sem]

If you want to drop procedure before recreating it you may consider CREATE OR REPLACE PROCEDURE command. To drop procedure from pl/sql you need dynamic sql:

If v_exists = 1 then
   execute immediate 'drop procedure my_prop_name';
End if;

Don't forget that DDL statement causes implicit commit.


Regards, Dima

http://seminihin.narod.ru

 

[lawlerpat]

Thank you that exactly what I needed.
Patrick.