CODEWITHSUNDEEP
javaoracle-databasejdbcplsqlvarchar
Invader
Invader

Reputation: 105

Single character string from java to plsql varchar2(1) causes 'character string buffer too small'

I have to call Oracle stored procedure from jdbc interface. Procedure takes a record type parameter which includes VARCHAR(2) field:

TYPE cust_account_rec_type IS RECORD (
    ... , status VARCHAR2(1), ... );

My jdbc query string declares record type variable and assigns value to status field, where right side is a query parameter. Then, calls procedure.

p_cust_account_rec.status   := :IN_insert_status;

someprocedure(p_cust_account_rec);

Java query call sets the value for IN_insert_status parameter:

callableStatement.setString("IN_insert_status", "I");
// callableStatement is type of java.sql.CallableStatement

after callableStatement.execute() i got

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Message points to line with this single character varchar variable. However, when i hardcode value in sql query string as:

  p_cust_account_rec.status   := 'I';

it works.

What is the problem with passing single character string in java, or what am I missing?

Upvotes: 1

Views: 1026

Answers (1)

Invader
Invader

Reputation: 105

I have the solution. Actually, problem lies elsewhere. I found that with oracle jdbc driver I am expected to set the query parameter values (in java) with the same order as they appeared in SQL, even thought I've used named parameters, not ? idexed.

So, I had query fragment like

p_cust_account_rec.account_name      := :IN_account_name;  -- VARCHAR2(240)
p_cust_account_rec.status            := :IN_insert_status; -- VARCHAR2(1)

and Java calls like (order important)

callableStatement.setString("IN_insert_status", "I");
callableStatement.setString("IN_account_name", "Some Account Name");

and it ended with IN_account_name = "I", and IN_insert_statis = "Some Account Name", which caused 'character buffer size error' due to insert_status VARCHAR2(1) nature.

I can't 100% confirm this for oracle jdbc cause I'm only reading decompiled code, but the problem was also mentioned http://info.michael-simons.eu/2012/07/23/oracle-jbdc-callablestatements-and-named-parameters/. However, it solved my problem.

Upvotes: 1

Related Questions