Wednesday, November 3, 2010

Oracle's PL/SQL, %ROWTYPE Records and Java


PL/SQL uses %ROWTYPE to mean a record that matches the structure of a row in a given table. So instead of declaring a series of scalar variables for the columns in a table you can declare a record that matches the entire row. You can then pass it as a parameter. This makes coding, and especially passing records, much simpler if you are working inside PL/SQL. But if you need to pass a %ROWTYPE parameter to PL/SQL from Java you will encounter problems. In this post will will show you how to pass PL/SQL's %ROWTYPE to and from Java using JDBC.



Let's say we have an arbitrary table called foo. In the real world tables usually have many more columns than the two we have here:


 

 CREATE TABLE foo 

 (thing_name VARCHAR2(100) 

 ,thing_date DATE); 




Here's a sample PL/SQL procedure that uses a %ROWTYPE parameter.



 

 CREATE OR REPLACE PROCEDURE add_foo_rec (p_row in foo%ROWTYPE) AS 

 -- 

 BEGIN 

 -- 

 INSERT INTO foo 

 (thing_name, thing_date) 

 VALUES 

 (p_row.thing_name, p_row.thing_date); 

 -- 

 COMMIT; 

 -- 

 END; 



Note that there is no built in way to pass a %ROWTYPE parameter from Java to a PL/SQL. This is because the JDBC standard is vague about records and doesn't have a construct that is directly equivalent to %ROWTYPE. While Oracle has extended the JDBC standard in multiple places it never addressed passing %ROWTYPE to and from Java using JDBC.


As a workaround you could create an Oracle TYPE object with the same structure as the table, but that turns into an awful lot of additional work and also involves changing the database schema you are working with. But there's a way round this problem - use an Anonymous Block.


An Anonymous block is a chunk of PL/SQL logic that is parsed like a CallableStatement but doesn't persist in the database. It allows us to do multiple things in a single trip to the database. An example of an anonymous block that calls our %ROWTYPE procedure is below


 

 DECLARE 

 -- 

 l_row foo%ROWTYPE; 

 -- 

 BEGIN 

 -- 

 l_row.thing_name := 'Hello'; 

 l_row.thing_date := SYSDATE; 

 -- 

 add_foo_rec(l_row); 

 -- 

 END; 



There's nothing to stop you executing the above block as a CallableStatement, which is how we can write a JDBC call to pass a %ROWTYPE parameter to PL/SQL by replacing the internal assignment statements with parameters we can set at bind time:


 

 DECLARE 

 -- 

 l_row foo%ROWTYPE; 

 -- 

 BEGIN 

 -- 

 -- Bind parameter 1 as a VARCHAR2 

 l_row.thing_name := ?; 

 -- Bind parameter 2 as a DATE 

 l_row.thing_date := ?; 

 -- 

 add_foo_rec(l_row); 

 -- 

 END; 

No comments: