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

Question

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?

I am forbidden to make any database changes. It is external database and from that point of view, it is 'read only' for me. — Invader
– Invader, Commented Jan 9, 2017 at 14:58
take a look here, might be the same issue, stackoverflow.com/questions/17598969/… — Rolando F
– Rolando F, Commented Jan 9, 2017 at 14:59

Invader · Accepted Answer · 2017-01-10 10:28:02Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related