How to fix this error?

mathguy

@James Su

I believe it is safe to assume that the OP is in some kind of "student" role, learning PL/SQL. In that context it does make sense to do "silly things" such as selecting too much into an associative array. The overall goal is to learn to write correct code, that does slightly non-trivial things, using the features of PL/SQL (so that one gets to practice using them in somewhat realistic scenarios).

Later. students should be taught not to do in PL/SQL simple things that can be done easily in plain SQL. But in order to get to the advanced uses of PL/SQL that are really needed in real life, one has to learn PL/SQL on simple tasks first (including tasks that can be performed much better in other ways).

Unfortunately, I believe less than 1% of instructors explain this last. very important point. and less than 10% of students who are told that, actually understand it and take it seriously - otherwise it's hard to understand why so may programmers use PL/SQL for such simple tasks in real life instead of only using it as a stepping stone, in introduction-level courses.

mathguy

@James Su

Actually, since the task is to "use nested loops", I expect that the instructor wants an even less efficient approach: loop over department id's. Then for each department, populate the array just with the records for that department, and work on individual records in a nested loop (while keeping track of a global count in the process). So there will be one bulk collect operation for each department - at least this mitigates the memory waste problem! 🙂

James Su

https://community.oracle.com/tech/developers/discussion/comment/16782598#Comment_16782598

Ahh now it does sound like a "nested loop" to me. Thanks for the explanation!

jdev1

Thanks you for the answers. So what about to use second loop, use simple loop and run until departmend_id count not exceed 10?

mathguy

The outer loop, it seems, should be based on department id (selected from the departments table).

For the inner loop, you shouldn't need the local counter (count within department); since you will fetch rows from the employees table, one department at a time, you can have a row-limiting clause right there in the query itself, to get at most 10 rows from each department. HOW to do that depends on your Oracle version; in Oracle 12.1 or higher it's trivial, with the FETCH FIRST 10 ROWS ONLY clause.

jdev1

I am using 11 gr.

So let's say

<<outer>>

for i in employee.first..employee.last

loop

cnt_dept := case when employee(i).department_id = d_id then cnt_dept + 1 else 1 end;

cnt_total := cnt_total + 1;

exit when cnt_total = 30;

<<inner>>

for j in employee_first..cnt_dept

dbms_output.put_line(employee(i).employee_id || ' ' || employee(i).last_name ||

                           ' ' || employee(i).department_id);

exit when cnt_dept = 10;>

end loop;

end loop;

mathguy

The global counter cnt_total, should be increased in the inner loop, not in the outer loop. The way you have your code, the "total" count increases by 1 every time you move on to a new department. It should increase by 1 for each row you put in the OUTPUT buffer. The EXIT WHEN CNT_TOTAL = 30 should also be moved to the inner loop; even if you only selected 3 rows from a given department so far, and there are more rows to select, you should stop if the GLOBAL counter has reached 30.

jdev1

Unfortunately, it not works well.

create or replace procedure emps is

 type em is record (

  employee_id  hr.employees.employee_id%type,

  last_name   hr.employees.last_name%type,

  department_id hr.employees.department_id%type

 );

 type empl is table of em index by pls_integer;

 employee empl;

 cnt_dept pls_integer;

 cnt_total pls_integer;

 d_id   hr.employees.department_id%type;

begin

 select employee_id, last_name, department_id bulk collect into employee

 from  hr.employees

 order by department_id, employee_id;

 cnt_total := 0; 

 <<outer>>

for i in employee.first..employee.last

loop

cnt_dept := case when employee(i).department_id = d_id then cnt_dept + 1 else 1 end;

exit when cnt_dept = 10;

<<inner>>

for j in employee.first..employee.last

loop

cnt_total := cnt_total + 1;

dbms_output.put_line(employee(i).employee_id || ' ' || employee(i).last_name ||

              ' ' || employee(i).department_id);

exit when cnt_total = 30;

end loop;

end loop;

end;

/

begin

emps;

end;

mathguy

Here is one way you can do this.

Notice a couple of things:

1. EXIT in a nested loop only causes exit from the nested loop; the outer loop is not exited. In this problem, since we want to exit both the inner and the outer loop when the count is 30, there are two EXIT WHEN statements, one in the inner and one in the outer loop (with the same condition).
2. I could have written the outer loop similar to the inner loop, creating an associative array for department numbers and looping over the array. I chose an alternative, to demonstrate the "implicit FOR loop", which either you have already covered in class or you will soon (or you SHOULD, if it's not included in the curriculum).

Also, please, please learn to highlight your code (select it with your mouse in the usual way), find the small "paragraph" icon/link on the left side, click on it, and format your code as code. I can't read your posts.

create or replace procedure emps is
  type em is record (
    employee_id   hr.employees.employee_id%type,
    last_name     hr.employees.last_name%type,
    department_id hr.employees.department_id%type
  );
  type empl is table of em index by pls_integer;
  employee empl;
  cnt_total pls_integer := 0;
begin
  << outer_loop >>
  for rec in (select department_id from hr.departments) loop
    employee.delete;
    select employee_id, last_name, department_id bulk collect into employee
    from  hr.employees
    where department_id = rec.department_id
    order by employee_id
    fetch first 10 rows only;
    << inner_loop >>
    for i in 1 .. employee.count loop
      dbms_output.put_line(employee(i).employee_id || ' ' || employee(i).last_name || ' '
                              || employee(i).department_id);
      cnt_total := cnt_total + 1;
      exit when cnt_total = 30;
    end loop inner_loop;
    exit when cnt_total = 30;
  end loop outer_loop;
end;
/

jdev1

Very nice solution, thank you. I am not sure why this procedure not working. Actually I don't see compile errors

create or replace procedure check_dept_existence is
 type em is record (
  department_id  employees.department_id%type,
 );
 type empl is table of em index by pls_integer;
 employee empl;
  
  
 type em2 is record (
  department_id  departments.department_id%type,
 );
 type empl2 is table of em2 index by pls_integer;
 employee2 empl2;
  
 ex_invalid_id EXCEPTION; 
 ex_null_id EXCEPTION;
  
begin
  select e.department_id bulk collect into employee
  from employees e
  
 select d.department_id bulk collect into employee2
  from departments d

IF e.department_id!=d.department_id THEN 
   RAISE ex_invalid_id;
elsif e.department_id is null
 then raise ex_null_id; 
 else
  null;
  
end if;

EXCEPTION 
  WHEN ex_invalid_id THEN 
   dbms_output.put_line('The department does not exist.'); 
  WHEN ex_null_id THEN 
   dbms_output.put_line('ORA-20001: Null parameter is passed.'); 
  WHEN others THEN 
   dbms_output.put_line('Error!'); 
   
end;

TASK DESCRIPTION:

Create a procedure check_dept_existence to check existence of a given department ID. The procedure should return

silently if the passed ID is correct and should raise the following errors if it’s NULL or invalid:

ORA-20001: Null parameter is passed.

ORA-20002: The department does not exist.