Jijo K Jose

Create a PL/SQL table containing instructorid, firstname, lastname of instructor table and insert values into the table.

Answer

declare
    cursor cur1 is select * from empp_a5518;
    emp_row cur1%rowtype;
    ch varchar2(20):=1;
begin
    
    select upper(tname) into ch from tab where tname like upper('instructor_a5518');

    if(ch=upper('instructor_a5518')) then
        dbms_output.put_line('instructor_a5518 table already created !!');
    else
        execute immediate 'CREATE GLOBAL TEMPORARY TABLE instructor_a5518 ( id  NUMBER,fname VARCHAR2(20), lname  VARCHAR2(20) ) ON COMMIT PRESERVE ROWS';
    end if;
    

    open cur1;
    loop
        fetch cur1 into emp_row;
        exit when cur1%notfound;
        insert into instructor_a5518 values (emp_row.empno,emp_row.empfname,emp_row.emplname);
    end loop;
    close cur1;

end;
/