Sunday, December 12, 2010

Deleting .svn directories in directory tree

If you use Subversion, you'd probably noticed a hidden directory ".svn" in every directory managed by Subversion. This is where Subversion keeps its version data. If you're in Linux, and you want to get rid of it for any reason. You can use the following command to do so:

rm -rf `find . -type d -name .svn`

This command will find and delete any directory with the name ".svn".

Saturday, December 4, 2010

Calling PL/SQL code in Java

In this blogpost, I will show you how to interact with Oracle PL/SQL code in Java. Before we continue with the Java code, let's create a simple Oracle package we can use to test our code.

Compile the following package specification and body in the Oracle database.

Package specification:

create or replace
package test_package as

procedure test_procedure(v varchar2);

function test_function(v varchar2) return varchar2;

end test_package;

Package body:

create or replace
package body package test_package as

procedure test_procedure(v varchar2) is
begin
null;
end;

function test_function(v varchar2) return varchar2 is
begin
return v;
end;

end test_package;

The package contains a procedure and a function that returns a VARCHAR2 type. We can call the procedure with the following Java code:

DataSource ds = ...
Connection con = ds.getConnection();
CallableStatement cs =
con.prepareCall("{call test_package.test_procedure(?)}");
cs.setString(1, "Calling test_procedure!");
cs.executeUpdate();
cs.close();

The function is slightly different, because we have to register the type the function returns.

DataSource ds = ...
Connection con = ds.getConnection();
CallableStatement cs =
con.prepareCall("{? = call test_package.test_function(?)}");
cs.registerOutParameter(1, java.sql.Types.VARCHAR);
cs.setString(2, "Calling test_function!");
cs.execute();
String s = cs.getString(1);
cs.close();

The function can also be called in another way. Like this:

DataSource ds = ...
Connection con = ds.getConnection();
PreparedStatement ps =
con.prepareStatement(
"SELECT test_package.test_function(?) S FROM dual"
);
ps.setString(1, "Calling test_function!");
ResultSet r = ps.executeQuery();
String result = null;

if (r.next()) {
result = r.getString("S");
}

con.close();

This way is similar to executing a standard SQL-statement.