Note
This view is available starting with V2.2.30.
Purpose
This view displays all functions and procedures available in the current tenant, along with their related attributes. The columns are the same as those in ALL_PROCEDURES.
Applicability
This view is applicable only to OceanBase Database in Oracle-compatible mode.
Columns
| Column | 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 order in which the subprogram appears in the package. For an independent procedure or function, this value is 1. |
| OVERLOAD | NUMBER | NO | The number of times the subprogram has been 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 enabled for parallel execution. |
| 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 declared to be deterministic. |
| AUTHID | VARCHAR2(12) | NO |
|
| ORIGIN_CON_ID | NUMBER(38) | NO | The tenant ID of the tenant to which the object belongs. If the object is a system package, this value is 0. |
Sample query
Query all functions and procedures available in the current tenant, along with their related attributes, and display the first 10 records.
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 accessible to the current user: ALL_PROCEDURES
Query all functions and stored procedures owned by the current user: USER_PROCEDURES