Question $2$: PL$/$SQL Bulk Insert Based on HR Employees

Objective:

The goal of this assignment is to create a PL$/$SQL program that demonstrates the use of bulk

operations to efficiently insert multiple records into a table using data from the HR$.$EMPLOYEES

table. The student will practice PL$/$SQL concepts such as cursors, bulk collect, and bulk insert.

Problem Statement:

You are tasked with creating a PL$/$SQL procedure that performs a bulk insert of employee records

from the HR$.$EMPLOYEES table into a new table EMPLOYEE$\_$ARCHIVE. The bulk insert should be

efficient, using PL$/$SQL features such as cursors and bulk collect.

Steps:

$1.$ Create a new table EMPLOYEE$\_$ARCHIVE:

o The EMPLOYEE$\_$ARCHIVE table should have the same structure as the

HR$.$EMPLOYEES table but should only include the following columns:

$$ EMPLOYEE$\_$ID $($NUMBER$)$

$$ FIRST$\_$NAME $($VARCHAR$2(20))$

$$ LAST$\_$NAME $($VARCHAR$2(25))$

$$ HIRE$\_$DATE $($DATE$)$

$$ SALARY $($NUMBER$)$

o Use the following SQL statement to create the table:

CREATE TABLE EMPLOYEE$\_$ARCHIVE $($

EMPLOYEE$\_$ID NUMBER$(6),$

FIRST$\_$NAME VARCHAR$2(20),$

LAST$\_$NAME VARCHAR$2(25),$

HIRE$\_$DATE DATE,

SALARY NUMBER$(8,2)$

$)$;

$2.$ Write a PL$/$SQL Procedure to Perform Bulk Insert:

o Create a PL$/$SQL procedure called BULK$\_$INSERT$\_$EMPLOYEES.

o The procedure should use a cursor to select employee data from the

HR$.$EMPLOYEES table.

o The selected records should be inserted into the EMPLOYEE$\_$ARCHIVE table using

bulk collect and a FORALL statement for bulk insertion.

$3.$ Requirements:

o The cursor should retrieve employees whose salary is greater than a specific

threshold $($e$.$g$.,5000).$

o Use BULK COLLECT to fetch the data into a PL$/$SQL collection.

o Use the FORALL statement to insert the records into EMPLOYEE$\_$ARCHIVE.

o Include exception handling to catch any errors during the bulk insert.

$4.$ Hints:

o You may use BULK COLLECT and LIMIT to fetch rows in batches if needed.

o Use a FORALL loop to perform the insertions in bulk.