Oracle Pl/Sql Records Exception Handling and Cursor

Description

This is your final examination which consists of five programming questions.
You are to provide me with a .txt file or a .sql file for supplying the code for each question. Put these in a .zip file and submit them.

1. Use a cursor to retrieve the location number and the city from the locations table. Pass the location number to another cursor to retrieve from the emp_details_view the last_name, job_title and salary for that employees that work at that location.
a. Use the %ISOPEN attribute
b. Use a simple loop
c. Use an EXIT WHEN and a %NOTFOUND attribute

2. Write an exception handler to raise an exception named DUE_FOR_ROTATION
The criteria for raising the exception is if location id is 2700.
If the employee’s location in the em_details_view meets this criteria output ‘Due for Rotation’.
Insert a listing of all of the EMPLOYEES that meet this criteria into the employee_analysis table.
You will need to create an analysis table as:
CREATE TABLE location_analysis
(employee_id NUMBER(6) not null,
Last_name VARCHAR(25),
First_name VARCHAR2(20),
Location_id NUMBER(4),
City VARCHAR2(30));
Screenshot #1 Creating the Analysis Table

3. You will be using the EMPLOYEES table in YOUR TABLESPACE. Using a cursor write a block to find the largest salary where the hire date is before ‘ 1-jan-96’ . When you find employees who match this criteria change give them a salary increase of 25%.

4. You will be using the JOBS table in YOUR TABLESPACE. Create a record that incorporates the four columns from the JOBS table. Output the values for a single job on four lines of output.

5. Write a PL/SQL block to print information about a given country.
a. Declare a PL/SQL record based on the structure of the COUNTRIES table.
b. Use the DEFINE command to provide the country ID. Pass the value to the PL/SQL block through a SQL*Plus substitution variable.
c. Use DBMS_OUTPUT.PUT_LINE to print selected information about the country.
d. Execute and test the PL/SQL block for the countries with the IDs CA, DE, UK, US.