Using Dynamic SQL - iBATIS in Action - page 187

When using Java code, you are left to handle all the basic repetitious tasks such as
connection retrieval, parameter preparation, result set iteration, and object popu-
lation over and over again. On top of managing the common tasks, in this exam-
ple we also deal with the minimal complexity introduced by the need to create an
IN statement. To deal with this, a List of parameters is created and populated.
Even after writing all the code we are still left with less functionality than would be
desired to handle our scenario properly. You may have noticed that the Prepared-
Statement simply uses setObject to assign the parameters. Ideally we would want
to have our types specified, but that requirement would have pushed this code to
an unreasonable size.
In the end, this code is reminiscent of those activities in children's magazines
in which you attempt to find cleverly hidden pictures contained in a larger pic-
ture. Here the challenge would be to find the “Hidden SQL .” In this example,
straight JDBC becomes seriously inhibitive. Let's move on to take a look at using a
stored procedure for Dynamic SQL .

8.5.2 Using stored procedures

Stored procedures can be a real lifesaver for many tasks and they should be appre-
ciated for what they can do. When used for Dynamic SQL , they often suffer the
same problems as the Java-coded Dynamic SQL . Listing 8.16 shows you how a stored
procedure written in Oracle's PL/SQL deals with Dynamic SQL construction.

Listing 8.16 Oracle stored procedure (provided by Sven Boden)

create or replace package category_pkg
as
type ref_cursor is ref cursor;

function get_category(
categoryid varchar default null,
name category.name%type default null)
return ref_cursor;
end;
/

create or replace package body category_pkg
as
function get_category(
categoryid varchar default null,
name category.name%TYPE default null)
return ref_cursor
is
return_cursor ref_cursor;