OceanBase Connector/J can process PL stored procedures and anonymous blocks and supports the PL block syntax and JDBC escape syntax.
The following PL calls can be used together with OceanBase Connector/J:
// The JDBC escape syntax.
CallableStatement ecs1 = conn.prepareCall
( "{call proc (?,?)}" ) ; // The stored procedure.
CallableStatement ecs2 = conn.prepareCall
( "{? = call func (?,?)}" ) ; // The stored function.
// The PL block syntax.
CallableStatement ecs3 = conn.prepareCall
( "begin proc (?,?); end;" ) ; // The stored procedure.
CallableStatement ecs4 = conn.prepareCall
( "begin ? := func(?,?); end;" ) ; // The stored function.
Function are called as follows in OceanBase Connector/J:
OceanBaseDataSource obds = new OceanBaseDataSource();
obds.setURL("jdbc:oceanbase:oracle:// Connection string:1521/username");
obds.setUser("Adam");
obds.setPassword("P@ssw0rd");
Connection conn = obds.getConnection();
CallableStatement cs = conn.prepareCall ("begin ? := f(?); end;");
cs.registerOutParameter(1,Types.CHAR);
cs.setString(3, "aaa");
cs.executeUpdate();
String result = cs.getString(1);
Example: Call a PL function to retrieve a character sequence and add a suffix to it.
CREATE OR REPLACE FUNCTION f (v CHAR)
RETURN CHAR AS
BEGIN
RETURN v || 'suffix';
END;
In Oracle mode, OceanBase Connector/J supports CallableStatement Set by Name. This allows you to assign values based on defined variable names. Here is an example:
public void testBeginInsertRepeatParameterNameOutOfOrder() throws Exception {
Connection connection = sharedPSConnection;
String tableName = "T_DS_TASK_INSTANCE";
createTable(tableName, "c1 varchar(30), c2 int, c3 varchar(30)");
CallableStatement cs = connection.prepareCall("begin\n" + " insert into " + tableName
+ " values(:col1, :col2, :col3);\n" + "end;");
cs.setString("col1", "aa1");
cs.setInt("col2", 111);
cs.setString("col3", "bb1");
cs.execute();
cs.setInt("col2", 222);
cs.setString("col1", "aa2");
cs.setString("col3", "bb2");
cs.execute();
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("select * from " + tableName);
int i = 0;
while (rs.next()) {
i++;
assertEquals(rs.getString(1), "aa" + i);
assertEquals(rs.getInt("c2"), 111 * i);
assertEquals(rs.getString("c3"), "bb" + i);
}
assertEquals(i, 2);
// oracle:
// val is aa1, 111, bb1
// val is aa2, 222, bb2
}