Purpose
Displays all functions and procedures that the current user can access and the associated properties. The ALL_PROCEDURES view displays all functions and procedures accessible to the current user as well as their properties.
Applicability
This view applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
| OWNER | VARCHAR2(128) | NO | The object owner. |
|---|---|---|---|
| Column | Type | Nullable? | Description |
| OWNER | VARCHAR2(128) | NO | The owner of the object. |
| OBJECT_NAME | VARCHAR2(128) | YES | The name of the package. |
| PROCEDURE_NAME | VARCHAR2(128) | NO | The name of the procedure or function. |
| OBJECT_ID | NUMBER | NO | The object ID. |
| SUBPROGRAM_ID | NUMBER | NO | The sequence of appearance of the object in the package. For an independent procedure or function, the value is 1. |
| OVERLOAD | NUMBER | NO | The sequence number of overloading of the object in the package source file. |
| OBJECT_TYPE | VARCHAR2(9) | NO | The type of the object. |
| AGGREGATE | VARCHAR2(3) | NO | At present, this column is not supported and its value is NO by default. |
| PIPELINED | VARCHAR2(3) | NO | Indicates whether the procedure is a pipelined table function. |
| IMPLTYPEOWNER | VARCHAR2(30) | NO | At present, this column is not supported and its value is NULL by default. |
| IMPLTYPENAME | VARCHAR2(30) | NO | At present, this column is not supported and its value is NULL by default. |
| PARALLEL | VARCHAR2(3) | NO | Indicates whether the procedure or function is parallel-enabled. |
| INTERFACE | VARCHAR2(3) | NO | At present, this column is not supported and its value is NULL by default. |
| DETERMINISTIC | VARCHAR2(3) | NO | Indicates whether the procedure or function is declared to be deterministic. |
| AUTHID | VARCHAR2(12) | NO | Indicates whether authorization of the current user or the definer is required. Valid values: CURRENT_USER and DEFINER |
| ORIGIN_CON_ID | NUMBER(38) | NO | The tenant ID of the tenant to which the object belongs. If the object is a system package, the value is 0. |
Sample query
Query all functions and procedures accessible to the current user and their related attributes, and display the first 10 records.
obclient [SYS]> SELECT * FROM SYS.ALL_PROCEDURES WHERE ROWNUM <=10;
The query result is as follows:
+-------+--------------------+---------------------+-----------+---------------+----------+-------------+-----------+-----------+---------------+--------------+----------+-----------+---------------+---------+---------------+
| OWNER | OBJECT_NAME | PROCEDURE_NAME | OBJECT_ID | SUBPROGRAM_ID | OVERLOAD | OBJECT_TYPE | AGGREGATE | PIPELINED | IMPLTYPEOWNER | IMPLTYPENAME | PARALLEL | INTERFACE | DETERMINISTIC | AUTHID | ORIGIN_CON_ID |
+-------+--------------------+---------------------+-----------+---------------+----------+-------------+-----------+-----------+---------------+--------------+----------+-----------+---------------+---------+---------------+
| SYS | JOB_UTILS | INSERT_DATE | 500021 | 1 | NULL | PACKAGE | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| SYS | JOB_UTILS | INSERT_DATE_PROGRAM | 500021 | 2 | NULL | PACKAGE | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| SYS | USERLOGIN | NULL | 500153 | 1 | NULL | PROCEDURE | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| SYS | GET_SALARY_BY_DEPT | NULL | 500158 | 1 | NULL | FUNCTION | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| SYS | GET_EMPLOYEE_INFO | NULL | 500172 | 1 | NULL | PROCEDURE | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| SYS | PKG1 | PRO1 | 500177 | 1 | 1 | PACKAGE | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| SYS | PKG1 | PRO1 | 500177 | 2 | 2 | PACKAGE | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| SYS | PKG1 | PRO1_OUT | 500177 | 3 | 1 | PACKAGE | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| SYS | PKG1 | PRO1_OUT | 500177 | 4 | 2 | PACKAGE | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| SYS | PKG1 | PRO1_INOUT | 500177 | 5 | 1 | PACKAGE | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
+-------+--------------------+---------------------+-----------+---------------+----------+-------------+-----------+-----------+---------------+--------------+----------+-----------+---------------+---------+---------------+
10 rows in set
References
Query all functions and stored procedures owned by the current user: USER_PROCEDURES
Query all functions and stored procedures in the current tenant: DBA_PROCEDURES