Reference no: EM131307051

Question :

1. Create a package, DEPT_PKG, with the following:

a) A public procedure called NEW_DEPT to enter a new department row into the DEPARTMENTS table. The procedure should accept four parameters - one for each column in the DEPARTMENTS table. Use the parameter values in your INSERT command.

b) A public procedure called UPD_DEPTMGR to update the manager for a specific department ID in the DEPARTMENTS table. The procedure should provide two parameters: the department ID, and a new manager ID. Add exception handling to account for an invalid department ID.

c) A public function called GET_DEPARTMENT_COUNT to retrieve the total number of employees assigned to a specific department. The function should accept the department ID as a parameter and return the number of employees in that department. Add error handling to account for an invalid department ID.

2. Write an anonymous block that invokes the procedure NEW_DEPT to add a new department to the DEPARTMENTS table with dept ID 88, department name of "IT". You may choose any value for location_id that is valid. Add an exception handler that will display the current error message that raised the exception.

3. Execute the UPD_DEPTMGR procedure and change the manager number of the new department you just added. Query the DEPARTMENTS table to view your changes.

4. Write an anonymous block which uses an explicit cursor to process all departments. Retrieve the department name and call the GET_DEPT_COUNT function from your package to return the total count by department. Simply call DBMS_OUTPUT.PUT_LINE and print the department name and number of employees for all departments.

5. Write a SELECT statement to display all department information for all departments using a SELECT statement which also invokes the GET_DEPT_COUNT function to return the total employee count for each department.

6. Query the code from your package, DEPT_PKG, (both parts) from the data dictionary.

7. Create a trigger which will always use the next value of the sequence, bb_prodid_seq, as the idproduct column when a new record is inserted into the BB_PRODUCT table. Test your trigger.