Note
This view is available starting with V2.2.30.
Purpose
This view displays all functions and procedures available to the current tenant and their relevant attributes. It contains the same columns as the ALL_PROCEDURES view.
Applicability
This view applies only to OceanBase Database in Oracle mode.
Columns
| Field name | Type | Nullable? | 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 is overloaded in the package source file. |
| OBJECT_TYPE | VARCHAR2(9) | NO | The type of the object. |
| AGGREGATE | VARCHAR2(3) | NO | This field is not supported. The default value of this field is NO. |
| PIPELINED | VARCHAR2(3) | NO | Indicates whether the procedure is a pipelined table function. |
| IMPLTYPEOWNER | VARCHAR2(30) | NO | This field is not supported. The default value of this field is NULL. |
| IMPLTYPENAME | VARCHAR2(30) | NO | This field is not supported. The default value of this field is NULL. |
| PARALLEL | VARCHAR2(3) | NO | Indicates whether the procedure or function is parallel. |
| INTERFACE | VARCHAR2(3) | NO | This field is not supported. The default value of this field is NULL. |
| DETERMINISTIC | VARCHAR2(3) | NO | Indicates that the procedure or function is deterministic. |
| AUTHID | VARCHAR2(12) | NO | |
| 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 records of all functions and stored procedures available to the current tenant, along with 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
ALL_PROCEDURES: lists all functions and stored procedures available to the current user.
USER_PROCEDURES: lists all functions and stored procedures owned by the current user.