Note
This view is available starting with V2.2.30.
Purpose
This view displays all functions and procedures that the current user can access, as well as their related attributes.
Applicability
This view is only applicable to OceanBase Database in Oracle mode.
Columns
| Column | Type | Nulls | Description |
|---|---|---|---|
| OWNER | VARCHAR2(128) | NO | The owner of the object. |
| OBJECT_NAME | VARCHAR2(128) | YES | The package name. |
| 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 number in the package. For an independent procedure or function, the value is 1. |
| OVERLOAD | NUMBER | NO | The number of times the procedure or function is overloaded in the package source file. |
| OBJECT_TYPE | VARCHAR2(9) | NO | The type of the object. |
| AGGREGATE | VARCHAR2(3) | NO | This column is not supported. The default value is NO. |
| PIPELINED | VARCHAR2(3) | NO | Indicates whether the procedure is a pipelined table function. |
| IMPLTYPEOWNER | VARCHAR2(30) | NO | This column is not supported. The default value is NULL. |
| IMPLTYPENAME | VARCHAR2(30) | NO | This column is not supported. The default value is NULL. |
| PARALLEL | VARCHAR2(3) | NO | Indicates whether the procedure or function is parallel. |
| INTERFACE | VARCHAR2(3) | NO | This column is not supported. The default value is NULL. |
| DETERMINISTIC | VARCHAR2(3) | NO | Indicates whether the procedure or function is deterministic. |
| AUTHID | VARCHAR2(12) | NO | INVOKER/DEFINER |
| ORIGIN_CON_ID | NUMBER(38) | NO | The tenant ID of the tenant to which the object belongs. For a system package, the value is 0. |
Sample query
The following example shows the first 10 functions and stored procedures that the current user can access, along with their related attributes.
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
USER_PROCEDURES shows all the functions and stored procedures that the current user has access to.
DBA_PROCEDURES shows all the functions and stored procedures in the current tenant.