Jijo K Jose

Create a PL/SQL block to retrieve name of each department from dept table and print each department name to screen, incorporating a PL/SQL table.

A) Declare PL/SQL table my_dept_table to temporarily store name of departments.   

B) Using a loop retrieves names of all departments currently in dept table and store them in PL/SQL table. Each department number is a multiple of 10.                          

C) Using another loop, retrieves department names from PL/SQL table and print them to screen, using dbms_output.put_line.

Answer

declare

            cursor cur1 is select * from ddept_a5518;

            cursor cur2 is select * from my_dept_table;

            dept_row cur1%rowtype;

            total number(4);

            dept_row_1 cur2%rowtype;

            total number(4);

begin

            –execute immediate 'CREATE GLOBAL TEMPORARY TABLE my_dept_table ( dno  NUMBER,    dname  VARCHAR2(20) ) ON COMMIT PRESERVE ROWS';

            delete from my_dept_table;
 

            open cur1;

            loop

                        fetch cur1 into dept_row;

                        exit when cur1%notfound;

                        insert into my_dept_table values (dept_row.dno*10,dept_row.dname);

                        dbms_output.put_line('Dept Name from DDEPT_A5518 : ' || dept_row.dname);

            end loop;

            close cur1;

            open cur2;

            loop

                        fetch cur2 into dept_row_1;

                        exit when cur2%notfound;

                        dbms_output.put_line('Dept Name from MY_DEPT_TABLE : ' || dept_row_1.dname);

            end loop;

            close cur2;

end;

/