Query user_objects in PL/SQL : USER_OBJECTS « System Tables Views « Oracle PL / SQL






Query user_objects in PL/SQL

    
SQL>
SQL> create or replace procedure show_objects
  2      as
  3      begin
  4          for c1 in (select object_name, object_type
  5                       from user_objects
  6                     order by object_name ) loop
  7              dbms_output.put_line('Name: ' || c1.object_name || ' Type: ' || c1.object_type );
  8          end loop;
  9      end;
 10     /

Procedure created.

SQL> set serveroutput on
SQL> exec show_objects;
Name: ACCESS$ Type: TABLE
Name: AGGXMLIMP Type: TYPE
Name: AGGXQIMP Type: TYPE
Name: ALERT_QT Type: TABLE
Name: ALERT_QUE Type: QUEUE
Name: ALERT_QUE$1 Type: RULE
Name: ALERT_QUE$1 Type: RULE SET
Name: ALERT_QUE_N Type: RULE SET
Name: ALERT_QUE_R Type: RULE SET
Name: old_values209_T Type: TYPE

PL/SQL procedure successfully completed.

SQL>
SQL> column object_name format a20
SQL> column object_type format a20
SQL>
SQL> select object_name, object_type
  2  from user_objects
  3  where rownum < 50
  4  order by object_name;

OBJECT_NAME          OBJECT_TYPE
-------------------- --------------------
BOOTSTRAP$           TABLE
CCOL$                TABLE
CDEF$                TABLE
CLU$                 TABLE
COL$                 TABLE
CON$                 TABLE
C_COBJ#              CLUSTER
C_FILE#_BLOCK#       CLUSTER
C_OBJ#               CLUSTER
C_TS#                CLUSTER
C_USER#              CLUSTER

OBJECT_NAME          OBJECT_TYPE
-------------------- --------------------
FET$                 TABLE
FILE$                TABLE
ICOL$                TABLE
IND$                 TABLE
I_CCOL1              INDEX
I_CDEF1              INDEX
I_CDEF2              INDEX
I_CDEF4              INDEX
I_COBJ#              INDEX
I_COL2               INDEX
I_COL3               INDEX

OBJECT_NAME          OBJECT_TYPE
-------------------- --------------------
I_CON1               INDEX
I_CON2               INDEX
I_FILE#_BLOCK#       INDEX
I_FILE1              INDEX
I_FILE2              INDEX
I_IND1               INDEX
I_OBJ#               INDEX
I_OBJ1               INDEX
I_OBJ2               INDEX
I_OBJ3               INDEX
I_PROXY_DATA$        INDEX

OBJECT_NAME          OBJECT_TYPE
-------------------- --------------------
I_PROXY_ROLE_DATA$_1 INDEX
I_TAB1               INDEX
I_TS#                INDEX
I_TS1                INDEX
I_UNDO1              INDEX
I_UNDO2              INDEX
I_USER1              INDEX
OBJ$                 TABLE
PROXY_DATA$          TABLE
PROXY_ROLE_DATA$     TABLE
SEG$                 TABLE

OBJECT_NAME          OBJECT_TYPE
-------------------- --------------------
TAB$                 TABLE
TS$                  TABLE
UET$                 TABLE
UNDO$                TABLE
USER$                TABLE

49 rows selected.

SQL>
SQL>

   
    
    
  








Related examples in the same category

1.Query user_objects table
2.Query USER_OBJECTS table by object name
3.Query object_type, object_name from user_objects
4.Query user_objects table for all procedure
5.list all stored procedures: 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY'
6.Query a view in user_objects table for a view just created
7.Query user_objects for invalid package body
8.Query user-objects for stored procedure
9.Query user_objects for trigger
10.Query user_objects for invalid view
11.Query all INVALID objects from user_objects table
12.Get code for all procedure, function and package from user_objects
13.Query user_objects table for stored procedure before and after recompile
14.If procedure is valid
15.A procedure with dependencies
16.Check package status
17.Check new created tables in user_objects
18.Finding, Validating, and Describing Packages
19.Get object id for created table
20.Show the procedure is marked invalid **
21.Show the status of a procedure
22.To find out what procedures and functions you have created, use the following SQL query: