Compare this with Employees_Cur_Dbms_Sql. The API has been simplified and the implementation is much simpler.
Create the procedure Show_Rows_For_Dept_Static_Sql. And as above, use two concurrent sessions connected as programmer and observer and step through this test.
The key to understanding the behavior is that the PL/SQL close statement for a static SQL cursor (as distinct from cursor variable) does not cause the same actions behind the scenes as does Dbms_Sql.Close_Cursor. Rather, it does a soft close, merely marking the cursor as a candidate for closing should that be necessary. This represents an optimistic approach: we expect a cursor to be used several times during a program's execution with different values for the in-bind variables. And so it's held in the PL/SQL cursor cache for potential re-use subject to usual LRU behavior. The size of the PL/SQL cursor cache is exactly equal to the system parameter open_cursors The details are explained in the comments in Employees_Cur_Static_Sql.