Oracle, JBDC, CallableStatements and named parameters

July 23, 2012 by Michael

Update: Please take a note of comment #5 by DSurber. He works for Oracle and sheds some light on named parameters. In short: The names of the parameter do not refer to named placeholders in statement (like in JPA for example) but to PL/SQL named parameters!. Thank you very much.

You might have wondered what happened here:

Well easy, i had fun with the Oracle JDBC Driver and named parameters. I modified one call, added a parameter and set this parameter not in order… No problem, i thought, as i was using a callable statement with named parameter. As i it turns out, named parameters (“:blah”) are not so named, after all, but the order of setting them does matter, see the following tests:

package tests.jdbc;
 
import java.sql.*;
 
import org.junit.*;
 
public class OracleCallableStatementTest {
	protected static Connection connection;
 
	@BeforeClass
	public static void setup() throws Exception {	
		try{
			Class.forName("oracle.jdbc.OracleDriver");
			connection = DriverManager.getConnection("jdbc:oracle:thin:<USER>/<PASS>@<DATABASEHOST>:1521:<SERVICE>");
			PreparedStatement ps = connection.prepareStatement("Create table callable_statement_test(column1 varchar(16), column2 varchar(16))");
			ps.executeUpdate();
			ps.close();
			ps = connection.prepareStatement(
					"CREATE OR REPLACE procedure p_callable_test (p1 IN VARCHAR, p2  IN  VARCHAR) AS " + 
							"BEGIN " + 
							"  INSERT INTO callable_statement_test(column1, column2) values(p1, p2); " + 
							"END;"
					);
			ps.executeUpdate();
			ps.close();
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		}		
	}
 
	@Before
	public void prepareTable() throws SQLException {
		Statement st = connection.createStatement();
		st.executeUpdate("Truncate table callable_statement_test");
		st.close();
	}
 
	private void verify() throws SQLException {
		final Statement st = connection.createStatement();
		ResultSet rs = st.executeQuery("Select * from callable_statement_test");
		rs.next();
 
		String column1 = rs.getString("column1");
		String column2 = rs.getString("column2");
 
		rs.close();
		st.close();		
 
		Assert.assertEquals("1", column1);
		Assert.assertEquals("2", column2);		
	}
 
	@Test
	public void testCallableStatementParameterInOrder() throws SQLException {		
		final CallableStatement ps = connection.prepareCall("{Call p_callable_test(:p1, :p2)}");		
		ps.setString("p1", "1");	
		ps.setString("p2", "2");					
		ps.execute();
		ps.close();
 
		verify();
	}	
 
	@Test
	public void testCallableStatementParameterNoOrder() throws SQLException {
		final CallableStatement ps = connection.prepareCall("{Call p_callable_test(:p1, :p2)}");
		ps.setString("p2", "2");	
		ps.setString("p1", "1");							
		ps.execute();
		ps.close();		
 
		verify();
	}	
 
	@AfterClass
	public static void tearDown() throws SQLException {
		if(connection == null)
			throw new RuntimeException("No connection...");
 
		PreparedStatement ps = connection.prepareStatement("Drop procedure p_callable_test");
		ps.executeUpdate();
		ps.close();
 
		ps = connection.prepareStatement("Drop table callable_statement_test");
		ps.executeUpdate();
 
		connection.close();
	}
}

In this test i actually used the same names of parameters like in the stored procedure and you clearly see p1 should be inserted in column1 and p2 in column2.

#testCallableStatementParameterInOrder succeeds, but #testCallableStatementParameterNoOrder fails. The only difference is that i set p2 before p1… To me, this is a severe bug as named parameters imply a very different behavior.

I strongly advice my colleagues to be very careful about using plain named parameters. The order in which they are set is important!

I could reproduce this behavior with OJDBC 11.1.0.7 and 11.2.0.3 against Oracle 10g and 11g. The example does not work at all with OJDBC 10.2.x.

6 comments

  1. Christian Zerzuben wrote:

    Hi Michael

    Do you know if there is a Oracle SR for this bug?

    Regards
    Christian

    Posted on March 6, 2013 at 11:13 AM | Permalink
  2. Michael wrote:

    Hi Christian,
    no, don’t know… Would be interesting, indeed.

    Regards,
    Michael

    Posted on March 6, 2013 at 11:21 AM | Permalink
  3. omar al kababji wrote:

    I know the post is a bit old, but maybe somebody will be reading it…

    If you take a look at the oracle specs they clearly mention that those methods are not supported anymore, you should use methods like setXXXAtName()

    Reference: http://docs.oracle.com/cd/E246.....apxref.htm

    Posted on March 27, 2013 at 2:28 AM | Permalink
  4. Michael wrote:

    Hi Omar,

    sure i read this.

    Have a look at the official JDBC Api:

    CallableStatement

    I don’t see any of the mentioned methods… I want to code against JDBC, not some specific vendor.

    Posted on March 27, 2013 at 8:27 AM | Permalink
  5. DSurber wrote:

    Don’t use :foo parameter markers. Use standard JDBC ‘?’ parameter markers. Then it will work fine. The String arg in setXXX(String,XXX) is the formal name of the stored procedure parameter not the name of the parameter marker.

    :foo style parameter markers are supported via an Oracle proprietary API not setXXX(String,XXX). setXXX(String,XXX) is a JDBC standard API that is defined to refer to the formal parameter names in the stored procedure, not the :foo parameter markers. Oracle recommends using the JDBC standard parameter markers ‘?’ and supports :foo markers and corresponding proprietary API only for backward compatibility. Note I am not telling you what the proprietary API is intentionally.

    Posted on June 7, 2013 at 5:54 AM | Permalink
  6. Michael wrote:

    Hello Douglas,

    thank you very much for your comment. That sheds some light on the whole thing (and actually solves other problems). I’m well aware of the fact, that PL/SQL functions and procedures do support named parameter (as opposed to Java for example) and i like that very much.

    In this case, the problem was somewhere around 40cm in front of the monitor and didn’t the api very well.

    And please note, i’m not interested in the internal api, as i said before: I want to code against JDBC 😉

    Have a nice day and thanks again!

    Posted on June 7, 2013 at 10:02 AM | Permalink
Post a Comment

Your email is never published. We need your name and email address only for verifying a legitimate comment. For more information, a copy of your saved data or a request to delete any data under this address, please send a short notice to michael@simons.ac from the address you used to comment on this entry.
By entering and submitting a comment, wether with or without name or email address, you'll agree that all data you have entered including your IP address will be checked and stored for a limited time by Automattic Inc., 60 29th Street #343, San Francisco, CA 94110-4929, USA. only for the purpose of avoiding spam. You can deny further storage of your data by sending an email to support@wordpress.com, with subject “Deletion of Data stored by Akismet”.
Required fields are marked *