Passing IN parameter of datatype varchar2 (merged 5) [message #443473] |
Tue, 16 February 2010 00:37  |
anil029
Messages: 15 Registered: February 2010
|
Junior Member |
|
|
Hi,
I have a stored proc which takes IN parameter of datatype varchar2.When I am trying to run the proc it is throwing error that "input buffer too small".The datatype assigned to IN parameter is of varchar2(200) but actually the length of the parameter passed is around 500 characters.
Can anyone suggest me the way to increase the length of Input parameter to 500 characters??
Thanks,
Anil
|
|
|
|
|
Re: Passing IN parameter of datatype varchar2 (merged 5) [message #443485 is a reply to message #443484] |
Tue, 16 February 2010 00:58   |
anil029
Messages: 15 Registered: February 2010
|
Junior Member |
|
|
Hi,
The structure of the code is excactly like below:
CREATE OR REPLACE Procedure myProc
( str_inString IN varchar2 )
IS
Begin
end;
The problem is that when i am passing the value of IN parameter str_inString which is 500 charcters long,it is throwing error that "buffer too small".How to get rid of this problem?
I know that varchar2 can hold value of upto 32767 long characters.This is somethig unusual i found.Can anyone help me to solve this.
Thanks,
Anil
|
|
|
|
|
|
|
|
|
Re: Passing IN parameter of datatype varchar2 (merged 5) [message #443585 is a reply to message #443565] |
Tue, 16 February 2010 07:18   |
anil029
Messages: 15 Registered: February 2010
|
Junior Member |
|
|
Hi all,
As JRowbottom figured out,I am copying the input parameter into a variable inside the procedure and subsequently extracting the comma separated values from it.The proc is somewhat like below:
CREATE OR REPLACE PROCEDURE APL_DOC9.SP_TRAINEE_SCORE (
aBLString IN varchar2,
aReturned_O OUT number)
IS
aVar1 number;
aVar2 char(1);
......
......
aBLStringTmp varchar2(500);
BEGIN
aBLStringTmp:=aBLString;
aVar1:= SUBSTR(aBLStringTmp, 1, INSTR(aBLStringTmp, ',')-1);
aBLStringTmp := SUBSTR(aBLStringTmp, INSTR(aBLStringTmp, ',')+1);
aVar2:= SUBSTR(aBLStringTmp, 1, INSTR(aBLStringTmp, ',')-1);
aBLStringTmp := SUBSTR(aBLStringTmp, INSTR(aBLStringTmp, ',')+1);
.................
.................
.................
This is how i am extracting values for all the variabes to process it further down the code.
The new variable aBLStringTmp used is of datatype varchar2(500).I don't think there should be any problem with the buffer size as i have allocated sufficient size to it.
I hope the issue is clear now to everyone.
So,can anyone please suggest me a way to resolve this issue?
Thanks to everyone for investing their valuable time and looking into this issue.Do let me know if anyone require further details regarding this.
Regards,
Anil
|
|
|
Re: Passing IN parameter of datatype varchar2 (merged 5) [message #443587 is a reply to message #443473] |
Tue, 16 February 2010 07:25   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
The full procedure along with the line number the error is occuring at would be a good start. Details of how you are calling it would probably help as well - what language are you using for the calling code?
If you are calling the procedure from anything other than PL/SQL then try calling it from sqlplus and see if you get the same issue.
One possibility that springs to mind is that the value you are passing in has been blank padded to longer than 500 chars.
|
|
|
Re: Passing IN parameter of datatype varchar2 (merged 5) [message #443588 is a reply to message #443585] |
Tue, 16 February 2010 07:28   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
aVar1 number;
aVar1:= SUBSTR(aBLStringTmp, 1, INSTR(aBLStringTmp, ',')-1);
A number is set to a string, are you sure of this?
aVar2 char(1);
aVar2:= SUBSTR(aBLStringTmp, 1, INSTR(aBLStringTmp, ',')-1);
Are you sure you get only ONE character?
Regards
Michel
|
|
|
|
Re: Passing IN parameter of datatype varchar2 (merged 5) [message #443590 is a reply to message #443473] |
Tue, 16 February 2010 08:02   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Well you still haven't posted the full code or the line number of the error but I strongly suspect the problem is in the lines Michel highlighted.
Put it this way - you are passing a string of comma seperated numbers, correct?
If so, every one of the numbers in that string must be a single digit (less than 10) or this bit of code is going to fail with the error you've described:
aVar2:= SUBSTR(aBLStringTmp, 1, INSTR(aBLStringTmp, ',')-1);
|
|
|
|
Re: Passing IN parameter of datatype varchar2 (merged 5) [message #443611 is a reply to message #443589] |
Tue, 16 February 2010 08:38  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
anil029 wrote on Tue, 16 February 2010 14:55Hi,
The stored proc is getting called from Unix cron job (KSH file).The variable aVar1 is of data type varchar2 not number.Sorry for the wrong info provided and aVar2 can only be one char long.Similarly for other variables.Please suggest.
Thanks,
Anil
I suggest you post the REAL code and avoid wasting our times.
Regards
Michel
|
|
|