Relational Databases and SQL

Introduction

A cursor is a control structure which is used for processing individual rows returned by a query. A typical programming language is procedural and does not offer any mechanism for manipulating a whole result set at once. Therefore, the rows in a result set must be processed sequentially by the application. In this way, a cursor can be thought of as an iterator over the collection of rows in the result set.

Do you actually need them?

The whole idea of the host programming language being handicapped, and therefore needed an awkward cursor structure has being eloquently criticized by Joe Celko in one of his books:

Cursor is a way to transform query result set into a stream that can be processed in a host 3GL language. Cursors are not compatible between different RDBMS vendors, and generally work slower than declarative SQL queries. Then, why bother? Mainly because of ignorance of database fundamentals and old habits. Here is detailed analogy:

ALLOCATE = turn tape recorder power on, assign channel
DECLARE CURSOR FOR … = mount the tape and declare file
OPEN = open the file
FETCH INTO = read in the program records one by one, move the head
CLOSE = close file, dismount tape from the recorder
DEALLOCATE = free tape recorder channel, power off

This quote reads especially funny in Russian translation edition of his “SQL Programming Style” where the “tape recorder” sounds like “audio tape deck”. And the suggestion that somebody still operates a tape deck in the world infested by iPod is hilarious.

Then, Joe goes through an anecdotal case of a newbie programmer who arranged three cursors to work together to iterate through master-detail data that have chosen conforming records and performed update. Eventually 250 lines of code were thrown away in favor of a single SQL update statement.

Cursors in PL/SQL

PL/SQL is a typical 3GL language, which genesis is traced back to Ada. Naturally, whenever you have a query which returns a result set containing more than one row you have to use a cursor. There can be little objection to this usage scenario. However, even though you program an oracle application most often your client would be written in a language other than PL/SQL: Java, .Net, Python, Ruby, etc. Then, you need a cursor that your host language, or the database connectivity library supports, and not the one supported within PL/SQL language environment!

Yet there are some people advocating an additional layer, so that your application technology stack becomes something like SQL -> PL/SQL -> .Net. Here is a quote and a short rebuttal:

Two related questions I am often asked are, “Can’t I just embed SQL statements in my code and use the OracleCommand object to get the data from the database?” and “If so, then why bother using PL/SQL and ref cursors?”

My answer to the first question is, “Yes, you can.” And my answer to the second question is, “Because it makes sense to do so, depending on the circumstances.” Let me explain. It is, after all, possible to create entire applications without ever writing or calling a single line of PL/SQL code. However, ref cursors can help optimize Oracle data retrieval. One of the primary benefits of using PL/SQL is that it is tightly integrated with both Oracle Database and the SQL language. For example, Oracle column types in a table are, in general, PL/SQL datatypes, and vice versa. This permits you to work with a single variable that is of the correct datatype for both a table in the database and the programming language being used. Another benefit that is sometimes overlooked is that you can use PL/SQL as a security tool or mechanism. It’s possible to create a PL/SQL procedure that returns data from a database table to which users have no direct access and are thus unable to browse with an external tool—they can get to the data only via the PL/SQL code. The users need the appropriate permissions to execute the PL/SQL procedure or function for this scenario to work correctly, but these can be granted by the database administrator. In addition, by using PL/SQL, you are moving code that deals with the data into the database and separating the client logic from the data logic. Moving the code into the database implicitly centralizes it, so it needs to be managed only in a single location.

The primary purpose of the ref cursor datatype is to allow PL/SQL code to return a result set to a program that is written in another language and is external to the database. It is the mechanism whereby PL/SQL code returns result sets to a client application. If you want to return a result set from PL/SQL code to a client, using a ref cursor is the way to go about it.

Even though there are several reasons to use PL/SQL, .NET programmers may initially find that it feels a bit unnatural to move code out of the .NET environment and into the database. However, keep in mind that PL/SQL was created for working with data in an Oracle database and that it does an excellent job. In fact, recent releases of the database, especially Oracle Database 10g, include enhancements to the PL/SQL compiler and optimizer, which make using PL/SQL even more appealing from a pure performance perspective.

There are many wrong ideas in this snippet. First, how introducing extra moving parts (an additional PL/SQL layer) helps “optimize Oracle data retrieval”? There is a fundamental incompatibility between declarative SQL and procedural PL/SQL language, so that “tight integration” between them is impossible. Second, advocating to use “PL/SQL as a security tool or mechanism” is totally inappropriate today, after dangling cursor security vulnerability received so much attention in oracle community. Finally, and most importantly, what kind of code is moved into the database, isn’t it just a query wrapped into a cursor? A standard way to reuse your frequently used queries is leveraging views.

Cursor support in SQL Developer

Yet, in rare cases when an application developer nees a PL/SQL cursor, what SQL Developer can offer? Ref cursor datatype inside SQL query is supported since SQL Developer version 1.1. Try the following in SQL worksheet:


select dname,
cursor(
select ename from emp
where emp.deptno=dept.deptno
)
from dept


If the above query run as a script (F5), output would be a plain text like this


DNAME CURSOR(SELECTENAMEFROMEMPWHERE…
————– ——————————————
ACCOUNTING CURSOR(CLARK,KING,MILLER,)
RESEARCH CURSOR(SMITH,JONES,SCOTT,ADAMS,FORD,)
SALES CURSOR(ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES,)
OPERATIONS CURSOR()


while running it as an individual statement (F9) would produce a table with a cursor datatype which you can drill down to see the content in a popup window.

A more sophisticated example includes opening a cursor in a function, which is called from your query:


create or replace
function empsor
return SYS_refcursor
as
ret SYS_refcursor;
i integer;
begin
i := p;
open ret for select empno, ename, sal from emp;
return ret;
end empsor;

select empsor from dual;


We already argued why this application design is crippled. You open a cursor in one function and it is the responsibility of the caller to close it. This makes the two actions open and close distant from each other, and the possibility of missing close is much greater. The consequence is a dangling cursor.

Stored procedure with REF CURSOR output

What if you have a cursor as an input and output parameter of a procedure — there is no way to plug in a procedure into a SQL! This is an interesting omission in SQL language design, because theoretically it is possible to join a procedure and a relation similar to the way functions and relations are joined.

Still, we can leverage the fact that SQL developer is able to output refcursor datatype inside of SQL statement. Ref cursor is just one more complex datatype! Therefore, what is neeeded is a translation of the procedure function argument output, to a function. Here is such a function OUT_REFCURSOR, which is uses dymamic SQL, all you need to do is to deploy it:


create or replace
FUNCTION OUT_REFCURSOR (my_call varchar2)
RETURN sys_refcursor AS
rc sys_refcursor;
BEGIN
EXECUTE IMMEDIATE ‘begin ‘||my_call||’; end;’ USING rc;
RETURN rc;
END OUT_REFCURSOR;


The function is generic, it can be plugged in to print the output of any stored procedure which accepts one refcursor parameter. Here is an example:

create or replace package pivot
as
type rc is ref cursor;
procedure testproc1( p_cursor in out rc );
end;


create or replace package body pivot
as
procedure testproc1( p_cursor in out rc ) is
l_stmt long;
begin
l_stmt := ’select empno, ename, sal from emp’;
open p_cursor for l_stmt;
end;
end;


select out_refcursor('pivot.testproc1(:1)') a1 from dual

A1
—–
CURSOR(7369,7499,7521,7566,7654,7698,7782,7788,7839,7844,7876,…)
1 rows selected


Certainly, a design with a cursor which is simultaneously an output and input procedure argument is odd by today’s programming standards. Even more grotesque is the case when a procedure returns more than one cursor.

Stored procedure with multiple REF CURSOR arguments

Procedures that return more than one cursor is only a marginally more complex case than the previous one. We add one extra transformation function:


create or replace FUNCTION OUT_REFCURSOR2 (my_call varchar2)
RETURN sys_refcursor AS
rc sys_refcursor;
dummy sys_refcursor;
BEGIN
EXECUTE IMMEDIATE ‘begin ‘||my_call||’; end;’ USING rc,dummy;
RETURN rc;
END OUT_REFCURSOR2;


Now suppose we want to output a procedure that returns 4 cursors:


create or replace package pivot
as
type rc is ref cursor;
procedure tst4( c1 in out rc, c2 out rc, c3 out rc, c4 out rc );
end;
/
create or replace package body pivot
as
procedure tst4( c1 in out rc, c2 out rc, c3 out rc, c4 out rc ) is
stmt1 long;
stmt2 long;
stmt3 long;
stmt4 long;
begin
stmt1 := ’select 1 from dual’;
open c1 for stmt1;
stmt2 := ’select 2 from dual’;
open c2 for stmt2;
stmt3 := ’select 3 from dual’;
open c3 for stmt3;
stmt4 := ’select 4 from dual’;
open c4 for stmt4;
end;
end;
/


Then we query:


select out_refcursor2(’pivot.tst4(:2,:1,:1,:1)’) a1,
out_refcursor2(’pivot.tst4(:2,:2,:1,:1)’) a2,
out_refcursor2(’pivot.tst4(:2,:2,:2,:1)’) a3,
out_refcursor(’pivot.tst4(:1,:1,:1,:1)’) a4
from dual;
A1 A2 A3 A4
—– —– —– —–
CURSOR(1,) CURSOR(2,) CURSOR(3,) CURSOR(4,)


Conclusion

SQL Developer doesn’t deploy anything on server side, therefore you need to deploy couple auxiliary stored procedures — out_refcursor and out_refcursor2 — manually. This is one time setup only. Then you can see the content of cursor called from select statement. Query output in SQL worksheet is either text, or graphics data grid, and cursor is a nested table within the grid.

Currently, there is no support for displaying the content of ref cursor when debugging. On the other hand, in Java world the content of cursor is almost never looked into directly, which prompts that there is something wrong with this cursor usage scenario. Also the ResultSet (cursor) object in Java is usually created, opened, iterated and closed within a single function, which indicates that the cursor crossing the procedure boundary in PL/SQL is bad practice too.

It seem that the genesis of “a better cursor demand” is a stream of programmers from SQL Server world, where a procedure which doesn’t even bother to declare its outputs as a cursor like this

CREATE OR REPLACE PROCEDURE TESTSPROC2
AS
select * from test_table order by id_no;
GO

Its a pity that TSQL designers give an impression of not understanding the difference between a procedure and a query.

Additional Reading

SQL Server article against excessive use of cursors

.