Purpose
This view displays all functions and procedures owned by the current user, as well as their associated properties. Its columns are the same as those of the ALL_PROCEDURES view, except that it does not have the OWNER column. The USER_PROCEDURES view displays all functions and procedures owned by the current user, as well as their associated properties. Its columns are the same as those of the ALL_PROCEDURES view, except that it does not have the OWNER column.
Applicability
This view applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| 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 | The tenant ID of the tenant to which the object belongs. If the object is a system package, the 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
Query all functions and stored procedures accessible to the current user: ALL_PROCEDURES
Query all functions and stored procedures in the current tenant: DBA_PROCEDURES