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. It has the same columns as ALL_PROCEDURES, but without the OWNER field.
Applicability
This view is applicable only to OceanBase Database in Oracle-compatible 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 order in which the procedure or function appears in the package. For an independent procedure or function, this value is 1. |
| OVERLOAD | NUMBER | NO | The number of times the procedure or function 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 parallel-enabled. |
| 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 | The authentication mode, which can be INVOKER or 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, this value is 0. |
Sample query
Query all functions and procedures owned by the current user and their associated 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