[question] Where the code indicates that select statement use a db cursor

[ShisiJu]

Hello, kubo

I have a question that confused me a lot. Could you give a favor? Thanks a lot!

Q: Where the code indicates that select statement use a db cursor?

Does the select statement need to explicitly declare cursor? Something like that

DECLARE
   CURSOR employees_in_10_cur
   IS
      SELECT *
        FROM employees
       WHERE department_id = 10;

lib\oci8\cursor.rb

# Executes the SQL statement assigned the cursor
# In case of select statement, it returns the number of the select-list.
sql = "SELECT * FROM emp WHERE ename = :ename"
cursor = conn.parse(sql)
cursor.bind_param(1, 'SMITH') # bind by position
cursor.exec()

I don't see DECLARE CURSOR, but it actually use a cursor.

So I tried to find where the code declare cursor step to step. Then, I found that

# conn.parse will call Initialize the internal C structure.
# __initialize will call C code
__initialize(conn, sql)

ext\oci8\stmt.c

static VALUE oci8_stmt_initialize(VALUE self, VALUE svc, VALUE sql)

C code is a big challenge for me and I failed to find the code that explicit cursor for a select statement.

[kubo]

It depends on DBMS (Oracle, MySQL, etc.) and the programming language. As for ruby-oci8, cursor.exec() sends the SQL statement in the ruby-side cursor to the database server, then the database server creates a server-side cursor implicitly when the statement is select.

[ShisiJu]

Thanks for your reply.

So you mean ruby-oci8 do not explicitly declare a cursor when the statement is select.

From docs.oracle sql_cursor, I saw

An implicit cursor has attributes that return information about the most recently run SELECT or DML statement that is not associated with a named cursor.

But I am still confused. Implicit cursors how to return the database cursor from the DBMS to the ruby code?

Does the implicit cursors will automatically open, fetch and close?

[kubo]

So you mean ruby-oci8 do not explicitly declare a cursor when the statement is select.

Correct.

Implicit cursors how to return the database cursor from the DBMS to the ruby code?

Implicit cursors and explicit cursors are concept specific to the PL/SQL language. Oracle database engine itself doesn't have such concept. For example, PL/SQL explicit cursors must have names. However the database engine doesn't know the names. I guess that the name is used only to tie the PL/SQL explicit cursor and a reference to the cursor in the database engine. As for implicit cursor, I guess that PL/SQL internally has a reference to the cursor in the database engine.

Does the implicit cursors will automatically open, fetch and close?

Ruby-oci8 uses Oracle Call Interface (OCI).

• The OCI function OCIStmtPrepare2 creates a statement handle.
• OCIStmtExecute sends the SQL statement and ties the statement handle and a reference to the opened cursor in the database engine.
• OCIStmtFetch fetches rows.
• OCIStmtRelease frees the statement handle though OCI doesn't close the cursor in the database side immediately. It is closed at the next network round trip to the database.

[ShisiJu]

Thanks!
I will try to search more information from oracle oci. Hope to get the key information.

[kubo]

What I wrote in https://github.com/kubo/ruby-oci8/issues/239#issuecomment-976181289 is not documented in the OCI manual. I checked it by network capture. I have made a prototype of Oracle network protocol decoder more than a decade ago. The OCI manual explains how to use OCI functions, not their internal behavior.

[ShisiJu]

So that is it. Maybe need to deep into Oralce, then it will be clear. There are so many things to learn. It is a cheering thing.

I closed the issue. Because the issue is related to Oracle, not ruby-oci8. I will be back, if I find the answer.

Thanks for your patience again.

[ShisiJu]

https://docs.oracle.com/cd/E11882_01/server.112/e40540/sqllangu.htm#CNCPT1741

When an application issues a SQL statement, the application makes a parse call to the database to prepare the statement for execution. The parse call opens or creates a cursor, which is a handle for the session-specific private SQL area that holds a parsed SQL statement and other processing information. The cursor and private SQL area are in the PGA.