PL/SQL Anonymous Blocks and Java

An 'Anonymous Block' is a PL/SQL procedure that doesn't have a name, doesn't directly take parameters and isn't persisted in the database. In this post we'll explain why it's a useful construct if you are working with PL/SQL, Java and JDBC

This is an example of an Anonymous Block:

 DECLARE 

 -- 

 -- 

 -- First and Second input params are name and number 

 -- 

 l_name emp.ename%TYPE := ?; 

 l_number emp.empno%TYPE := ?; 

 -- 

 l_howmany NUMBER; 

 -- 

 BEGIN 

 -- 

 INSERT INTO emp 

 (empno, ename) 

 VALUES 

 (l_name, l_number); 

 -- 

 COMMIT; 

 -- 

 SELECT COUNT(*) 

 INTO l_howmany 

 FROM emp; 

 -- 

 -- Third param is an output one - number of records in table. 

 -- 

 ? := l_howany; 

 -- 

 END; 

Note how the above block has all the attributes of a stored procedure except a name and parameters. We can still use JDBC bind variables though, as long as they as used as values and targets for assignments,

To call an Anonymous Block of PL/SQL from Java you simply use a CallableStatement.

Note: On May 17th 2010 OrindaBuild was renamed to JDBCWizard