• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

in out char parameter

 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

Need a help. I am trying to call a pl/sql function from java. The pl/sql function has IN OUT CHAR parameter as one of the parameters. I am sending only 1 character in to it and assigning that char value to a temporary char value. But getting pl/sql numeric value error, further debugging shows the parameter's length is like 32767. Could not get how to resolve it. I am using Oracle 12c db and ojdbc7.jar to connect to it. Below is the java code



Below is the pl/sql code and table structure,





Any help on this is appreciated.

Thanks
 
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can you post the full error you get?

CHARs are fixed length, and I expect your CHAR parameter is the max length for one, which is too large to fit in your table column.

By the way, why are you defining the VARCHAR2 column in terms of bytes and not characters?
 
Bartender
Posts: 598
26
Oracle Notepad Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Does the SP work from the console via EXEC?
 
RajaR Sekhar
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:Can you post the full error you get?

CHARs are fixed length, and I expect your CHAR parameter is the max length for one, which is too large to fit in your table column.

By the way, why are you defining the VARCHAR2 column in terms of bytes and not characters?



Thanks for looking into it Dave. Below is the error message,

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Error message is coming, when i am assigning

tempb := b;

I tried to check the length of the char parameter and it comes to 32767..

The temp table is being used only to debug.. the VARCHAR2 column is being interpreted as bytes, due to the nls_character_set in Oracle database. Strange thing is, if i remove the IN OUT specification in PL/SQL, this program works.
 
RajaR Sekhar
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Brian Tkatch wrote:Does the SP work from the console via EXEC?



Yeah, the function works fine from pl/sql



And the function works when i remove the IN OUT specification of CHAR.. the length is 1 in this case instead of 32767.
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


It works fine because your CHAR is defined as 20 characters long.

The one coming into the SP via Java is 32767 characters long (it seems), because there's nothing there to say otherwise.

Now, I've never used a CHAR field so I'm not sure how you would actually tell it to only send a single character.
 
Brian Tkatch
Bartender
Posts: 598
26
Oracle Notepad Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:I've never used a CHAR field so I'm not sure how you would actually tell it to only send a single character.


CHAR(1). CHAR is the same as VARCHAR2, but with implicit padding. But you knew that. What am i missing?
 
RajaR Sekhar
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:

It works fine because your CHAR is defined as 20 characters long.

The one coming into the SP via Java is 32767 characters long (it seems), because there's nothing there to say otherwise.

Now, I've never used a CHAR field so I'm not sure how you would actually tell it to only send a single character.



Actually, it works with this as well,



From the java code, i am doing as below, to make sure i am sending 1 character. Also, if i remove the IN OUT specification for CHAR parameter, there is no error..



The one, I first posted contains, which is deprecated, but i thought i would give a try..

 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Brian Tkatch wrote:

Dave Tolls wrote:I've never used a CHAR field so I'm not sure how you would actually tell it to only send a single character.


CHAR(1). CHAR is the same as VARCHAR2, but with implicit padding. But you knew that. What am i missing?



The JDBC side.
How do you actually tell that the Java String supplied is only 1 character and should not be padded.
It seems that the driver is interpreting the CHAR as CHAR(32767).

To be honest, this does ring a bell of a driver bug from years ago.
 
RajaR Sekhar
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:

Brian Tkatch wrote:

Dave Tolls wrote:I've never used a CHAR field so I'm not sure how you would actually tell it to only send a single character.


CHAR(1). CHAR is the same as VARCHAR2, but with implicit padding. But you knew that. What am i missing?



The JDBC side.
How do you actually tell that the Java String supplied is only 1 character and should not be padded.
It seems that the driver is interpreting the CHAR as CHAR(32767).

To be honest, this does ring a bell of a driver bug from years ago.



I have been googling on this, i did found out some posts that has reported exact behaviour in 2002, but there were no replies to that.. found one reply that states to mention the max size in the registerOutParameter which i am already doing. Is there some property that we set at the connection level to not do the padding you are mentioning?
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

PL/SQL translates this as CHAR(1), so it works.


this is telling it the out parameter is a single character, it has no effect on the input character.
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Now, of course, my next question is going to be, does this have to be a CHAR?
The column it's going into isn't a CHAR so I'm not sure why you are using one.

If it does, then my next suggestion is to simply hack the input down to 32 characters when you do the assignment in the SP.
 
RajaR Sekhar
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:Now, of course, my next question is going to be, does this have to be a CHAR?
The column it's going into isn't a CHAR so I'm not sure why you are using one.

If it does, then my next suggestion is to simply hack the input down to 32 characters when you do the assignment in the SP.



Unfortunately, cannot change any of the pl/sql part, cause i have hundreds of those. I was trying to do a simple example program that will reproduce the issue, which it did.. Just need something from java or jdbc level that will help in resolving this situation..
 
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

I am using Oracle 12c db and ojdbc7.jar to connect to it. Below is the java code



We had a very similar issue with Oracle 12c and ojdbc7.jar. We found out that using the old ojdbc6.jar instead, solved the issue. Just in case you still have not found a solution to this problem.
 
Marshal
Posts: 79151
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you for that solution . Unfortunately, what is going to happen when the version 6 jar ceases to be available?
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic