How to set OracleCallableStatement.Regist… to use byte instead of char?

[sharok das]

In the OracleCallableStatement function called registerOutParameter (shown below), how do I ensure that the maxLength is in bytes and not Chars??? (Java Chars are two bytes/char but I like to be specify 5 bytes. The explaination give below says it depends on OracleConnection.getDataSizeUnits(). But this function seem to be not working and nothing is found on google in regards to it. My database charset has char = 1 byte. And the size should be exactly 5 bytes. But java char has 2 bytes/char. The functionionality they are specifying in the spec "Depending on the value of OracleConnection.getDataSizeUnits(), maxLength will be measured in bytes or characters." does not seem to work.

------------------
void registerOutParameter(java.lang.String parameterName, int sqlType, int scale, int maxLength) throws java.sql.SQLException
Special Oracle version of registerOutParameter for registering CHAR, VARCHAR, LONG, RAW and LONG RAW columns. Depending on the value of OracleConnection.getDataSizeUnits(), maxLength will be measured in bytes or characters.

Parameters:
parameterName - the name of the parameter
sqlType - SQL type code defined by java.sql.Types.
scale - the desired number of digits to the right of the decimal point. It must be greater than or equal to zero.
maxLength - maximum length of the column, specified in bytes or characters.

Throws: java.sql.SQLException - if an error occurs (conversion or database-access error)
Since: 10i Release 1
-------------------

The link to the function:
docs.oracle.com/cd/E18283_01/appdev.112/e13995/oracle/jdbc/OracleCallableStatement.html#registerOutParameter_java_lang_String__int__int__int_

Thanks, Sharok

[Ireneusz Kordal]

Why do you need 5 bytes, could you explain what kind of data is stored in this field ?

[sharok das]

The data is alphanumeric of exactly char(5). The existing stored procedure has an out variable that is exactly char(5). There is also a field on a table that is char(5) for this. The procedure inserts the generated value before passing it back to the java program. The same java program will need to run against different oracle databases. Some of these databases have a charsets where each char occupies 1 byte and in some others each char occupies 2 bytes. If the java program does not set the out parameter to the proper number of bytes, the stored procedure crashes since the same variable is used to do insert into the table from the stored procedure. The crash does not happen because the procedure is trying to return the value to java, it crashes (due to size mismatch) while doing the insert the generated value into the table. The size of the variable set by the registerOutParamet is automatically propagated into the stored procedure so it must have exact bytes depending on the charset, either 5 bytes or 10 bytes. In the java function registerOutParameter I specify 5 for the maxLength and CHAR for the sqlType. It should automatically detect the database charset (1 byte/char or 2 bytes/char) and set the byte size accordingly. The documentation does say that this is automatically detected:
"Depending on the value of OracleConnection.getDataSizeUnits(), maxLength will be measured in bytes or characters."
But that does not seem to be the case. When I test the java program against a database with a charset where i know it has 1 byte/char, it crashes saying there are 10 bytes instead of 5. So, the data size is not automatically detected by the so called "OracleConnection.getDataSizeUnits()" function as the documentation specifies. BTW, I could not find any trace of this "OracleConnection.getDataSizeUnits()" anywhere on google.

Here is the error, the java program using jdbc which mistakenly uses char(2 bytes/char) instead of byte:
String spcall = "{call es_job.create_user_job(?,?,?,?)}";
OracleCallableStatement Sstmt = (OracleCallableStatement)connection.prepareCall( spcall );
// set the in param
Sstmt.setString("program", "mft-j100");
Sstmt.setString("arguments", "java");
Sstmt.setString("executable", "mft-j100");
Sstmt.registerOutParameter("jobid", OracleTypes.CHAR, 0, 5);
Sstmt.execute();
Sstmt.close();
return true;



Run error:
java.sql.SQLException: ORA-12899: value too large for column "R13"."JOB"."ID" (actual: 10, maximum: 5)
ORA-06512: at "R13.ES_JOB", line 261
ORA-06512: at "R13.ES_JOB", line 339
ORA-06512: at line 1

Thanks, Sharok

[Martin Vashko]

The obvious solution would be to declare another variable as CHAR(5) in the stored procedure to hold the value of the ID and use it to put that value into a table, then, at the end of the procedure, copy this variable into the output parameter. This way you could completely avoid all this trickery and use JDBC's normal registerOutParameter without bothering about the output parameter's length.

Anyway, a much better design would certainly be to declare the job ID as a number and feed it from a sequence. That is the way these things should be done on Oracle and it is guaranteed to work on all versions since at least 8, regardless of their character sets. ID is just an ID, you should not try to store additional information into it. If you need an additional information, store it into a column of its own.

[Ireneusz Kordal]

Hello,

Try to wrap a call to the procedure in this way:

   CallableStatement cs = connection.prepareCall( "declare x char(5 byte); begin name( x ); ? = x; end; " );
   cs.registerOutParameter( 1, java.sql.Types.VARCHHAR );
   cs.executeUpdate();
   String s = cs.getString( 1 );

[sharok das]

What about other variables in the call? If currently it is:

OracleCallableStatement Sstmt = (OracleCallableStatement)connection.prepareCall( "{call es_job.create_user_job(?,?,?,?)}" );

What would it look like based on the prepare method you are suggesting?

[Martin Vashko]

That's a neat idea, Ireneusz!

To elaborate for sharok:

   CallableStatement cs = connection.prepareCall( "declare x char(5 byte); begin es_job.create_user_job(?,?,?,x); ? = x; end; "); 
   Sstmt.setString(1, "mft-j100"); 
   Sstmt.setString(2, "java"); 
   Sstmt.setString(3, "mft-j100"); 
   cs.registerOutParameter(4, java.sql.Types.VARCHHAR);  
   cs.executeUpdate();  
   String s = cs.getString(4);  

You'll just have to bind your parameters by position, not by name; make sure I didn't get the parameter positions wrong.

[Ireneusz Kordal]

Ireneusz Kordal wrote:Hello,

Try to wrap a call to the procedure in this way:

   CallableStatement cs = connection.prepareCall( "declare x char(5 byte); begin name( x ); ? = x; end; " );
   cs.registerOutParameter( 1, java.sql.Types.VARCHHAR );
   cs.executeUpdate();
   String s = cs.getString( 1 );

Sorry i've done a small mistake - assigment in PL/SQL is not = but := ,
should be:

 connection.prepareCall( "declare x char(5 byte); begin name( x );    ? :=  x    ; end; " );

[sharok das]

Yes, that is it. It is working well now with the last fix. It's great. Thanks very much. You were right on. Thanks again. Sharok