Views: User_Objects, User_source, User_Errors, User_Object_size, User_Dependicies
–Query the User_Objects view for all procedures and functions
SELECT object_name, status
FROM user_objects
WHERE object_type IN(‘PROCEDURE’, ‘FUNCTION’);
—Determine which objects have source code
SELECT DISTINCT name, type
FROM user_source;
–Retrieve the code for the raise_salary procedure
SELECT text
FROM user_source
WHERE name = ‘RAISE_SALARY’
ORDER BY line;
–Execute a query retrieving all errors for the Error_Salary procedure
SELECT line, text, attribute, message_number
FROM user_errors
WHERE name = ‘ERROR_SALARY’
ORDER BY line;
–Query the user_object_size to ascertain storage information for our objects.
SELECT *
FROM user_object_size
WHERE type IN(‘PROCEDURE’, ‘FUNCTION’);
–Query the USER_DEPENDENCIES to find the dependency information on the
–SALARY_VALID procedure
SELECT referenced_name, referenced_type
FROM user_dependencies
WHERE name = ‘SALARY_VALID’;
Dependency Tracking Utility SETUP – utldtree.sql
DEPTREE_FILL() – fills IDEPTREE table
Package
Oracle Packages are database objects that are unique to the Oracle DBMS. They are containers that group logically-related objects into a single definition. Packages have two parts – a specification and a body. The:
- Specification section declares the various components
- Body section provides the full definitions of the components
The Package components can consist of Types, Variables, Constants, Exceptions, Cursors and subprograms.
Specifications
CREATE OR REPLACE PACKAGE personnel AS
–Define hire_employee procedure
PROCEDURE hire_employee
( input_ssn IN employee.ssn%TYPE,
first_name IN employee.fname%TYPE,
last_name IN employee.lname%TYPE,
department_name IN department.dname%TYPE,
input_salary IN employee.salary%TYPE);
–Define procedure to fire employees
PROCEDURE fire_employee (input_ssn IN employee.ssn%TYPE);
–Define procedure to transfer employees to different department
PROCEDURE transfer_employee
( input_ssn IN employee.ssn%TYPE,
new_department_number IN department.dnumber%TYPE);
–Define procedure to test salary increases
PROCEDURE raise_salary_valid
(employee_ssn IN CHAR,
employee_pct IN NUMBER DEFAULT 5,
result_message OUT CHAR);
END personnel;
Body
CREATE OR REPLACE PACKAGE BODY Personnel AS
….
…..
END Personnel;
SELECT object_name, object_type, status
FROM user_objects
WHERE object_type IN (‘PACKAGE’, ‘PACKAGE BODY’);
[the_ad id=’12166′]