Oracle PL/SQL/System Tables Views/USER OBJECTS — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
| |
Текущая версия на 10: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
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.
Check new created tables in user_objects
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>
Check package status
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>
Finding, Validating, and Describing Packages
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>
Get code for all procedure, function and package from user_objects
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>
Get object id for created table
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.
If procedure is valid
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.
list all stored procedures: "PROCEDURE", "FUNCTION", "PACKAGE", "PACKAGE BODY"
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>
Query all INVALID objects from user_objects table
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>
Query a view in user_objects table for a view just created
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>
Query object_type, object_name from user_objects
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>
Query user_objects for invalid package body
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>
Query user_objects for invalid view
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>
Query user-objects for stored procedure
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>
Query user_objects for trigger
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>
Query user_objects in PL/SQL
SQL>
SQL> create or replace procedure show_objects
2 as
3 begin
4 for c1 in (select object_name, object_type
5 from user_objects
6 order by object_name ) loop
7 dbms_output.put_line("Name: " || c1.object_name || " Type: " || c1.object_type );
8 end loop;
9 end;
10 /
Procedure created.
SQL> set serveroutput on
SQL> exec show_objects;
Name: ACCESS$ Type: TABLE
Name: AGGXMLIMP Type: TYPE
Name: AGGXQIMP Type: TYPE
Name: ALERT_QT Type: TABLE
Name: ALERT_QUE Type: QUEUE
Name: ALERT_QUE$1 Type: RULE
Name: ALERT_QUE$1 Type: RULE SET
Name: ALERT_QUE_N Type: RULE SET
Name: ALERT_QUE_R Type: RULE SET
Name: old_values209_T Type: TYPE
PL/SQL procedure successfully completed.
SQL>
SQL> column object_name format a20
SQL> column object_type format a20
SQL>
SQL> select object_name, object_type
2 from user_objects
3 where rownum < 50
4 order by object_name;
OBJECT_NAME OBJECT_TYPE
-------------------- --------------------
BOOTSTRAP$ TABLE
CCOL$ TABLE
CDEF$ TABLE
CLU$ TABLE
COL$ TABLE
CON$ TABLE
C_COBJ# CLUSTER
C_FILE#_BLOCK# CLUSTER
C_OBJ# CLUSTER
C_TS# CLUSTER
C_USER# CLUSTER
OBJECT_NAME OBJECT_TYPE
-------------------- --------------------
FET$ TABLE
FILE$ TABLE
ICOL$ TABLE
IND$ TABLE
I_CCOL1 INDEX
I_CDEF1 INDEX
I_CDEF2 INDEX
I_CDEF4 INDEX
I_COBJ# INDEX
I_COL2 INDEX
I_COL3 INDEX
OBJECT_NAME OBJECT_TYPE
-------------------- --------------------
I_CON1 INDEX
I_CON2 INDEX
I_FILE#_BLOCK# INDEX
I_FILE1 INDEX
I_FILE2 INDEX
I_IND1 INDEX
I_OBJ# INDEX
I_OBJ1 INDEX
I_OBJ2 INDEX
I_OBJ3 INDEX
I_PROXY_DATA$ INDEX
OBJECT_NAME OBJECT_TYPE
-------------------- --------------------
I_PROXY_ROLE_DATA$_1 INDEX
I_TAB1 INDEX
I_TS# INDEX
I_TS1 INDEX
I_UNDO1 INDEX
I_UNDO2 INDEX
I_USER1 INDEX
OBJ$ TABLE
PROXY_DATA$ TABLE
PROXY_ROLE_DATA$ TABLE
SEG$ TABLE
OBJECT_NAME OBJECT_TYPE
-------------------- --------------------
TAB$ TABLE
TS$ TABLE
UET$ TABLE
UNDO$ TABLE
USER$ TABLE
49 rows selected.
SQL>
SQL>
Query user_objects table
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> --
Query USER_OBJECTS table by object name
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>
Query user_objects table for all procedure
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>
Query user_objects table for stored procedure before and after recompile
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>
Show the procedure is marked invalid **
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.
Show the status of a procedure
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.
To find out what procedures and functions you have created, use the following SQL query:
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>