• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

IN OUT CHAR parameter

 
RajaR Sekhar
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • 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
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • 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?
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • 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
  • 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
  • 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
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • 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: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • 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
  • 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
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • 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
  • 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
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • 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
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • 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
  • 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..
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic