Oracle PL/SQL/System Tables Views/USER OBJECTS

Материал из SQL эксперт
Перейти к: навигация, поиск

A procedure with dependencies

   <source lang="sql">

SQL> SQL> CREATE TABLE books ( 

 2    isbn      CHAR(10) PRIMARY KEY,
 3    category  VARCHAR2(20),
 4    title     VARCHAR2(100),
 5    num_pages NUMBER,
 6    price     NUMBER,
 7    copyright NUMBER(4),
 8    emp1   NUMBER,
 9    emp2   NUMBER,
10    emp3   NUMBER
11  );

Table created. SQL> SQL> CREATE TABLE myTable 

 2    (num_col    NUMBER
 3    ,char_col   VARCHAR2(60));

Table created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE RecordThreeemp AS 

 2    CURSOR c_Books IS SELECT *  FROM books;
 3  BEGIN
 4    FOR v_BookRecord in c_Books LOOP
 5
 6      IF Threeemp(v_BookRecord.ISBN) THEN
 7        INSERT INTO myTable (char_col) VALUES(v_BookRecord.title || " has three emp!");
 8      END IF;
 9    END LOOP;
10  END RecordThreeemp;
11  /

Procedure created. SQL> SQL> SQL> SQL> COLUMN object_name FORMAT a20 SQL> SQL> SELECT object_name, status FROM user_objects WHERE object_name IN ("THREEemp", "RECORDTHREEemp"); no rows selected SQL> SQL> ALTER TABLE books MODIFY 

 2    (title VARCHAR2(150)  -- Increase size of title column
 3  );

Table altered. SQL> SQL> SELECT object_name, status 

 2    FROM user_objects
 3    WHERE object_name IN ("THREEEMP", "RECORDTHREEEMP");

no rows selected SQL> SQL> SQL> BEGIN 

 2    RecordThreeemp;
 3  END;
 4  /

PL/SQL procedure successfully completed. SQL> SQL> SELECT object_name, status 

 2    FROM user_objects
 3    WHERE object_name IN ("THREEEMP", "RECORDTHREEEMP");

no rows selected SQL> SQL> SQL> drop table myTable; Table dropped. SQL> drop table books; Table dropped. 



 </source>


Check new created tables in user_objects

   <source lang="sql">

SQL> create table myTable ( x int ); Table created. SQL> create table myTable2 ( x int ); Table created. SQL> insert into myTable values ( 1 ); 1 row created. SQL> insert into myTable2 values ( 1 ); 1 row created. SQL> SQL> SQL> select object_name, object_id 

 2    from user_objects
 3   where object_name in ("MYTABLE","MYTABLE2")
 4  /

SQL> SQL> SQL> SQL> drop table myTable; Table dropped. SQL> drop table myTable2; Table dropped. SQL> 



 </source>


Check package status

   <source lang="sql">

SQL> SQL> create table t ( x int ); Table created. SQL> SQL> create or replace view v as select * from t; View created. SQL> SQL> create or replace procedure p 

 2  as
 3  begin
 4          for x in ( select * from t )
 5          loop
 6                  null;
 7          end loop;
 8  end;
 9  /

SQL> SQL> create or replace function f return number 

 2  as
 3          countValue number;
 4  begin
 5          select count(*) into countValue from t;
 6          return countValue;
 7  end;
 8  /

Function created. SQL> SQL> SQL> create or replace procedure p2 

 2  as
 3  begin
 4          p;
 5  end;
 6  /

SQL> SQL> SQL> SQL> SQL> create or replace package p1 

 2  as
 3          procedure p;
 4  end;
 5  /

SQL> SQL> create or replace package body p1 

 2  as
 3  procedure p
 4  as
 5  begin
 6          for x in ( select * from t )
 7          loop
 8                  null;
 9          end loop;
10  end;
11  end p1;
12  /

SQL> SQL> create or replace package p2 

 2  as
 3          procedure p;
 4  end;
 5  /

SQL> SQL> create or replace package body p2 

 2  as
 3  procedure p
 4  as
 5  begin
 6          p1.p;
 7  end;
 8  end p2;
 9  /

SQL> SQL> select object_name, object_type, status 

 2    from user_objects
 3    where rownum < 10
 4  /

OBJECT_NAME

OBJECT_TYPE STATUS

-------

MYSTATS INDEX VALID MYSTATS TABLE VALID S SEQUENCE VALID

OBJECT_NAME

OBJECT_TYPE STATUS

-------

P_ADD_ITEMS PROCEDURE INVALID WORKING_CUSTOMERS TABLE VALID ADDTUPLE3 PROCEDURE INVALID

OBJECT_NAME

OBJECT_TYPE STATUS

-------

P_ADD_ORDERS PROCEDURE INVALID P_ADD_PROD PROCEDURE INVALID UPDATE_PRODUCT_PRICE PROCEDURE INVALID

9 rows selected. SQL> 



 </source>


Finding, Validating, and Describing Packages

   <source lang="sql">

SQL> SQL> COLUMN object_name FORMAT A10 SQL> SELECT object_name 

 2  ,       object_type
 3  ,       last_ddl_time
 4  ,       timestamp
 5  ,       status
 6  FROM    user_objects
 7  WHERE   object_name IN ("PIPELINED","PF");

no rows selected SQL> 



 </source>


Get code for all procedure, function and package from user_objects

   <source lang="sql">

SQL> SQL> set termout off SQL> set heading off SQL> set feedback off SQL> set linesize 50 SQL> SQL> select "@getcode " || object_name 

 2  from user_objects
 3  where object_type in ( "PROCEDURE", "FUNCTION", "PACKAGE" )
 4  /

@getcode DONTCOUNTSP @getcode DO_COMMIT @getcode MY_TO_DATE @getcode EXITFUNC @getcode FACTORIAL @getcode EMP_CHANGE_S @getcode EMP_DEPT_PROCS @getcode ERASE @getcode GETEMPBLDGNAME @getcode ASSIGNEMPTOBLDG @getcode DEBUG @getcode ADD_MON @getcode RAISE @getcode ASSERT @getcode MAKE_MYTABLE @getcode WRITE_NAME @getcode HELLO @getcode STATE_PACKAGE @getcode GET_NEXT_OBJECT_TO_COMPILE @getcode COMPILE_SCHEMA @getcode PRINT_TABLE @getcode SHOW_SPACE @getcode DEBUG_TIMER @getcode P @getcode PEOPLE @getcode P_ADD_PROD @getcode P_ADD_EMPS @getcode P_ADD_ORDERS @getcode MEASURE_USAGE @getcode SHOW_IOT_SPACE @getcode FILE_DUMP @getcode COMPANY_LISTING @getcode PIVOT @getcode VIRTUAL_TABLE @getcode REMOVE_CONSTANTS SQL> SQL> SQL> set termout off SQL> set heading off SQL> set feedback on SQL> SQL> select "@" || object_name 

 2  from user_objects
 3  where object_type in ( "PROCEDURE", "FUNCTION", "PACKAGE" )
 4  /

@DONTCOUNTSP @DO_COMMIT @MY_TO_DATE @EXITFUNC @FACTORIAL @EMP_CHANGE_S @EMP_DEPT_PROCS @ERASE @GETEMPBLDGNAME @ASSIGNEMPTOBLDG @DEBUG @ADD_MON @RAISE @ASSERT @MAKE_MYTABLE @WRITE_NAME @HELLO @STATE_PACKAGE @GET_NEXT_OBJECT_TO_COMPILE @COMPILE_SCHEMA @PRINT_TABLE @SHOW_SPACE @DEBUG_TIMER @P @PEOPLE @P_ADD_PROD @P_ADD_EMPS @P_ADD_ORDERS @MEASURE_USAGE @SHOW_IOT_SPACE @FILE_DUMP @COMPANY_LISTING @PIVOT @VIRTUAL_TABLE @REMOVE_CONSTANTS 35 rows selected. SQL> SQL> set heading on SQL> set feedback on SQL> set linesize 130 SQL> set termout on SQL> SQL> 


 </source>


Get object id for created table

   <source lang="sql">

SQL> SQL> create table t 

 2   (c1 number primary key,
 3    c2 number not null )
 4  ;

Table created. SQL> SQL> create sequence s1; Sequence created. SQL> SQL> insert into t values(s1.nextval, s1.nextval+10); 1 row created. SQL> / 1 row created. SQL> / 1 row created. SQL> / 1 row created. SQL> / 1 row created. SQL> commit; Commit complete. SQL> SQL> select object_id from user_objects where object_name = "T"; 

    20955

1 row selected. SQL> SQL> SQL> drop table t; Table dropped. SQL> SQL> drop sequence s1; Sequence dropped. 



 </source>


If procedure is valid

   <source lang="sql">

SQL> CREATE TABLE EMP( 

 2      EMPNO NUMBER(4) NOT NULL,
 3      ENAME VARCHAR2(10),
 4      JOB VARCHAR2(9),
 5      MGR NUMBER(4),
 6      HIREDATE DATE,
 7      SAL NUMBER(7, 2),
 8      COMM NUMBER(7, 2),
 9      DEPTNO NUMBER(2)
10  );

Table created. SQL> create or replace procedure P1 is 

 2   v_cnt number;
 3   begin
 4       select count(*) into v_cnt from emp where empno > 0;
 5   end;
 6  /

SQL> SQL> select object_name, status from user_objects where object_name in ("P1","P2","P3","P4"); Enter... P1 VALID P1 INVALID P2 INVALID P2 INVALID P3 INVALID P4 INVALID

6 rows selected. SQL> SQL> select object_name, status from user_objects where object_name in ("P1","P2","P3","P4"); Enter... P1 VALID P1 INVALID P2 INVALID P2 INVALID P3 INVALID P4 INVALID

6 rows selected. SQL> SQL> drop table emp; Table dropped. 



 </source>


list all stored procedures: "PROCEDURE", "FUNCTION", "PACKAGE", "PACKAGE BODY"

   <source lang="sql">

SQL> SQL> select object_name, object_type 

 2      from user_objects
 3     where object_type in ( "PROCEDURE", "FUNCTION","PACKAGE", "PACKAGE BODY" )
 4     and rownum < 50;

OBJECT_NAME OBJECT_TYPE

-------------------

STANDARD PACKAGE STANDARD PACKAGE BODY DBMS_STANDARD PACKAGE DBMS_REGISTRY PACKAGE DBMS_REGISTRY PACKAGE BODY DBMS_REGISTRY_SERVER PACKAGE XML_SCHEMA_NAME_PRESENT PACKAGE XML_SCHEMA_NAME_PRESENT PACKAGE BODY UTL_RAW PACKAGE DBMS_ADVISOR PACKAGE PLITBLM PACKAGE OBJECT_NAME OBJECT_TYPE

-------------------

SYS_STUB_FOR_PURITY_ANALYSIS PACKAGE PIDL PACKAGE PIDL PACKAGE BODY DIANA PACKAGE DIANA PACKAGE BODY DIUTIL PACKAGE DIUTIL PACKAGE BODY PSTUBT PROCEDURE PSTUB PROCEDURE SUBPTXT2 PROCEDURE SUBPTXT PROCEDURE OBJECT_NAME OBJECT_TYPE

-------------------

DBMS_PICKLER PACKAGE DBMS_PICKLER PACKAGE BODY DBMS_JAVA_TEST PACKAGE DBMS_JAVA_TEST PACKAGE BODY UTL_FILE PACKAGE UTL_FILE PACKAGE BODY UTL_RAW PACKAGE BODY UTL_TCP PACKAGE UTL_TCP PACKAGE BODY UTL_INADDR PACKAGE UTL_INADDR PACKAGE BODY OBJECT_NAME OBJECT_TYPE

-------------------

UTL_SMTP PACKAGE UTL_SMTP PACKAGE BODY UTL_HTTP PACKAGE UTL_HTTP PACKAGE BODY UTL_URL PACKAGE UTL_URL PACKAGE BODY UTL_ENCODE PACKAGE UTL_ENCODE PACKAGE BODY UTL_GDK PACKAGE UTL_GDK PACKAGE BODY DBMS_LOB PACKAGE OBJECT_NAME OBJECT_TYPE

-------------------

DBMS_LOB PACKAGE BODY UTL_SYS_COMPRESS PACKAGE UTL_SYS_COMPRESS PACKAGE BODY UTL_COMPRESS PACKAGE UTL_COMPRESS PACKAGE BODY 49 rows selected. SQL> 


 </source>


Query all INVALID objects from user_objects table

   <source lang="sql">

SQL> SQL> column object_name format a30 SQL> column tablespace_name format a30 SQL> column object_type format a20 SQL> column status format a1 SQL> SQL> break on object_type skip 1 SQL> SQL> select object_type, object_name, 

 2         decode( status, "INVALID", "*", "" ) status,
 3         decode( object_type,
 4                 "TABLE",
 5                 (select tablespace_name
 6                    from user_tables
 7                   where table_name = object_name),
 8                 "TABLE PARTITION",
 9                 (select tablespace_name
10                    from user_tab_partitions
11                   where partition_name = subobject_name),
12                 "INDEX",
13                 (select tablespace_name
14                    from user_indexes
15                   where index_name = object_name),
16                 "INDEX PARTITION",
17                 (select tablespace_name
18                    from user_ind_partitions
19                   where partition_name = subobject_name),
20                 "LOB",
21                 (select tablespace_name
22                    from user_segments
23                   where segment_name = object_name),
24                 null ) tablespace_name
25    from user_objects a
26   order by object_type, object_name
27  /

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

EVALUATION CONTEXT AQ$_EMP_CHANGES_V

FUNCTION ADD_MON 

                    DONTCOUNTSP

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

FUNCTION EXITFUNC 

                    FACTORIAL

                    GETEMPBLDGNAME                 *

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

FUNCTION MY_TO_DATE 

                    PIVOT

                    RAISE

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

FUNCTION REMOVE_CONSTANTS 

                    VIRTUAL_TABLE

INDEX COMPILE_SCHEMA_TMP_PK OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

INDEX SYS_C005784 SYSTEM 

                    SYS_C005787

SYSTEM 

                    SYS_C005826

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

SYSTEM INDEX SYS_C005827 SYSTEM 

                    SYS_IOT_TOP_15816

SYSTEM 

                    SYS_IOT_TOP_16247

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

SYSTEM INDEX SYS_IOT_TOP_16249 SYSTEM 

                    SYS_IOT_TOP_16251

SYSTEM 

                    SYS_IOT_TOP_16254

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

SYSTEM

LOB SYS_LOB0000016238C00034$$ SYSTEM

PACKAGE DEBUG

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

PACKAGE EMP_DEPT_PROCS * 

                    PEOPLE                         *

                    STATE_PACKAGE

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

PACKAGE BODY DEBUG * 

                    EMP_DEPT_PROCS                 *

                    PEOPLE                         *

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

PROCEDURE ASSERT 

                    ASSIGNEMPTOBLDG                *

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

PROCEDURE COMPANY_LISTING * 

                    COMPILE_SCHEMA                 *

                    DEBUG_TIMER                    *

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

PROCEDURE DO_COMMIT * 

                    EMP_CHANGE_S                   *

                    ERASE                          *

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

PROCEDURE FILE_DUMP 

                    GET_NEXT_OBJECT_TO_COMPILE     *

                    HELLO

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

PROCEDURE MAKE_MYTABLE 

                    MEASURE_USAGE                  *

                    P

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

PROCEDURE PRINT_TABLE 

                    P_ADD_EMPS                     *

                    P_ADD_ORDERS                   *

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

PROCEDURE P_ADD_PROD * 

                    SHOW_IOT_SPACE

                    SHOW_SPACE

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

PROCEDURE WRITE_NAME *

QUEUE AQ$_EMP_CHANGES_E 

                    EMP_DEPT_CHANGES

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

RULE SET EMP_DEPT_CHANGES_N 

                    EMP_DEPT_CHANGES_R


OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

SEQUENCE AQ$_EMP_CHANGES_N

TABLE AQ$_EMP_CHANGES_G 

                    AQ$_EMP_CHANGES_H

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

TABLE AQ$_EMP_CHANGES_I 

                    AQ$_EMP_CHANGES_S

SYSTEM 

                    AQ$_EMP_CHANGES_T

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

TABLE COMPILE_SCHEMA_TMP 

                    DEPT_AND_EMP

SYSTEM 

                    DONE

SYSTEM OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

TABLE EMPS_NT 

                    EMP_CHANGES

SYSTEM 

                    EMP_REG

SYSTEM OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

TABLE MYTABLE_SESSION 

                    SESS_EVENT

                    SQL_AREA_TMP

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

TABLE SYS_IOT_OVER_16251 SYSTEM 

                    TEMP_EMP

                    UPPER_ENAME

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

TRIGGER EMP_NAME_CHANGE * 

                    IO_BIFER_DEPT_EMP_VIEW         *

                    LOGON_TRIGGER

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

TRIGGER LOG_SHUTDOWN * 

                    LOG_STARTUP                    *


OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

TYPE ADDRESS 

                    ELEMENTTYPE

                    EMPLOYEES_LIST

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

TYPE EMPLOYEE_TYPE 

                    EMP_CHG

                    EMP_TAB_TYPE

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

TYPE EMP_TYPE 

                    NEW_PERSON

                    PERSON

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

TYPE RESULTTYPE 

                    ROWTYPE

                    SHAPE

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

TYPE SUPER_TYPE 

                    VC2TAB

                    VIRTUAL_TABLE_TYPE

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

TYPE BODY ADDRESS * 

                    NEW_PERSON

VIEW AQ$EMP_CHANGES OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

VIEW AQ$EMP_CHANGES_R 

                    AQ$EMP_CHANGES_S

                    AQ$_EMP_CHANGES_F

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

VIEW DEPARTMENT_10 * 

                    DEPT_EMP_VIEW                  *

                    EMP_PUBLIC_DATA                *

OBJECT_TYPE OBJECT_NAME S

------------------------------ -

TABLESPACE_NAME

VIEW VIEW_T *


102 rows selected. SQL> column status format a10 SQL> 


 </source>


Query a view in user_objects table for a view just created

   <source lang="sql">

SQL> SQL> SQL> create table t( 

 2      id number,
 3      data varchar2(200) );

Table created. SQL> SQL> SQL> create or replace view view_t as 

 2      select id view_id, data view_data
 3        from t;

View created. SQL> SQL> select object_name, status 

 2      from user_objects
 3     where object_name = "VIEW_T";

OBJECT_NAME STATUS

-------

VIEW_T VALID SQL> SQL> SQL> drop table t; Table dropped. SQL> 


 </source>


Query object_type, object_name from user_objects

   <source lang="sql">

SQL> SQL> SQL> describe user_objects 

Name                                                                                                      Null?    Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
OBJECT_NAME                                                                                                        VARCHAR2(128)
SUBOBJECT_NAME                                                                                                     VARCHAR2(30)
OBJECT_ID                                                                                                          NUMBER
DATA_OBJECT_ID                                                                                                     NUMBER
OBJECT_TYPE                                                                                                        VARCHAR2(19)
CREATED                                                                                                            DATE
LAST_DDL_TIME                                                                                                      DATE
TIMESTAMP                                                                                                          VARCHAR2(19)
STATUS                                                                                                             VARCHAR2(7)
TEMPORARY                                                                                                          VARCHAR2(1)
GENERATED                                                                                                          VARCHAR2(1)
SECONDARY                                                                                                          VARCHAR2(1)

SQL> SQL> select object_type, object_name 

 2  from user_objects
 3  where rownum < 50
 4  order by object_type, object_name;

OBJECT_TYPE OBJECT_NAME

--------------------------------------------------------------------------------------------------------------------------------

CLUSTER C_COBJ# CLUSTER C_FILE#_BLOCK# CLUSTER C_OBJ# CLUSTER C_TS# CLUSTER C_USER# INDEX I_CCOL1 INDEX I_CDEF1 INDEX I_CDEF2 INDEX I_CDEF4 INDEX I_COBJ# INDEX I_COL2 OBJECT_TYPE OBJECT_NAME

--------------------------------------------------------------------------------------------------------------------------------

INDEX I_COL3 INDEX 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 OBJECT_TYPE OBJECT_NAME

--------------------------------------------------------------------------------------------------------------------------------

INDEX I_PROXY_DATA$ INDEX I_PROXY_ROLE_DATA$_1 INDEX I_TAB1 INDEX I_TS# INDEX I_TS1 INDEX I_UNDO1 INDEX I_UNDO2 INDEX I_USER1 TABLE BOOTSTRAP$ TABLE CCOL$ TABLE CDEF$ OBJECT_TYPE OBJECT_NAME

--------------------------------------------------------------------------------------------------------------------------------

TABLE CLU$ TABLE COL$ TABLE CON$ TABLE FET$ TABLE FILE$ TABLE ICOL$ TABLE IND$ TABLE OBJ$ TABLE PROXY_DATA$ TABLE PROXY_ROLE_DATA$ TABLE SEG$ OBJECT_TYPE OBJECT_NAME

--------------------------------------------------------------------------------------------------------------------------------

TABLE TAB$ TABLE TS$ TABLE UET$ TABLE UNDO$ TABLE USER$ 49 rows selected. SQL> SQL> column object_name format a30; SQL> SQL> 


 </source>


Query user_objects for invalid package body

   <source lang="sql">

SQL> SQL> set pause off SQL> set heading off SQL> set feedback off SQL> set linesize 80 SQL> SQL> select "alter package "" || object_name || "" compile body;" 

 2    from user_objects
 3   where object_type = "PACKAGE BODY"
 4     and status = "INVALID"
 5  /

alter package "EMP_DEPT_PROCS" compile body; alter package "DEBUG" compile body; alter package "PEOPLE" compile body; SQL> SQL> set heading on SQL> set feedback on SQL> SQL> select "show errors package body " || object_name 

 2    from user_objects
 3   where object_type = "PACKAGE BODY"
 4     and status = "INVALID"
 5  /

"SHOWERRORSPACKAGEBODY"||OBJECT_NAME

show errors package body EMP_DEPT_PROCS show errors package body DEBUG show errors package body PEOPLE 3 rows selected. SQL> SQL> 


 </source>


Query user_objects for invalid view

   <source lang="sql">

SQL> SQL> set heading off SQL> set feedback off SQL> set linesize 80 SQL> SQL> select "alter view "" || object_name || "" compile;" 

 2    from user_objects
 3   where object_type = "VIEW"
 4     and status = "INVALID"
 5  /

alter view "EMP_PUBLIC_DATA" compile; alter view "VIEW_T" compile; alter view "DEPT_EMP_VIEW" compile; alter view "DEPARTMENT_10" compile; SQL> spool off not spooling currently SQL> set heading on SQL> set feedback on SQL> SQL> select "show errors view " || object_name 

 2    from user_objects
 3   where object_type = "VIEW"
 4     and status = "INVALID"
 5  /

"SHOWERRORSVIEW"||OBJECT_NAME

show errors view EMP_PUBLIC_DATA show errors view VIEW_T show errors view DEPT_EMP_VIEW show errors view DEPARTMENT_10 4 rows selected. SQL> SQL> 


 </source>


Query user-objects for stored procedure

   <source lang="sql">

SQL> SQL> set heading off SQL> set feedback off SQL> set linesize 80 SQL> select "alter procedure "" || object_name || "" compile;" 

 2    from user_objects
 3   where object_type = "PROCEDURE"
 4     and status = "INVALID"
 5  /

alter procedure "DO_COMMIT" compile; alter procedure "EMP_CHANGE_S" compile; alter procedure "ERASE" compile; alter procedure "ASSIGNEMPTOBLDG" compile; alter procedure "WRITE_NAME" compile; alter procedure "GET_NEXT_OBJECT_TO_COMPILE" compile; alter procedure "COMPILE_SCHEMA" compile; alter procedure "DEBUG_TIMER" compile; alter procedure "P_ADD_PROD" compile; alter procedure "P_ADD_EMPS" compile; alter procedure "P_ADD_ORDERS" compile; alter procedure "MEASURE_USAGE" compile; alter procedure "COMPANY_LISTING" compile; SQL> SQL> set heading on SQL> set feedback on SQL> SQL> select "show errors procedure " || object_name 

 2    from user_objects
 3   where object_type = "PROCEDURE"
 4     and status = "INVALID"
 5  /

"SHOWERRORSPROCEDURE"||OBJECT_NAME

show errors procedure DO_COMMIT show errors procedure EMP_CHANGE_S show errors procedure ERASE show errors procedure ASSIGNEMPTOBLDG show errors procedure WRITE_NAME show errors procedure GET_NEXT_OBJECT_TO_COMPILE show errors procedure COMPILE_SCHEMA show errors procedure DEBUG_TIMER show errors procedure P_ADD_PROD show errors procedure P_ADD_EMPS show errors procedure P_ADD_ORDERS "SHOWERRORSPROCEDURE"||OBJECT_NAME

show errors procedure MEASURE_USAGE show errors procedure COMPANY_LISTING 13 rows selected. SQL> SQL> 


 </source>


Query user_objects for trigger

   <source lang="sql">

SQL> SQL> set heading off SQL> set feedback off SQL> set linesize 80 SQL> SQL> select "alter TRIGGER "" || object_name || "" compile;" 

 2    from user_objects
 3   where object_type = "TRIGGER"
 4    and status = "INVALID"
 5  /

alter TRIGGER "LOG_SHUTDOWN" compile; alter TRIGGER "LOG_STARTUP" compile; alter TRIGGER "EMP_NAME_CHANGE" compile; alter TRIGGER "IO_BIFER_DEPT_EMP_VIEW" compile; SQL> SQL> set heading on SQL> set feedback on SQL> SQL> select "show errors trigger " || object_name 

 2    from user_objects
 3   where object_type = "TRIGGER"
 4    and status = "INVALID"
 5  /

"SHOWERRORSTRIGGER"||OBJECT_NAME

show errors trigger LOG_SHUTDOWN show errors trigger LOG_STARTUP show errors trigger EMP_NAME_CHANGE show errors trigger IO_BIFER_DEPT_EMP_VIEW 4 rows selected. SQL> 


 </source>


Query user_objects in PL/SQL

   <source lang="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> 


 </source>


Query user_objects table

   <source lang="sql">

SQL> SQL> select object_name, object_id from user_objects; OBJECT_NAME OBJECT_ID

----------

MYTABLE_SESSION 15960 DONTCOUNTSP 16019 DO_COMMIT 16000 OBJECT_NAME OBJECT_ID

----------

DONE 16006 MY_TO_DATE 16018 EXITFUNC 16020 FACTORIAL 16021 EMP_CHANGE_S 16023 LOG_SHUTDOWN 16086 ADDRESS 16114 LOG_STARTUP 16083 ADDRESS 16115 EMP_PUBLIC_DATA 16091 EMP_NAME_CHANGE 16095 EMP_DEPT_PROCS 16109 EMP_DEPT_PROCS 16110 DEBUG 16183 ERASE 16181 GETEMPBLDGNAME 16178 ASSIGNEMPTOBLDG 16165 DEBUG 16185 ADD_MON 16184 RAISE 16187 ASSERT 16188 MAKE_MYTABLE 16215 WRITE_NAME 16216 HELLO 16219 EMP_CHG 16237 EMP_CHANGES 16238 SYS_C005784 16241 SYS_LOB0000016238C00034$$ 16239 AQ$_EMP_CHANGES_S 16242 SYS_C005787 16243 AQ$_EMP_CHANGES_V 16246 AQ$_EMP_CHANGES_T 16247 AQ$EMP_CHANGES_S 16245 AQ$_EMP_CHANGES_N 16244 SYS_IOT_TOP_16247 16248 AQ$_EMP_CHANGES_H 16249 EMPLOYEES_LIST 14844 EMPLOYEE_TYPE 14841 VIEW_T 14933 STATE_PACKAGE 14802 SHAPE 14931 DEPT_EMP_VIEW 14853 IO_BIFER_DEPT_EMP_VIEW 15100 DEPARTMENT_10 14945 COMPILE_SCHEMA_TMP_PK 15343 GET_NEXT_OBJECT_TO_COMPILE 15337 COMPILE_SCHEMA 15339 COMPILE_SCHEMA_TMP 15342 PRINT_TABLE 15344 SHOW_SPACE 15346 DEBUG_TIMER 15423 VC2TAB 15365 P 15805 PEOPLE 15427 PEOPLE 15428 P_ADD_PROD 15572 P_ADD_EMPS 15746 P_ADD_ORDERS 15758 MEASURE_USAGE 15811 SYS_IOT_TOP_15816 15817 UPPER_ENAME 15816 SHOW_IOT_SPACE 15830 SYS_IOT_TOP_16249 16250 SYS_IOT_OVER_16251 16252 AQ$_EMP_CHANGES_G 16251 SYS_IOT_TOP_16251 16253 AQ$_EMP_CHANGES_I 16254 SYS_IOT_TOP_16254 16255 AQ$_EMP_CHANGES_E 16260 AQ$_EMP_CHANGES_F 16259 AQ$EMP_CHANGES 16257 EMP_DEPT_CHANGES_R 16262 EMP_DEPT_CHANGES_N 16263 AQ$EMP_CHANGES_R 16264 EMP_DEPT_CHANGES 16261 DEPT_AND_EMP 16299 FILE_DUMP 16351 EMP_REG 16294 EMP_TYPE 16297 EMP_TAB_TYPE 16298 EMPS_NT 16300 SYS_C005826 16301 SYS_C005827 16302 SUPER_TYPE 16436 SESS_EVENT 16377 NEW_PERSON 16549 PERSON 16449 NEW_PERSON 16550 LOGON_TRIGGER 16373 VIRTUAL_TABLE_TYPE 16647 ROWTYPE 16642 ELEMENTTYPE 16641 RESULTTYPE 16643 COMPANY_LISTING 16515 PIVOT 16644 VIRTUAL_TABLE 16648 T1 16999 OBJECT_NAME OBJECT_ID

----------

REMOVE_CONSTANTS 16650 SQL_AREA_TMP 16651 TEMP_EMP 16944 T2 17000 104 rows selected. SQL> SQL> drop table t1; Table dropped. SQL> drop table t2; Table dropped. SQL> -- 


 </source>


Query USER_OBJECTS table by object name

   <source lang="sql">

SQL> -- create demo table SQL> create table Employee( 

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) 

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) 

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) 

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) 

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) 

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) 

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) 

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) 

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee 

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION

---------- ---------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> select object_name, status 

 2      from user_objects
 3     where object_name = "EMPLOYEE";

OBJECT_NAME STATUS

-------

EMPLOYEE VALID SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee 

 2  /

Table dropped. SQL> SQL> 



 </source>


Query user_objects table for all procedure

   <source lang="sql">

SQL> SQL> SQL> select object_name, status 

 2      from user_objects
 3     where object_type = "PROCEDURE" and rownum < 100;

OBJECT_NAME STATUS

-------

PSTUBT VALID PSTUB VALID SUBPTXT2 VALID SUBPTXT VALID ODCIINDEXINFOFLAGSDUMP VALID ODCIINDEXINFODUMP VALID ODCIPREDINFODUMP VALID ODCIQUERYINFODUMP VALID ODCICOLINFODUMP VALID ODCISTATSOPTIONSDUMP VALID ODCIENVDUMP VALID OBJECT_NAME STATUS

-------

ODCIINDEXALTEROPTIONDUMP VALID ODCIINDEXCALLPROPERTYDUMP VALID ODCITABFUNCINFODUMP VALID LOGMNR_KRVRDLUID3 VALID LOGMNR_KRVRDREPDICT3 VALID DBMS_LOGMNR_FFVTOLOGMNRT VALID LOGMNR_KRVRDA_TEST_APPLY VALID DBMS_LOGMNR_OCTOLOGMNRT VALID LOGMNR_GTLO3 VALID AW_DROP_PROC VALID LOAD_UNDO_STAT VALID OBJECT_NAME STATUS

-------

RESET_UNDO_STAT VALID DBMS_FEATURE_PARTITION_USER VALID DBMS_FEATURE_PARTITION_SYSTEM VALID DBMS_FEATURE_PLSQL_NATIVE VALID DBMS_FEATURE_RAC VALID DBMS_FEATURE_TEST_PROC_1 VALID DBMS_FEATURE_TEST_PROC_2 VALID DBMS_FEATURE_TEST_PROC_3 VALID DBMS_FEATURE_TEST_PROC_4 VALID DBMS_FEATURE_TEST_PROC_5 VALID DBMS_FEATURE_REGISTER_ALLFEAT VALID OBJECT_NAME STATUS

-------

DBMS_FEATURE_REGISTER_ALLHWM VALID VALIDATE_CONTEXT VALID SET_TABLESPACE VALID EMP_LOOKUP INVALID MY_PROC VALID SHOW_EMP INVALID SWAP VALID LOG_MESSAGE INVALID INSERT_INTO_T INVALID FOO INVALID INSERT_NUMBERS INVALID OBJECT_NAME STATUS

-------

OUT_PARMS VALID THREE_PARMS VALID DEFAULT_VALUES VALID THREE_PARAMS VALID SHOW_OBJECTS VALID SET_JETSON_CONTEXT VALID 50 rows selected. SQL> 


 </source>


Query user_objects table for stored procedure before and after recompile

   <source lang="sql">

SQL> SQL> SQL> set serveroutput on SQL> SQL> CREATE OR REPLACE PROCEDURE P2 AS 

 2  BEGIN
 3    DBMS_OUTPUT.PUT_LINE("Inside P2!");
 4  END P2;
 5  /

Procedure created. SQL> SQL> CREATE OR REPLACE PROCEDURE P1 AS 

 2  BEGIN
 3    DBMS_OUTPUT.PUT_LINE("Inside P1!");
 4    P2;
 5  END P1;
 6  /

Procedure created. SQL> SQL> SQL> SELECT object_name, object_type, status 

 2    FROM user_objects
 3    WHERE object_name IN ("P1", "P2");

OBJECT_NAME

OBJECT_TYPE STATUS

-------

P1 PROCEDURE VALID P2 PROCEDURE VALID

SQL> SQL> ALTER PROCEDURE P2 COMPILE; Procedure altered. SQL> SQL> SELECT object_name, object_type, status 

 2    FROM user_objects
 3    WHERE object_name IN ("P1", "P2");

OBJECT_NAME

OBJECT_TYPE STATUS

-------

P1 PROCEDURE INVALID P2 PROCEDURE VALID

SQL> 


 </source>


Show the procedure is marked invalid **

   <source lang="sql">

SQL> SQL> COL object_name FORMAT A15 SQL> COL status FORMAT A10 SQL> SQL> SELECT object_name, status FROM user_objects WHERE object_name = "COMPILEERROR"; OBJECT_NAME STATUS

----------

COMPILEERROR VALID 1 row selected. 



 </source>


Show the status of a procedure

   <source lang="sql">

SQL> CREATE TABLE books ( 

 2    isbn      CHAR(10) PRIMARY KEY,
 3    category  VARCHAR2(20),
 4    title     VARCHAR2(100),
 5    num_pages NUMBER,
 6    price     NUMBER,
 7    copyright NUMBER(4),
 8    emp1   NUMBER,
 9    emp2   NUMBER,
10    emp3   NUMBER
11  );

SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE BOOK_INS ( 

 2     i_ISBN VARCHAR2,
 3     i_Category VARCHAR2,
 4     i_Title VARCHAR2,
 5     i_Num_Pages NUMBER,
 6     i_Price VARCHAR2,
 7     i_Copyright NUMBER,
 8     i_emp1 NUMBER,
 9     i_emp2 NUMBER,
10     i_emp3 NUMBER)
11  IS
12  BEGIN
13
14     INSERT INTO BOOKS (isbn, category, title, num_pages,price, copyright, emp1, emp2, emp3)
15      VALUES (i_ISBN, i_Category, i_Title, i_Num_Pages,i_Price, i_Copyright, i_emp1, i_emp2, i_emp3);
16
17  EXCEPTION
18     WHEN OTHERS
19     THEN
20        DBMS_OUTPUT.PUT_LINE("Error: "||sqlerrm);
21  END;
22  /

SP2-0804: Procedure created with compilation warnings SQL> SQL> COL object_name FORMAT A30 SQL> COL status FORMAT A10 SQL> SQL> SELECT object_name, status FROM user_objects WHERE object_name = "BOOK_INS"; OBJECT_NAME STATUS

----------

BOOK_INS VALID 1 row selected. SQL> SQL> PROMPT SQL> PROMPT SQL> SQL> SQL> ALTER PROCEDURE book_ins COMPILE PLSQL_WARNINGS="ERROR:07202"; Warning: Procedure altered with compilation errors. SQL> drop table books; Table dropped. 



 </source>


To find out what procedures and functions you have created, use the following SQL query:

   <source lang="sql">

SQL> SQL> select object_type, object_name 

 2  from user_objects
 3  where object_type = "PROCEDURE"
 4     or object_type = "FUNCTION";

OBJECT_TYPE OBJECT_NAME

--------------------------------------------------------------------------------------------------------------------------------

PROCEDURE P_ADD_ITEMS PROCEDURE ADDTUPLE3 PROCEDURE P_ADD_ORDERS PROCEDURE P_ADD_PROD PROCEDURE UPDATE_PRODUCT_PRICE FUNCTION CIRCLE_AREA FUNCTION AVERAGE_PRODUCT_PRICE PROCEDURE NCLOB_EXAMPLE PROCEDURE ADDTUPLE1 PROCEDURE ADDTUPLE2 FUNCTION MONTH_GENERATOR_PIPED FUNCTION MONTH_GENERATOR FUNCTION ORD_GENERATOR_PIPED FUNCTION ORD_GENERATOR PROCEDURE PRINT_PRODUCTS FUNCTION GETPRODUCTTAXIN FUNCTION F_CORP_PWD_VFY PROCEDURE ADDSTUDENT PROCEDURE PROMOTION_REVIEW_2 PROCEDURE PROMOTION_REVIEW_1 PROCEDURE PROMOTION_REVIEW_4 PROCEDURE SUBMULTISET_EXAMPLE PROCEDURE CUSTOMERS_ASSOCIATIVE_ARRAY PROCEDURE MULTISET_EXAMPLE PROCEDURE CARDINALITY_EXAMPLE PROCEDURE MEMBER_OF_EXAMPLE PROCEDURE EQUAL_EXAMPLE PROCEDURE IN_EXAMPLE PROCEDURE SET_EXAMPLE PROCEDURE IS_A_SET_EXAMPLE PROCEDURE IS_EMPTY_EXAMPLE PROCEDURE ERASE_EXAMPLE PROCEDURE INITIALIZE_BLOB PROCEDURE INITIALIZE_CLOB PROCEDURE READ_BLOB_EXAMPLE PROCEDURE READ_CLOB_EXAMPLE PROCEDURE WRITE_EXAMPLE PROCEDURE APPEND_EXAMPLE PROCEDURE COMPARE_EXAMPLE PROCEDURE COPY_EXAMPLE PROCEDURE TEMPORARY_LOB_EXAMPLE PROCEDURE MY_RAISE PROCEDURE DW_STYLE PROCEDURE OLTP_STYLE FUNCTION GET_ROW_CNT PROCEDURE PARALLEL_PROCEDURE FUNCTION F PROCEDURE NROWS_AT_A_TIME PROCEDURE SAL_CHECK PROCEDURE P2 FUNCTION GET_VALUE_STATIC FUNCTION GET_VALUE_DYN PROCEDURE ROW_AT_A_TIME PROCEDURE ROW_FETCH_ROW_SELECT PROCEDURE ARRAY_FETCH_HEAP_INSERT PROCEDURE EXPLICIT PROCEDURE IMPLICIT PROCEDURE P_ADD_EMPS PROCEDURE GEN_CUSTOMER PROCEDURE INSERT_BLOB_PROC FUNCTION CCN_DECRYPT PROCEDURE SEARCHED_CASE PROCEDURE PROC1 PROCEDURE PROC2 PROCEDURE AUTONOMOUS_INSERT PROCEDURE NONAUTONOMOUS_INSERT FUNCTION EXT FUNCTION EMP_COUNT PROCEDURE LITERALS PROCEDURE BINDING PROCEDURE UPDATE_EMP PROCEDURE EASY_AS_THAT FUNCTION BINARY_AND FUNCTION PIPE_DATE PROCEDURE P0 PROCEDURE REPORT_SAL_ADJUSTMENT PROCEDURE REPORT_SAL_ADJUSTMENT2 PROCEDURE REPORT_SAL_ADJUSTMENT3 PROCEDURE REPORT_SAL_ADJUSTMENT4 PROCEDURE XXX PROCEDURE P4 PROCEDURE P3 PROCEDURE SHOW_SAL PROCEDURE PRC_0002 PROCEDURE PRC_0001 PROCEDURE PRC_0003 PROCEDURE PRC_0004 PROCEDURE PRC_0005 PROCEDURE PRC_0006 PROCEDURE PRC_0007 PROCEDURE PRC_0008 PROCEDURE PRC_0009 PROCEDURE PRC_0010 PROCEDURE PRC_0011 PROCEDURE PRC_0012 PROCEDURE PRC_0013 PROCEDURE PRC_0014

304 rows selected. SQL> SQL> 



 </source>
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL/System_Tables_Views/USER_OBJECTS&oldid=2239»