Manage stored procedures and functions

PL supports viewing and deleting stored procedures and functions.

View stored procedures and functions

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

• DBA_SOURCE: all stored procedures and functions.

• ALL_SOURCE: all stored procedures and functions that the current user can access.

• 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 for deleting a stored procedure is as follows:

DROP PROCEDURE [user.]Procudure_name;

The command for deleting a function is as follows:

DROP FUNCTION [user.]Function_name;

Here is an example:

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