Forum Stats

  • 3,855,262 Users
  • 2,264,493 Discussions
  • 7,905,953 Comments

Discussions

How to fix this error?

245

Answers

  • mathguy
    mathguy Member Posts: 10,692 Blue Diamond
    edited Jan 25, 2021 5:16PM

    @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
    mathguy Member Posts: 10,692 Blue Diamond

    @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
    James Su Member Posts: 1,165 Gold Trophy

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

  • jdev1
    jdev1 Member Posts: 222 Green Ribbon

    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
    mathguy Member Posts: 10,692 Blue Diamond

    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
    jdev1 Member Posts: 222 Green Ribbon

    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
    mathguy Member Posts: 10,692 Blue Diamond

    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
    jdev1 Member Posts: 222 Green Ribbon

    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
    mathguy Member Posts: 10,692 Blue Diamond

    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
    jdev1 Member Posts: 222 Green Ribbon

    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.