| Very Strange Problem [message #362930] |
Thu, 04 December 2008 09:41  |
andydba
Messages: 34
Registered: September 2008
|
Member |
|
|
Hi All,
I am using:
Java Version
============
java version "1.5.0_11"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_11-b03)
Java HotSpot(TM) Client VM (build 1.5.0_11-b03, mixed mode)
Jar files
========
ojdbc5.jar
orai18n.jar
Oracle Database Version
=======================
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Database Character Set
======================
AL32UTF8
Problem
=======
Strored procedure SP_AA sets an out parameter of test_aa.registryno%type to 16 zeros. When I retreive this in java I am getting unexpected results.
Output from Java Program
========================
Output 2 : Length of P2_OUT is : 32766 and First 25 Chars are : '0000000000000000 '
I don't understand how the out parameter length is 32766 and spaces are being appended to the string of zeros? TEST_AA.REGISTRYNO is A CHAR type with a length of 16 chars.
However, when I call the same Stored procedure from pl/sql block, it works fine.
I have attached all the code for reference.
Please Help???
|
|
|
|
| Re: Very Strange Problem [message #365659 is a reply to message #362930] |
Thu, 11 December 2008 20:28  |
andydba
Messages: 34
Registered: September 2008
|
Member |
|
|
Hi All,
I got a solution to this and wanted to share it with you.
Explanation
=============
Adding extra spaces to CHAR datatype when it is used in pl/sql program that is being called from JAVA using JDBC is the default behavior of Oracle. This extra padding of spaces goes to maximum length of CHAR datatype.
Solution
========
Memory Consumption for CHAR Columns Defined as OUT or IN/OUT Variables In PL/SQL, when a CHAR or a VARCHAR column is defined as a OUT or IN/OUT variable, the driver allocates a CHAR array of 32512 chars. This can cause a memory consumption problem. Note that VARCHAR2 columns do not exhibit this behavior.
At previous releases, the solution to the problem was to invoke the Statement.setMaxFieldSize method. A better solution is to use
OracleCallableStatement.registerOutParameter.
Oracle recommends always to call registerOutParameter(int paramIndex,int sqlType, int scale, int maxLength) on each CHAR or VARCHAR column. This method is defined in oracle.jdbc.driver.OracleCallableStatement. Use the fourth argument, maxLength, to limit the memory consumption. maxLength tells the driver how many characters are necessary to store this column. The column will be truncated if the character array cannot hold the column data. The third argument, scale, is ignored by the driver.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
