User's Manual

Chapter 9. Persistence Tutorial 79
Note
The do call and OUT parameters are not available for Postgres because Postgres has not yet imple-
mented CallableStatements or OUT parameters.
9.4.2.2.2. PL/SQL Functions
Retrieving a single value back from a function is almost identical to using OUT parameters for proce-
dures. First, declare your PL/SQL in your SQL file. For example, you may define the following:
create or replace function DataQueryPLSQLFunction(v_article_id in integer)
return number
is
v_title varchar(700);
begin
select title into v_title from articles
where article_id = v_article_id;
return v_title;
end;
/
show errors
Next, you can define the function as a DataOperation within your PDL file, as follows:
data operation DataOperationWithPLSQLAndArgsAndReturnInPDL {
do call {
:title = DataQueryPLSQLFunction(:articleID)
} map {
title : VARCHAR(700);
articleID : Integer;
}
}
Finally, you can retrieve the value for title just like any normal data query, after binding the :ar-
ticleID variable.
It is necessary to declare the types for each variable within the function whether or not it is an OUT
parameter.
9.5. Filtering, Ordering, and Binding Parameters
When retrieving information from the database, developers almost always need to be able to filter and
order the results that are returned. Therefore, DataQuery, DataCollection, and DataAssocia-
tionCursor objects allow for ordering and filtering. DataQuery and DataOperation also allow
developers to set arbitrary bind variables within the queries and DML statements. This document dis-
cusses how these features are implemented and how using the Filter can be overridden to use any
arbitrary filtering scheme.