Show the status of a procedure : USER_OBJECTS « System Tables Views « Oracle PL / SQL






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.

   
    
    
    
  








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 stored procedure
10.Query user_objects for trigger
11.Query user_objects for invalid view
12.Query all INVALID objects from user_objects table
13.Get code for all procedure, function and package from user_objects
14.Query user_objects table for stored procedure before and after recompile
15.If procedure is valid
16.A procedure with dependencies
17.Check package status
18.Check new created tables in user_objects
19.Finding, Validating, and Describing Packages
20.Get object id for created table
21.Show the procedure is marked invalid **
22.To find out what procedures and functions you have created, use the following SQL query: