Oracle PL/SQL Tutorial/System Tables Data Dictionary/user objects

Behavior of dependent objects.

   <source lang="sql">

SQL> SQL> COLUMN object_name FORMAT a20 SQL> SQL> CREATE TABLE simple_table (f1 NUMBER); Table created. SQL> SQL> CREATE OR REPLACE PACKAGE Dependee AS

 2    PROCEDURE Example(p_Val IN NUMBER);
 3  END Dependee;
 4  /

Package created. SQL> SQL> CREATE OR REPLACE PACKAGE BODY Dependee AS

 2    PROCEDURE Example(p_Val IN NUMBER) IS
 3    BEGIN
 4       INSERT INTO simple_table VALUES (p_Val);
 5    END Example;
 6  END Dependee;
 7  /

Package body created. SQL> SQL> CREATE OR REPLACE PROCEDURE Depender(p_Val IN NUMBER) AS

 2  BEGIN
 3    Dependee.Example(p_Val + 1);
 4  END Depender;
 5  /

Procedure created. SQL> SQL> SELECT object_name, object_type, status FROM user_objects WHERE object_name IN ("DEPENDER", "DEPENDEE","SIMPLE_TABLE"); OBJECT_NAME OBJECT_TYPE STATUS

---

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

DEPENDEE PACKAGE VALID DEPENDEE PACKAGE BODY VALID DEPENDER PROCEDURE VALID SIMPLE_TABLE TABLE VALID 4 rows selected. SQL> SQL> CREATE OR REPLACE PACKAGE BODY Dependee AS

 2    PROCEDURE Example(p_Val IN NUMBER) IS
 3    BEGIN
 4      INSERT INTO simple_table VALUES (p_Val - 1);
 5    END Example;
 6  END Dependee;
 7  /

Package body created. SQL> SQL> SELECT object_name, object_type, status FROM user_objects WHERE object_name IN ("DEPENDER", "DEPENDEE","SIMPLE_TABLE"); OBJECT_NAME OBJECT_TYPE STATUS

---

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

DEPENDEE PACKAGE VALID DEPENDEE PACKAGE BODY VALID DEPENDER PROCEDURE VALID SIMPLE_TABLE TABLE VALID 4 rows selected. SQL> SQL> DROP TABLE simple_table; Table dropped. SQL> SQL> SELECT object_name, object_type, status FROM user_objects WHERE object_name IN ("DEPENDER", "DEPENDEE","SIMPLE_TABLE"); OBJECT_NAME OBJECT_TYPE STATUS

---

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

DEPENDEE PACKAGE VALID DEPENDEE PACKAGE BODY INVALID DEPENDER PROCEDURE VALID 3 rows selected. SQL></source>

Check function/procedure status

   <source lang="sql">

SQL> SQL> create or replace function getArea (i_rad NUMBER)

 2  return NUMBER
 3  is
 4  begin
 5    return 3.14*(i_rad**2);
 6  end;
 7  /

Function created. SQL> SQL> SQL> SQL> alter function getArea compile; Function altered. SQL> SQL> select status

 2  from user_objects
 3  where object_name = "GETAREA";
/

STATUS

---

VALID</source>

Check the package status in user_objects

   <source lang="sql">

SQL> SQL> create or replace package pkg_a is

 2        v_a number:=0;
 3        function a1 return NUMBER;
 4  end;
 5  /

Package created. SQL> create or replace package body pkg_a is

 2      function a1 return NUMBER is
 3      begin
 4          return 0;
 5      end;
 6  end;
 7  /

Package body created. SQL> create or replace package pkg_b is

 2      function b1 return NUMBER;
 3  end;
 4  create or replace package body pkg_b is
 5      function b1 return NUMBER is
 6      begin
 7          return pkg_a.a1+1;
 8      end;
 9  end;
10  /

Warning: Package created with compilation errors. SQL> SQL> select object_name||" "||object_type

 2  from user_objects
 3  where status = "INVALID";

OBJECT_NAME||""||OBJECT_TYPE

---

ADDNEWEMPLOYEE PROCEDURE ADDNEWROW PROCEDURE ADD_NEW_EMPLOYEE PROCEDURE ALMOSTFULL FUNCTION AUTHORS_SEL PROCEDURE AUTHOR_FIRST_NAME_UPD PROCEDURE AUTHOR_SEL PROCEDURE BIGUTIL PACKAGE BIGUTIL PACKAGE BODY BIND_TEST PROCEDURE COMPILE_WARNING PROCEDURE DELETEEMPLOYEE FUNCTION DELETEMYEMPLOYEE PROCEDURE EMPLOYEEDATA PACKAGE EMPLOYEEINFO FUNCTION EMPLOYEEOPS PACKAGE EMPLOYEEOPS PACKAGE BODY EMPLOYEEPACKAGE PACKAGE EMPLOYEEPACKAGE PACKAGE BODY EMPLOYEE_COUNT PROCEDURE EMPLOYEE_PACKAGE PACKAGE BODY EMPLOYEE_VIEW VIEW EMPLOYEE_VIEW_TRIGGER TRIGGER EMP_CHANGE_S PROCEDURE EMP_DATA VIEW EMP_NAME_CHANGE TRIGGER FULLNAME FUNCTION F_EMP_DSP FUNCTION F_GETDIFF FUNCTION F_GETDNAME FUNCTION F_GETEMPS FUNCTION F_MAKEADDRESS FUNCTION F_VALIDATESALARY FUNCTION HELLOFLE PROCEDURE INSERT_NUMBERS PROCEDURE LOG_SHUTDOWN TRIGGER LOG_STARTUP TRIGGER MEASURE_USAGE PROCEDURE MYPROCEDURE PROCEDURE NAMED_BLOCK PROCEDURE NEWYORKEMP VIEW PKG PACKAGE PKG_ADIFFERENTUTIL PACKAGE BODY PKG_B PACKAGE PKG_B PACKAGE BODY PKG_EMP PACKAGE PKG_EMP PACKAGE BODY PKG_UTIL PACKAGE PRODUCTTYPE TYPE BODY PRODUCT_PACKAGE PACKAGE PRODUCT_PACKAGE PACKAGE BODY P_PRINTEMPS PROCEDURE P_UPDATELOC PROCEDURE P_VALIDATESALARY PROCEDURE RECORDFULLCLASSES PROCEDURE TEST_TIME PROCEDURE UPDATEEMPLOYEE FUNCTION UPDATEMYEMPLOYEE PROCEDURE UPDATE_EMP PROCEDURE V_EMP VIEW V_EMPLOYEE VIEW V_EMP_IU TRIGGER 62 rows selected. SQL> SQL> SQL></source>

Check the status for all stored procedures is by using the Oracle data dictionary view USER_OBJECTS

   <source lang="sql">

select object_type, object_name, status from user_objects where object_type in ("FUNCTION","PROCEDURE","PACKAGE","PACKAGE BODY","TRIGGER") order by object_type,object_name;</source>

Check user_objects for object name

   <source lang="sql">

SQL> SQL> SQL> SELECT object_name, object_type, status

 2  FROM   user_objects
 3  WHERE  OBJECT_NAME IN ("TEST_PACK", "CALL_TEST_PROC")
 4  and    rownum < 50
 5  ORDER BY object_name, object_type;

OBJECT_NAME

---

OBJECT_TYPE STATUS

---

-------

CALL_TEST_PROC PROCEDURE VALID TEST_PACK PACKAGE VALID TEST_PACK PACKAGE BODY VALID

SQL></source>

Create drop command by query the user_objects table

   <source lang="sql">

SQL> SQL> SQL> SQL> SQL> SELECT "DROP " || object_type || " " || object_name || ";"

 2  FROM      user_objects
 3  WHERE     object_type IN ("PACKAGE","PACKAGE BODY","PROCEDURE",
 4                         "FUNCTION","TRIGGER")
 5  and       rownum < 50
 6  ORDER BY  object_type;

DROP FUNCTION CHITIME; DROP FUNCTION RULERSTR; DROP FUNCTION TO_NUMBER_OR_NULL; DROP FUNCTION TRACEIT; DROP FUNCTION TO_MMSDDSYYYY_OR_NULL; DROP FUNCTION IS_OVERDUE; DROP FUNCTION RECEIVE_MESSAGE; DROP FUNCTION TABCOUNT; DROP FUNCTION VALUE_IN; DROP PACKAGE PARAMETERS; DROP PACKAGE ONECUR; DROP PACKAGE WORKER_TS; DROP PACKAGE DATES; DROP PACKAGE WORKPLACE_TYPE_TS; DROP PACKAGE WORKER_TYPE_TS; DROP PACKAGE VALERR; DROP PACKAGE SCOPES; DROP PACKAGE CLASSPACKAGE; DROP PACKAGE GENDER_TS; DROP PACKAGE BODY WORKER_TYPE_TS; DROP PACKAGE BODY WORKER_TS; DROP PACKAGE BODY GENDER_TS; DROP PACKAGE BODY SCOPES; DROP PACKAGE BODY PARAMETERS; DROP PACKAGE BODY WORKPLACE_TYPE_TS; DROP PACKAGE BODY DATES; DROP PACKAGE BODY CLASSPACKAGE; DROP PACKAGE BODY SALESPKG; DROP PACKAGE BODY VALERR; DROP PROCEDURE DROPIT; DROP PROCEDURE FLUSH; DROP PROCEDURE BULK_TEST; DROP PROCEDURE CLOSECUR; DROP PROCEDURE ROW_AT_A_TIME_TEST; DROP PROCEDURE SAVESTRINGTOFILE; DROP PROCEDURE PUT_BOOLEAN; DROP PROCEDURE CREINDX; DROP PROCEDURE GET_LINE; DROP PROCEDURE P_RUN_INSERT; DROP PROCEDURE P1; DROP PROCEDURE WAIT; DROP PROCEDURE SEND_MESSAGE; DROP PROCEDURE PRINTLN; DROP PROCEDURE DROP_IF_EXISTS; DROP PROCEDURE SHOWEMPS; DROP PROCEDURE DELETE_CUST; DROP PROCEDURE FCREATE; DROP PROCEDURE RUNDDL; DROP PROCEDURE PROCA; 49 rows selected. SQL> SQL></source>

Create grant permission command by querying user_objects table

   <source lang="sql">

SQL> SQL> SQL> SQL> SELECT "GRANT EXECUTE ON " || object_name || " TO " ||

 2           UPPER("&user_role") || ";"
 3  FROM     user_objects
 4  WHERE    object_type IN ("PACKAGE","PROCEDURE","FUNCTION")
 5  and      rownum < 50
 6  ORDER BY object_type, object_name;

Enter value for user_role: old 2: UPPER("&user_role") || ";" new 2: UPPER("") || ";" GRANT EXECUTE ON CHITIME TO ; GRANT EXECUTE ON GRPVAL TO ; GRANT EXECUTE ON IS_OVERDUE TO ; GRANT EXECUTE ON RECEIVE_MESSAGE TO ; GRANT EXECUTE ON RULERSTR TO ; GRANT EXECUTE ON TABCOUNT TO ; GRANT EXECUTE ON TO_MMSDDSYYYY_OR_NULL TO ; GRANT EXECUTE ON TO_NUMBER_OR_NULL TO ; GRANT EXECUTE ON TRACEIT TO ; GRANT EXECUTE ON VALUE_IN TO ; GRANT EXECUTE ON CLASSPACKAGE TO ; GRANT EXECUTE ON DATES TO ; GRANT EXECUTE ON GENDER_TS TO ; GRANT EXECUTE ON MYDATE TO ; GRANT EXECUTE ON ONECUR TO ; GRANT EXECUTE ON PARAMETERS TO ; GRANT EXECUTE ON SALESPKG TO ; GRANT EXECUTE ON SCOPES TO ; GRANT EXECUTE ON TIMER TO ; GRANT EXECUTE ON VALERR TO ; GRANT EXECUTE ON WORKER_TS TO ; GRANT EXECUTE ON WORKER_TYPE_TS TO ; GRANT EXECUTE ON WORKPLACE_TYPE_TS TO ; GRANT EXECUTE ON BULK_TEST TO ; GRANT EXECUTE ON CLOSECUR TO ; GRANT EXECUTE ON CREINDX TO ; GRANT EXECUTE ON DELETE_CUST TO ; GRANT EXECUTE ON DROPIT TO ; GRANT EXECUTE ON DROP_IF_EXISTS TO ; GRANT EXECUTE ON FCREATE TO ; GRANT EXECUTE ON FLUSH TO ; GRANT EXECUTE ON GET_LINE TO ; GRANT EXECUTE ON P1 TO ; GRANT EXECUTE ON PRINTLN TO ; GRANT EXECUTE ON PROC0 TO ; GRANT EXECUTE ON PROC1 TO ; GRANT EXECUTE ON PROC2 TO ; GRANT EXECUTE ON PROC3 TO ; GRANT EXECUTE ON PROCA TO ; GRANT EXECUTE ON PROCB TO ; GRANT EXECUTE ON PUT_BOOLEAN TO ; GRANT EXECUTE ON P_RUN_INSERT TO ; GRANT EXECUTE ON ROW_AT_A_TIME_TEST TO ; GRANT EXECUTE ON RUNDDL TO ; GRANT EXECUTE ON SAVESTRINGTOFILE TO ; GRANT EXECUTE ON SEND_MESSAGE TO ; GRANT EXECUTE ON SHOWEMPS TO ; GRANT EXECUTE ON SHOW_FILE TO ; GRANT EXECUTE ON WAIT TO ; 49 rows selected.</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 all invalid user objects in user_objects table

   <source lang="sql">

SQL> SQL> SQL> SELECT object_type, object_name, status, created, last_ddl_time

 2  FROM      user_objects
 3  WHERE     object_type IN ("PROCEDURE", "FUNCTION", "PACKAGE",
 4                            "PACKAGE BODY", "TRIGGER")
 5  AND       status = "INVALID"
 6  and       rownum < 50;

OBJECT_TYPE OBJECT_NAME STATUS CREATED

---

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

LAST_DDL_

---

PROCEDURE DELETE_CUST INVALID 17-JUL-08 24-JUL-08 PROCEDURE WAIT INVALID 16-JUL-08 24-JUL-08 PACKAGE WORKPLACE_TYPE_TS INVALID 16-JUL-08 24-JUL-08

OBJECT_TYPE OBJECT_NAME STATUS CREATED

---

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

LAST_DDL_

---

PACKAGE BODY WORKPLACE_TYPE_TS INVALID 16-JUL-08 24-JUL-08 PACKAGE WORKER_TYPE_TS INVALID 16-JUL-08 24-JUL-08 PACKAGE GENDER_TS INVALID 16-JUL-08 24-JUL-08

OBJECT_TYPE OBJECT_NAME STATUS CREATED

---

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

LAST_DDL_

---

PACKAGE BODY GENDER_TS INVALID 16-JUL-08 24-JUL-08 PACKAGE WORKER_TS INVALID 16-JUL-08 24-JUL-08 PACKAGE BODY WORKER_TS INVALID 16-JUL-08 24-JUL-08

OBJECT_TYPE OBJECT_NAME STATUS CREATED

---

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

LAST_DDL_

---

PACKAGE BODY WORKER_TYPE_TS INVALID 16-JUL-08 24-JUL-08 PROCEDURE P_RUN_INSERT INVALID 17-JUL-08 24-JUL-08 PROCEDURE ROW_AT_A_TIME_TEST INVALID 17-JUL-08 24-JUL-08

OBJECT_TYPE OBJECT_NAME STATUS CREATED

---

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

LAST_DDL_

---

PROCEDURE BULK_TEST INVALID 17-JUL-08 24-JUL-08 PACKAGE CLASSPACKAGE INVALID 28-JUN-08 24-JUL-08 PACKAGE BODY CLASSPACKAGE INVALID 28-JUN-08 24-JUL-08

OBJECT_TYPE OBJECT_NAME STATUS CREATED

---

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

LAST_DDL_

---

PROCEDURE FCREATE INVALID 14-JUL-08 24-JUL-08 PROCEDURE CLOSECUR INVALID 14-JUL-08 24-JUL-08 PROCEDURE SAVESTRINGTOFILE INVALID 14-JUL-08 24-JUL-08

OBJECT_TYPE OBJECT_NAME STATUS CREATED

---

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

LAST_DDL_

---

PROCEDURE PUT_BOOLEAN INVALID 14-JUL-08 24-JUL-08 PROCEDURE CREINDX INVALID 14-JUL-08 24-JUL-08 PROCEDURE GET_LINE INVALID 14-JUL-08 24-JUL-08

OBJECT_TYPE OBJECT_NAME STATUS CREATED

---

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

LAST_DDL_

---

FUNCTION RECEIVE_MESSAGE INVALID 14-JUL-08 24-JUL-08 FUNCTION TRACEIT INVALID 14-JUL-08 24-JUL-08 PROCEDURE SEND_MESSAGE INVALID 14-JUL-08 24-JUL-08

OBJECT_TYPE OBJECT_NAME STATUS CREATED

---

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

LAST_DDL_

---

PACKAGE ONECUR INVALID 14-JUL-08 24-JUL-08 PROCEDURE SHOWEMPS INVALID 14-JUL-08 24-JUL-08 PROCEDURE PROCA INVALID 14-JUL-08 24-JUL-08

OBJECT_TYPE OBJECT_NAME STATUS CREATED

---

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

LAST_DDL_

---

PROCEDURE PROCB INVALID 14-JUL-08 24-JUL-08 PACKAGE BODY MYDATE INVALID 14-JUL-08 24-JUL-08 PROCEDURE SHOW_FILE INVALID 14-JUL-08 24-JUL-08

OBJECT_TYPE OBJECT_NAME STATUS CREATED

---

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

LAST_DDL_

---

FUNCTION FOPEN INVALID 14-JUL-08 24-JUL-08 FUNCTION EMP_COUNT INVALID 14-JUL-08 24-JUL-08 PROCEDURE ORG_PROC INVALID 15-JUL-08 24-JUL-08

OBJECT_TYPE OBJECT_NAME STATUS CREATED

---

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

LAST_DDL_

---

FUNCTION F_CURSOR_EXP INVALID 15-JUL-08 24-JUL-08 PROCEDURE P_PRINT_REPORT INVALID 15-JUL-08 24-JUL-08 FUNCTION F_CURSOR_EXP_COMPLEX INVALID 15-JUL-08 24-JUL-08

OBJECT_TYPE OBJECT_NAME STATUS CREATED

---

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

LAST_DDL_

---

FUNCTION GETALLHIERARCHIES INVALID 15-JUL-08 24-JUL-08 PACKAGE BODY ORGMASTER INVALID 15-JUL-08 24-JUL-08 FUNCTION F_GET_FORMATTED_ORG_NAME INVALID 15-JUL-08 24-JUL-08

OBJECT_TYPE OBJECT_NAME STATUS CREATED

---

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

LAST_DDL_

---

PACKAGE BODY RFPKG INVALID 15-JUL-08 24-JUL-08 PACKAGE BODY RFPKG2 INVALID 15-JUL-08 24-JUL-08 PACKAGE NOCOPYPKG INVALID 15-JUL-08 24-JUL-08

OBJECT_TYPE OBJECT_NAME STATUS CREATED

---

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

LAST_DDL_

---

PACKAGE BODY NOCOPYPKG INVALID 15-JUL-08 24-JUL-08 PROCEDURE UPDATE_DYN_GLOBAL_BULK2 INVALID 16-JUL-08 24-JUL-08 PACKAGE BODY ORGMASTER2 INVALID 15-JUL-08 24-JUL-08

OBJECT_TYPE OBJECT_NAME STATUS CREATED

---

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

LAST_DDL_

---

PROCEDURE UPDATE_DYN_GLOBAL_BULK INVALID 16-JUL-08 24-JUL-08 PACKAGE SRPKG2 INVALID 15-JUL-08 24-JUL-08 PACKAGE BODY SRPKG2 INVALID 15-JUL-08 24-JUL-08

OBJECT_TYPE OBJECT_NAME STATUS CREATED

---

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

LAST_DDL_

---

PROCEDURE LOG_ERROR INVALID 15-JUL-08 25-JUL-08

49 rows selected. SQL></source>

Get invalid table objects by joining user_segments and user_objects

   <source lang="sql">

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

 2         decode(status,"INVALID","*","") status,
 3         tablespace_name
 4    from user_objects a, user_segments b
 5   where a.object_name = b.segment_name (+)
 6     and a.object_type = b.segment_type (+)
 7     and rownum < 50
 8   order by object_type, object_name
 9  /

OBJECT_TYPE OBJECT_NAME STATUS TABLESPACE_NAME

---

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

FUNCTION COMPUTE_DISCOUNTS

            DELETEEMPLOYEE                 *
            EXITFUNC
            F_GETDIFF                      *
            GETAREA

INDEX BIN$fKX+qNArQt+8GLZvNQJlrQ==$0 SYSTEM

OBJECT_TYPE OBJECT_NAME STATUS TABLESPACE_NAME

---

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

PACKAGE PKG *

            PKG_TEST1

PACKAGE BODY PKG_TEST1 PROCEDURE ADD_NEW_EMPLOYEE *

clear columns;

OBJECT_TYPE OBJECT_NAME STATUS TABLESPACE_NAME

---

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

PROCEDURE AUTHORS_SEL *

            AUTHOR_FIRST_NAME_UPD          *
            AUTHOR_SEL                     *
            BIND_TEST                      *
            CHANGEPRICE
            COMPILE_WARNING                *
            DELETEMYEMPLOYEE               *

OBJECT_TYPE OBJECT_NAME STATUS TABLESPACE_NAME

---

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

PROCEDURE EMPLOYEE_COUNT *

            EMP_CHANGE_S                   *
            HELLO
            HELLOFLE                       *
            HIKE_PRICES
            INSERT_NUMBERS                 *
            MEASURE_USAGE                  *

OBJECT_TYPE OBJECT_NAME STATUS TABLESPACE_NAME

---

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

PROCEDURE MULTIPLE_CURSORS_PROC

            MYPROCEDURE                    *
            MY_FIRST_PROC
            NAMED_BLOCK                    *
            P
            P_HELLO
            P_HELLOTO

OBJECT_TYPE OBJECT_NAME STATUS TABLESPACE_NAME

---

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

TABLE BIN$dgfyftgCSZSBnu0LqVJzrQ==$0 USERS

            BIN$zbJqSAxnQA2up7MHjGauHw==$0            USERS
            EMPLOYEE_TAB
            EMP_DEPT                                  SYSTEM
            PEOPLE                                    SYSTEM

OBJECT_TYPE OBJECT_NAME STATUS TABLESPACE_NAME

---

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

TRIGGER EMP_NAME_CHANGE *

            LOG_SHUTDOWN                   *
            LOG_STARTUP                    *

TYPE ADDRESSTYPE

            ADDRESS_OBJ
            AOBJ

OBJECT_TYPE OBJECT_NAME STATUS TABLESPACE_NAME

---

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

TYPE EMPLOYEETYPE

            PERSONTYPE
            PRODUCTTYPE

TYPE BODY AOBJ

            EMPLOYEETYPE

OBJECT_TYPE OBJECT_NAME STATUS TABLESPACE_NAME

---

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

VIEW EMP_DATA *

            NEWYORKEMP                     *

49 rows selected. SQL></source>

Get Object id from user_objects

   <source lang="sql">

SQL> SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) ); Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,

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

Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> create or replace type emp_type

 2    as object
 3    (empno       number(4),
 4     ename       varchar2(10),
 5     job         varchar2(9),
 6     mgr         number(4),
 7     hiredate    date,
 8     sal         number(7, 2),
 9     comm        number(7, 2)
10    );
11  /

Type created. SQL> SQL> create or replace type emp_tab_type as table of emp_type

 2  /

Type created. SQL> SQL> create table dept_and_emp

 2    (deptno number(2) primary key,
 3     dname     varchar2(14),
 4     loc       varchar2(13),
 5     emps      emp_tab_type
 6    )
 7    nested table emps store as emps_nt;

Table created. SQL> SQL> alter table emps_nt add constraint emps_empno_unique unique(empno)

 2  /

Table altered. SQL> alter table emps_nt add constraint mgr_fk foreign key(mgr) references emps_nt(empno); alter table emps_nt add constraint mgr_fk foreign key(mgr) references emps_nt(empno)

ERROR at line 1: ORA-30730: referential constraint not allowed on nested table column

SQL> SQL> insert into dept_and_emp

 2    select dept.*,
 3       CAST( multiset( select empno, ename, job, mgr, hiredate, sal, comm
 4                         from emp
 5                         where emp.deptno = dept.deptno ) AS emp_tab_type )
 6      from dept
 7  /

4 rows created. SQL> SQL> SQL> SQL> SQL> select name

 2      from sys.col$
 3     where obj# = ( select object_id
 4                     from user_objects
 5                     where object_name = "DEPT_AND_EMP" )
 6  /

NAME

---

DEPTNO DNAME EMPS LOC SYS_NC0000400005$ SQL> SQL> SQL> SQL> SQL> drop table dept cascade constraint; Table dropped. SQL> drop table emp; Table dropped. SQL> drop table dept_and_emp; Table dropped. SQL> drop type emp_tab_type; Type dropped. SQL> drop type emp_type; Type dropped. SQL> SQL></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>

Join sys.col$ and user_objects

   <source lang="sql">

SQL> SQL> create or replace type address_type as object

 2  ( city    varchar2(30),
 3    street  varchar2(30),
 4    state   varchar2(2),
 5    zip     number
 6  )
 7  /

SQL> SQL> create or replace type person_type as object

 2  ( name             varchar2(30),
 3    dob              date,
 4    home_address     address_type,
 5    work_address     address_type
 6  )
 7  /

Type created. SQL> create table people of person_type

 2  /

Table created. SQL> desc people

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
NAME                                               VARCHAR2(30)
DOB                                                DATE
HOME_ADDRESS                                       ADDRESS_TYPE
WORK_ADDRESS                                       ADDRESS_TYPE

SQL> SQL> SQL> select * from people; no rows selected SQL> SQL> select name, p.home_address.city from people p; no rows selected SQL> SQL> select name, segcollength from sys.col$

 2  where obj# = ( select object_id from user_objects where object_name = "PEOPLE" )
 3  /

NAME SEGCOLLENGTH

---

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

SYS_NC_OID$ 16 SYS_NC_ROWINFO$ 1 NAME 30 DOB 7 HOME_ADDRESS 1 SYS_NC00006$ 30 SYS_NC00007$ 30 SYS_NC00008$ 2 SYS_NC00009$ 22 WORK_ADDRESS 1 SYS_NC00011$ 30 SYS_NC00012$ 30 SYS_NC00013$ 2 SYS_NC00014$ 22 14 rows selected. SQL> SQL> drop table people; Table dropped. SQL> SQL></source>

Query user_objects for all java classes

   <source lang="sql">

SQL> SQL> SELECT object_name shortname,

 2         DBMS_JAVA.LONGNAME (object_name) longname
 3    FROM USER_OBJECTS
 4    WHERE object_type = "JAVA CLASS"
 5      AND object_name != DBMS_JAVA.LONGNAME (object_name);
   AND object_name != DBMS_JAVA.LONGNAME (object_name)
                      *

ERROR at line 5: ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier

SQL> SQL> SQL></source>

Query user_objects for all packages, procedures and functions

   <source lang="sql">

SQL> SQL> SQL> SQL> SQL> SELECT "CREATE PUBLIC SYNONYM " || object_name || " FOR " ||

 2           object_name || ";"
 3  FROM     user_objects
 4  WHERE    object_type IN ("PACKAGE","PROCEDURE","FUNCTION")
 5  and      rownum < 50
 6  ORDER BY object_type, object_name;

CREATE PUBLIC SYNONYM CHITIME FOR CHITIME; CREATE PUBLIC SYNONYM GRPVAL FOR GRPVAL; CREATE PUBLIC SYNONYM IS_OVERDUE FOR IS_OVERDUE; CREATE PUBLIC SYNONYM RECEIVE_MESSAGE FOR RECEIVE_MESSAGE; CREATE PUBLIC SYNONYM RULERSTR FOR RULERSTR; CREATE PUBLIC SYNONYM TABCOUNT FOR TABCOUNT; CREATE PUBLIC SYNONYM TO_MMSDDSYYYY_OR_NULL FOR TO_MMSDDSYYYY_OR_NULL; CREATE PUBLIC SYNONYM TO_NUMBER_OR_NULL FOR TO_NUMBER_OR_NULL; CREATE PUBLIC SYNONYM TRACEIT FOR TRACEIT; CREATE PUBLIC SYNONYM VALUE_IN FOR VALUE_IN; CREATE PUBLIC SYNONYM CLASSPACKAGE FOR CLASSPACKAGE; CREATE PUBLIC SYNONYM DATES FOR DATES; CREATE PUBLIC SYNONYM GENDER_TS FOR GENDER_TS; CREATE PUBLIC SYNONYM MYDATE FOR MYDATE; CREATE PUBLIC SYNONYM ONECUR FOR ONECUR; CREATE PUBLIC SYNONYM PARAMETERS FOR PARAMETERS; CREATE PUBLIC SYNONYM SALESPKG FOR SALESPKG; CREATE PUBLIC SYNONYM SCOPES FOR SCOPES; CREATE PUBLIC SYNONYM TIMER FOR TIMER; CREATE PUBLIC SYNONYM VALERR FOR VALERR; CREATE PUBLIC SYNONYM WORKER_TS FOR WORKER_TS; CREATE PUBLIC SYNONYM WORKER_TYPE_TS FOR WORKER_TYPE_TS; CREATE PUBLIC SYNONYM WORKPLACE_TYPE_TS FOR WORKPLACE_TYPE_TS; CREATE PUBLIC SYNONYM BULK_TEST FOR BULK_TEST; CREATE PUBLIC SYNONYM CLOSECUR FOR CLOSECUR; CREATE PUBLIC SYNONYM CREINDX FOR CREINDX; CREATE PUBLIC SYNONYM DELETE_CUST FOR DELETE_CUST; CREATE PUBLIC SYNONYM DROPIT FOR DROPIT; CREATE PUBLIC SYNONYM DROP_IF_EXISTS FOR DROP_IF_EXISTS; CREATE PUBLIC SYNONYM FCREATE FOR FCREATE; CREATE PUBLIC SYNONYM FLUSH FOR FLUSH; CREATE PUBLIC SYNONYM GET_LINE FOR GET_LINE; CREATE PUBLIC SYNONYM P1 FOR P1; CREATE PUBLIC SYNONYM PRINTLN FOR PRINTLN; CREATE PUBLIC SYNONYM PROC0 FOR PROC0; CREATE PUBLIC SYNONYM PROC1 FOR PROC1; CREATE PUBLIC SYNONYM PROC2 FOR PROC2; CREATE PUBLIC SYNONYM PROC3 FOR PROC3; CREATE PUBLIC SYNONYM PROCA FOR PROCA; CREATE PUBLIC SYNONYM PROCB FOR PROCB; CREATE PUBLIC SYNONYM PUT_BOOLEAN FOR PUT_BOOLEAN; CREATE PUBLIC SYNONYM P_RUN_INSERT FOR P_RUN_INSERT; CREATE PUBLIC SYNONYM ROW_AT_A_TIME_TEST FOR ROW_AT_A_TIME_TEST; CREATE PUBLIC SYNONYM RUNDDL FOR RUNDDL; CREATE PUBLIC SYNONYM SAVESTRINGTOFILE FOR SAVESTRINGTOFILE; CREATE PUBLIC SYNONYM SEND_MESSAGE FOR SEND_MESSAGE; CREATE PUBLIC SYNONYM SHOWEMPS FOR SHOWEMPS; CREATE PUBLIC SYNONYM SHOW_FILE FOR SHOW_FILE; CREATE PUBLIC SYNONYM WAIT FOR WAIT; 49 rows selected. SQL></source>

Query user_objects for all PL/SQL code

   <source lang="sql">

SQL> SQL> SET PAGESIZE 66 SQL> COLUMN object_type FORMAT A20 SQL> COLUMN object_name FORMAT A30 SQL> COLUMN status FORMAT A10 SQL> BREAK ON object_type SKIP 1 SQL> SQL> SELECT object_type, object_name, status

 2      FROM user_objects
 3     WHERE object_type IN ("PACKAGE",
 4                          "PACKAGE BODY",
 5                          "FUNCTION",
 6                          "PROCEDURE",
 7                          "TYPE",
 8                          "TYPE BODY",
 9                          "TRIGGER"
10                         )
11           and rownum < 50
12  ORDER BY object_type, status, object_name
13  /

OBJECT_TYPE OBJECT_NAME STATUS

---

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

FUNCTION ACCOUNT_BALANCE INVALID

                    AUTHORIZED_EMPS                INVALID
                    BIZDAYS_BETWEEN                INVALID
                    BOOKTITLE                      INVALID
                    ADD_TWO                        VALID
                    BETWEENSTRING                  VALID
                    BETWNSTR                       VALID
                    BIZDAYS_BETWN                  VALID

PACKAGE AA_TYPES INVALID

                    ANYNUMS_PKG                    INVALID
                    BIDIR                          INVALID
                    BOOK_INFO                      INVALID
                    AUDIT_TRAIL_PKG                VALID
                    AUTHORS_PKG                    VALID
                    BT                             VALID

PACKAGE BODY ANYNUMS_PKG INVALID

                    AUDIT_TRAIL_PKG                INVALID
                    BIDIR                          INVALID
                    BOOK_INFO                      INVALID
                    AUTHORS_PKG                    VALID
                    BT                             VALID

PROCEDURE A INVALID

                    ADJUST_SALARY                  INVALID
                    APPLY_BALANCE                  INVALID
                    AUDIT_CEO_COMP                 INVALID
                    AUTONFAIL                      INVALID
                    AUTONOMOUS_INSERT              INVALID
                    AUTO_PROC                      INVALID
                    AUTO_PROC1                     INVALID
                    AUTO_PROC2                     INVALID
                    B                              INVALID
                    BACKGROUND_ALERT               INVALID
                    BACKGROUND_SEND_MAIL           INVALID
                    BIND                           INVALID
                    BULK_EXCEPTIONS                INVALID
                    BULK_TEST                      INVALID
                    AA                             VALID
                    ADD_UP                         VALID
                    ANALYZE_MY_TABLES              VALID
                    ASSIGN                         VALID
                    ASSIGNDT                       VALID
                    BB                             VALID
                    BUILD_SCHEDULE                 VALID
                    CALCULATE_TOTALS               VALID

TRIGGER APPLICATION_USERS_IOI INVALID TYPE ASCII_TABLE_TYPE INVALID

                    ADDRESS2                       VALID
                    ADD_LIST                       VALID

TYPE BODY ADDRESS2 VALID

49 rows selected. SQL> SQL></source>

Query user_objects for object status

   <source lang="sql">

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> alter table t add y number

 2  /

Table altered. SQL> SQL> select object_name, object_type, status from user_objects

 2  where rownum < 10
 3  /

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> SQL> drop table t; Table dropped.</source>

Query user_objects for user-defined data type

   <source lang="sql">

SQL> SQL> SQL> CREATE OR REPLACE TYPE address AS OBJECT

 2              (line1 VARCHAR2(20),
 3               line2 VARCHAR2(20),
 4               city VARCHAR2(20),
 5               state_code VARCHAR2(2),
 6               zip VARCHAR2(13),
 7    MEMBER FUNCTION get_address RETURN VARCHAR2,
 8    MEMBER PROCEDURE set_address
 9              (addressLine1 VARCHAR2,
10               addressLine2 VARCHAR2,
11               address_city VARCHAR2,
12               address_state VARCHAR2,
13               address_zip VARCHAR2)
14  );
15  /

Type created. SQL> CREATE OR REPLACE TYPE BODY address AS

 2    MEMBER FUNCTION get_address RETURN VARCHAR2
 3    IS
 4    BEGIN
 5      RETURN (SELF.line1||" "||SELF.line2||" "||SELF.city||", "||SELF.state_code||" "||SELF.zip);
 6    END get_address;
 7    MEMBER PROCEDURE set_address (addressLine1 VARCHAR2,
 8                  addressLine2 VARCHAR2,
 9                  address_city VARCHAR2,
10                  address_state VARCHAR2,
11                  address_zip VARCHAR2)
12    IS
13    BEGIN
14      line1 :=addressLine1;
15      line2 :=addressLine2;
16      city :=address_city;
17      state_code :=address_state;
18      zip :=address_zip;
19    END set_address;
20  END;
21  /

Type body created. SQL> SQL> SQL> column object_name format a30; SQL> SQL> select object_name,object_type from user_objects where object_name like "ADDRESS%FOR%"; no rows selected SQL> SQL> SQL></source>

Query user_objects for your functions and procedures

   <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> select object_name, object_type, status from user_objects

 2  where rownum < 10
 3  /

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> SQL> SQL> drop table t; Table dropped.</source>

Recompile all invalid package

   <source lang="sql">

SQL> SQL> SQL> declare

 2      cursor myCursor is select object_type, object_name from SYS.USER_OBJECTS where  status = "INVALID"
 3      order by 1, 2;
 4      sqlString varchar2(100);
 5  begin
 6    for r1 in myCursor loop
 7      begin
 8        if r1.object_type = "PACKAGE BODY" then
 9
10          sqlString := "alter PACKAGE "||r1.object_name||" compile BODY";
11
12        elsif r1.object_type = "TYPE BODY" then
13
14          sqlString := "alter TYPE "||r1.object_name||" compile BODY";
15
16        else
17
18          sqlString := "alter "||r1.object_type||" "||r1.object_name||" compile";
19
20        end if;
21
22        execute immediate sqlString;
23
24        dbms_output.put_line(r1.object_type||" "||r1.object_name||" compiled successfully");
25
26      exception
27
28        when OTHERS then
29
30          dbms_output.put_line(SQLERRM||" on "||sqlString);
31
32      end;
33
34    end loop;
35
36  end;
37  /

PL/SQL procedure successfully completed. 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>

User and its average object id

   <source lang="sql">

SQL> SQL> select a.username, (select count(*) from all_objects b where b.owner = a.username) cnt,

 2                     (select avg(object_id ) from all_objects b where b.owner = a.username) avg
 3  from all_users a
 4  /

USERNAME CNT AVG

---

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

SYS 6520 5009.74064 SYSTEM 422 6095.87678 OUTLN 7 1172.57143 DIP 0 TSMSYS 2 8606.5 INV15 2 16237.5 DBSNMP 46 9592.65217 INV10 2 16227.5 CTXSYS 338 9877.92012 XDB 334 10800.7485 ANONYMOUS 0 USERNAME CNT AVG

---

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

MDSYS 458 11667.2009 HR 34 12104.5 FLOWS_FILES 11 12717.2727 FLOWS_020100 1085 12813.424 sqle 530 16254.6849 INV11 2 16229.5 INV12 2 16231.5 INV13 2 16233.5 INV14 2 16235.5 PLSQL 0 INV16 2 16239.5 USERNAME CNT AVG

---

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

INV17 2 16241.5 INV18 2 16243.5 INV19 2 16245.5 INV20 2 16247.5 DEFINER 4 16250.5 27 rows selected. SQL> SQL></source>