Query user-objects for stored procedure : USER_OBJECTS « System Tables Views « Oracle PL / 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>

   
    
    
  








Related examples in the same category

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