Home » SQL & PL/SQL » SQL & PL/SQL » Passing IN parameter of datatype varchar2 (merged 5) (Oracle 9i,Windows XP)
Passing IN parameter of datatype varchar2 (merged 5) [message #443473] Tue, 16 February 2010 00:37 Go to next message
anil029
Messages: 14
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 [message #443483 is a reply to message #443473] Tue, 16 February 2010 00:51 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
can you please copy and paste you code to review please.

did you google

CREATE OR REPLACE Procedure myProc
   ( str_inString IN varchar2 )
IS
Begin
end;

Re: Passing IN parameter of datatype varchar2 (merged 5) [message #443484 is a reply to message #443473] Tue, 16 February 2010 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post your code or one that simulate your case.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Passing IN parameter of datatype varchar2 (merged 5) [message #443485 is a reply to message #443484] Tue, 16 February 2010 00:58 Go to previous messageGo to next message
anil029
Messages: 14
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 #443492 is a reply to message #443485] Tue, 16 February 2010 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With what you posted we can't know what is the error but it is not in the code you posted.

Use SQL*Plus and copy and paste your session.

Regards
Michel

[Updated on: Tue, 16 February 2010 01:09]

Report message to a moderator

Re: Passing IN parameter of datatype varchar2 (merged 5) [message #443495 is a reply to message #443473] Tue, 16 February 2010 01:22 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Anil,

As you are advised earlier by me and Michel please Use SQL*Plus and copy and paste your session.

otherwise HOW we would anyone possibly know what error you are facing?

Re: Passing IN parameter of datatype varchar2 (merged 5) [message #443544 is a reply to message #443485] Tue, 16 February 2010 03:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The most likely problem is that you are copying the input parameter into a variable inside the procedure.

Re: Passing IN parameter of datatype varchar2 (merged 5) [message #443552 is a reply to message #443544] Tue, 16 February 2010 03:56 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I bet that the OP uses DBMS_OUTPUT within the procedure which raises the "buffer too small" error.

Something like
SQL> create or replace procedure prc_test
  2    (p_string in varchar2)
  3  is
  4    l_string varchar2(200);
  5  begin
  6    l_string := lpad(p_string, 1e6, p_string);
  7    dbms_output.put_line(l_string);
  8  end;
  9  /

Procedure created.

SQL> exec prc_test('abc');
BEGIN prc_test('abc'); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SCOTT.PRC_TEST", line 6
ORA-06512: at line 1


SQL>
icon12.gif  Re: Passing IN parameter of datatype varchar2 (merged 5) [message #443561 is a reply to message #443552] Tue, 16 February 2010 05:03 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
Would say the ORA-06502 error does not come not from dbms_output.put_line in line 7 but from the assignment of a very big string to varchar2(200) in line 6, bet lost Razz.

[Updated on: Tue, 16 February 2010 05:06]

Report message to a moderator

Re: Passing IN parameter of datatype varchar2 (merged 5) [message #443565 is a reply to message #443561] Tue, 16 February 2010 05:10 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Absolutely true! My bad.

However:
SQL> create or replace procedure prc_test
  2    (p_string in varchar2)
  3  is
  4  begin
  5    dbms_output.put_line(lpad(p_string, 1e6, p_string));
  6  end;
  7  /

Procedure created.

SQL>
SQL> exec prc_test('abcde')
BEGIN prc_test('abcde'); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SCOTT.PRC_TEST", line 5
ORA-06512: at line 1


SQL>
Re: Passing IN parameter of datatype varchar2 (merged 5) [message #443585 is a reply to message #443565] Tue, 16 February 2010 07:18 Go to previous messageGo to next message
anil029
Messages: 14
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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
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 #443589 is a reply to message #443588] Tue, 16 February 2010 07:55 Go to previous messageGo to next message
anil029
Messages: 14
Registered: February 2010
Junior Member
Hi,
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
Re: Passing IN parameter of datatype varchar2 (merged 5) [message #443590 is a reply to message #443473] Tue, 16 February 2010 08:02 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 #443594 is a reply to message #443473] Tue, 16 February 2010 08:05 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Having reread your last post I realise it might not be numbers. Never the less, unless everything between the commas in the string is a single character or digit you'll get your error from that line.
Re: Passing IN parameter of datatype varchar2 (merged 5) [message #443611 is a reply to message #443589] Tue, 16 February 2010 08:38 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
anil029 wrote on Tue, 16 February 2010 14:55
Hi,
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

Previous Topic: Lexical Parameter Slow down query
Next Topic: Regarding the SQL (merged 3)
Goto Forum:
  


Current Time: Sun Dec 11 08:10:09 CST 2016

Total time taken to generate the page: 0.09891 seconds