Create a stored procedure PDELD11 that CREATES a GLOBAL TEMP

Create a stored procedure (P_DEL_D11) that CREATES a GLOBAL TEMPORARY TABLE, that is based on the following query (SELECT distinct firstname as fname, lastname as lname,workdept as deptnum FROM employee e). Then issues a DELETE on the GLOBAL TEMPORARY table deleting records with deptnum = ‘D11’.

Solution

Please Excute below scripts in the database one by one
If you want to check whether record is getting deleted or not please enable
insert into debug values();commit; line
if you enable the debug table you can check in the debug table

-- Create table
create global temporary table GTT_EMPLOYEE
(
firstname VARCHAR2(20),
lastname VARCHAR2(20),
workdept VARCHAR2(20)
)
on commit preserve rows;

/

-- Create table
create table EMPLOYEE
(
firstname VARCHAR2(20),
lastname VARCHAR2(20),
workdept VARCHAR2(20)
)

/

-- Create table
create table DEBUG
(
c VARCHAR2(4000)
)
/

-- insert scripts for employee table
insert into employee (FIRSTNAME, LASTNAME, WORKDEPT)
values (\'Sally \', \'Adams\', \'D01\');

insert into employee (FIRSTNAME, LASTNAME, WORKDEPT)
values (\'Mary \', \'Nelson\', \'D02\');

insert into employee (FIRSTNAME, LASTNAME, WORKDEPT)
values (\'Don\', \'Charles\', \'D11\');

/
commit;
/

create or replace procedure P_DEL_D11
IS

vcQuery varchar2(1000);
vcdelete varchar2(100);
vccount int;
BEGIN

vcQuery := \'INSERT INTO GTT_employee
SELECT distinct firstname as fname, lastname as lname,workdept as deptnum FROM employee e\'; --Here we are inserting records into GTT_employee table
--insert into debug values (vcQuery);
commit;
EXECUTE IMMEDIATE (vcQuery);commit;--Excuting vcQuery querty and commit the transaction
  
vcdelete:=\'delete from GTT_employee where workdept=\'\'D11\'\'\';--Here We are deleting D11 record from GTT_employee table


-- insert into debug values (vccount);commit;
EXECUTE IMMEDIATE(vcdelete);commit; --Excuting vcdelete query and commit the transaction
select count(*) into vccount from GTT_employee;--Here we are inserting the count into vccount variable
--insert into debug values (vccount);commit;

END;