Observing v$open_cursor

Note: The query used in the following tests joins back to v$sqlarea. That's simply because v$open_cursor. shows only the first 60 characters of the text while v$sqlarea shows the first 1000. As a convenience, create the view open_cursor_sql_text for this. If you prefer, you can use the simpler...

select sql_text from v$open_cursor
  where user_name = 'PROGRAMMER'
  order by sql_text

Create the procedure Show_Rows_For_Dept_Dbms_Sql. Now use two concurrent sessions connected as programmer and observer and step through this test.

Note: You'll see some extra sql_text rows besides what we're trying to observe which reflects how Set ServerOutput On works - quite interesting in its own right if you've never thought about how this works.

The results of the work done by Set_Up_Statement (opening the cursor, parsing and setting up the define-binds) is held in appropriate session structures represented by the persisting row in v$open_cursor.

For completeness, the package Employees_Cur_Bulk_Dbms_Sql, which exposes the same API, illustrates bulk syntax (aka array fetch) for Dbms_Sql. Test it with the procedure Sh_Rws_For_Dept_Bulk_Dbms_Sql.