Manage stored procedures and functions

PL allows you to view and drop stored procedures and functions.

View stored procedures and functions

Stored procedures and functions are stored in the database dictionaries. The following are several common dictionary views:

• DBA_SOURCE: displays all stored procedures and functions.

• ALL_SOURCE: displays all stored procedures and functions that can be accessed by the current user.

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

Example: View all the stored procedures of the HR user in 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 log_message stored procedure owned by the current user in 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

Drop stored procedures and functions

The syntax for dropping a stored procedure is as follows:

DROP PROCEDURE [user.]Procedure_name;

The syntax for dropping a function is as follows:

DROP FUNCTION [user.]Function_name;

Here is an example:

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