sharok das

Greenhorn
+ Follow
since Jan 04, 2012
Merit badge: grant badges
For More
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
0
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by 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
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?



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
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
Has anyone found a solution for this problem of: java.sql.SQLException: ORA-01460: unimplemented or unreasonable conversion requested
Thanks
12 years ago