Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
How to fix this error?
Answers
-
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.
-
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! 🙂
-
Ahh now it does sound like a "nested loop" to me. Thanks for the explanation!
-
Thanks you for the answers. So what about to use second loop, use simple loop and run until departmend_id count not exceed 10?
-
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.
-
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;
-
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.
-
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;
-
Here is one way you can do this.
Notice a couple of things:
- 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).
- 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; /
-
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.