Oracle PL/SQL/System Tables Views/USER OBJECTS — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 13:01, 26 мая 2010
Содержание
- 1 A procedure with dependencies
- 2 Check new created tables in user_objects
- 3 Check package status
- 4 Finding, Validating, and Describing Packages
- 5 Get code for all procedure, function and package from user_objects
- 6 Get object id for created table
- 7 If procedure is valid
- 8 list all stored procedures: "PROCEDURE", "FUNCTION", "PACKAGE", "PACKAGE BODY"
- 9 Query all INVALID objects from user_objects table
- 10 Query a view in user_objects table for a view just created
- 11 Query object_type, object_name from user_objects
- 12 Query user_objects for invalid package body
- 13 Query user_objects for invalid view
- 14 Query user-objects for stored procedure
- 15 Query user_objects for trigger
- 16 Query user_objects in PL/SQL
- 17 Query user_objects table
- 18 Query USER_OBJECTS table by object name
- 19 Query user_objects table for all procedure
- 20 Query user_objects table for stored procedure before and after recompile
- 21 Show the procedure is marked invalid **
- 22 Show the status of a procedure
- 23 To find out what procedures and functions you have created, use the following SQL query:
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>