A procedure with dependencies : USER_OBJECTS « System Tables Views « Oracle PL / SQL






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.

   
    
    
    
  








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.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: