Manage stored procedures and functions

PL/SQL supports viewing and deleting stored procedures and functions.

View stored procedures and functions

Stored procedures and functions are stored in the database dictionary. The following are some commonly used dictionary views:

• DBA_SOURCE: All stored procedures and functions.

• ALL_SOURCE: All stored procedures and functions accessible to the current user.

• USER_SOURCE: All stored procedures and functions owned by the current user.

Example: View all stored procedures of the HR user by using the DBA_SOURCE view.

obclient> SELECT OWNER,NAME,TYPE FROM DBA_SOURCE
      WHERE OWNER='HR' AND TYPE='PROCEDURE';
+-------+-------------+-----------+
| OWNER | NAME        | TYPE      |
+-------+-------------+-----------+
| HR    | AAA         | PROCEDURE |
| HR    | USERLOGIN   | PROCEDURE |
| HR    | LOG_MESSAGE | PROCEDURE |
+-------+-------------+-----------+

Example: View the content of the stored procedure log_message owned by the current user by using the USER_SOURCE view.

obclient> SELECT TEXT FROM USER_SOURCE WHERE NAME='log_message' AND
     TYPE='PROCEDURE'\G
*************************** 1. row ***************************
TEXT: PROCEDURE log_message(p_message varchar2)
    AS
BEGIN
    INSERT INTO logtable VALUES ( user, sysdate, p_message );
    COMMIT;
END log_message
1 row in set

Delete stored procedures and functions

The command format for deleting a stored procedure is as follows:

DROP PROCEDURE [user.]Procudure_name;

The command format for deleting a function is as follows:

DROP FUNCTION [user.]Function_name;

Here are some examples:

obclient> DROP PROCEDURE log_message;
Query OK, 0 rows affected