Oracle Pl/Sql Records Exception Handling and Cursor

Availability: In stock

Regular Price: $20.00

Special Price: $15.00

OR

Task 1 Oracle PlSql Cursors

Double click on above image to view full picture

Zoom Out
Zoom In

More Views

Quick Overview

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.

Regular Price: $20.00

Special Price: $15.00

Details

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.

Product Tags

Use spaces to separate tags. Use single quotes (') for phrases.