User's Manual

78 Chapter 9. Persistence Tutorial
select nvl(max(magazine_id), 0) + 1, :title from magazine_id;
end;
/
show errors
To do this, first include the above statement in your SQL file, so that it will be defined in the database
when your package is installed. Next, declare it in your PDL file using a DataOperation:
data operation DataOperationWithPLSQLAndArgs {
do {
select myPLSQLProc(:title) from dual
}
}
You can then execute this data operation just like any other data operation, after binding the title
variable.
It is also possible to use OUT parameters within the DataOperation. To do this, the only additional
requirement is for the developer to specify the JDBC type of all of the parameters within the query.
Suppose you want to copy the article with the highest ID into a new row with the ID that you pass
into the procedure, and you want back the ID for the row that was copied. You can declare a PL/SQL
procedure such as the following:
create or replace procedure DataOperationProcWithInOut(
v_new_id IN Integer,
v_copied_id OUT Integer)
as
begin
select max(article_id) into v_copied_id from articles;
insert into articles (article_id, title)
select v_new_id, title from articles where article_id = v_copied_id;
insert into article_author_map (article_id, author_id)
select v_new_id, author_id from article_author_map
where article_id = v_copied_id;
end;
/
show errors
Adding the JDBC type, the PDL to access this procedure appears as follows:
data operation DataOperationProcWithInOut {
do call {
DataOperationProcWithInOut(:newID, :copiedID)
} map {
newID : INTEGER;
copiedID : INTEGER;
}
}
To execute this in Java, you simply need to bind the variable and then retrieve the variable using the
get(String) method in a fashion similar to retrieving a value from a DataQuery. For instance, to
print out the value of the copiedID variable, the following code can be executed:
DataOperation operation = getSession().retrieveDataOperation
("tutorial.DataOperationProcWithInOut");
operation.set("newID", new Integer(4));
operation.execute();
Integer copiedID = (Integer)operation.get("copiedID");
System.out.println("The copied ID was [" + copiedID.toString() + "]");