Purpose
Displays all functions and procedures available in the current tenant and their related attributes. The columns of this view are the same as those of the ALL_PROCEDURES view. The DBA_PROCEDURES view displays all available functions and procedures in the database as well as their properties. Its columns are the same as those of the ALL_PROCEDURES view.
Applicability
This view applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
| OWNER | VARCHAR2(128) | NO | The owner of the object |
|---|---|---|---|
| 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 | At present, this column is not supported and its value is NULL by default. |
Sample query
Query the first 10 functions and procedures available in the current tenant and their related attributes.
obclient [SYS]> SELECT * FROM SYS.DBA_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 that the current user can access: ALL_PROCEDURES
Query all functions and stored procedures owned by the current user: USER_PROCEDURES