Note
This view is available starting with V2.2.30.
Purpose
This view displays all functions and procedures owned by the current user and their associated attributes. The columns are the same as those in the ALL_PROCEDURES view, except that the OWNER column is removed.
Applicability
This view is applicable only to OceanBase Database in Oracle mode.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| 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 a standalone procedure or function, this 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 column is not supported. The default value of this column 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 of this column is NULL. |
| IMPLTYPENAME | VARCHAR2(30) | NO | This column is not supported. The default value of this column 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 of this column is NULL. |
| DETERMINISTIC | VARCHAR2(3) | NO | Indicates whether the procedure or function is deterministic. |
| AUTHID | VARCHAR2(12) | NO | INVOKER or DEFINER |
| ORIGIN_CON_ID | NUMBER(38) | NO | The tenant ID of the tenant to which the object belongs. For system packages, this value is 0. |
Sample query
Query all functions and procedures owned by the current user and their related attributes.
obclient [SYS]> SELECT * FROM SYS.USER_PROCEDURES;
The query result is as follows:
+----------------------+---------------------+-----------+---------------+----------+-------------+-----------+-----------+---------------+--------------+----------+-----------+---------------+---------+---------------+
| OBJECT_NAME | PROCEDURE_NAME | OBJECT_ID | SUBPROGRAM_ID | OVERLOAD | OBJECT_TYPE | AGGREGATE | PIPELINED | IMPLTYPEOWNER | IMPLTYPENAME | PARALLEL | INTERFACE | DETERMINISTIC | AUTHID | ORIGIN_CON_ID |
+----------------------+---------------------+-----------+---------------+----------+-------------+-----------+-----------+---------------+--------------+----------+-----------+---------------+---------+---------------+
| JOB_UTILS | INSERT_DATE | 500021 | 1 | NULL | PACKAGE | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| JOB_UTILS | INSERT_DATE_PROGRAM | 500021 | 2 | NULL | PACKAGE | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| USERLOGIN | NULL | 500153 | 1 | NULL | PROCEDURE | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| GET_SALARY_BY_DEPT | NULL | 500158 | 1 | NULL | FUNCTION | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| GET_EMPLOYEE_INFO | NULL | 500172 | 1 | NULL | PROCEDURE | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| PKG1 | PRO1 | 500177 | 1 | 1 | PACKAGE | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| PKG1 | PRO1 | 500177 | 2 | 2 | PACKAGE | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| PKG1 | PRO1_OUT | 500177 | 3 | 1 | PACKAGE | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| PKG1 | PRO1_OUT | 500177 | 4 | 2 | PACKAGE | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| PKG1 | PRO1_INOUT | 500177 | 5 | 1 | PACKAGE | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| PKG1 | PRO1_INOUT | 500177 | 6 | 2 | PACKAGE | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| MY_REC_TYPES | INIT_REC | 500189 | 1 | NULL | PACKAGE | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| F_POLICY | NULL | 500196 | 1 | NULL | FUNCTION | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| POLICY_GROUP_FUNC | NULL | 500208 | 1 | NULL | FUNCTION | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| SET_SALES_CTX_PKG | SET_REGION | 500213 | 1 | NULL | PACKAGE | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| REGION_CTX_PKG | SET_REGION | 500217 | 1 | NULL | PACKAGE | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| REGION_CTX_PKG | SET_ROLE | 500217 | 2 | NULL | PACKAGE | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| POLICY_SELECTOR_FUNC | NULL | 500227 | 1 | NULL | FUNCTION | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
| DEPT_CTX_PKG | SET_DEPT | 500233 | 1 | NULL | PACKAGE | NO | NO | NULL | NULL | NO | NO | NO | DEFINER | 1004 |
+----------------------+---------------------+-----------+---------------+----------+-------------+-----------+-----------+---------------+--------------+----------+-----------+---------------+---------+---------------+
19 rows in set
References
View all functions and stored procedures accessible to the current user: ALL_PROCEDURES
View all functions and stored procedures in the current tenant: DBA_PROCEDURES