Home » Developer & Programmer » JDeveloper, Java & XML » Very Strange Problem (Oracle 11.1.0.6, Java 1.5)
Very Strange Problem [message #362930] Thu, 04 December 2008 09:41 Go to next message
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 Go to previous messageGo to next message
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.

Re: Very Strange Problem [message #366372 is a reply to message #365659] Sat, 13 December 2008 02:18 Go to previous message
Frank
Messages: 7877
Registered: March 2000
Senior Member
Thanks for reporting back!
Previous Topic: seeing java output in sqlplus
Next Topic: JDevloper 11g Tutorial - Buttons not working
Goto Forum:
  


Current Time: Wed Oct 01 23:12:57 CDT 2014

Total time taken to generate the page: 0.13867 seconds