469,352 Members | 1,768 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,352 developers. It's quick & easy.

Oracle - Input Buffer

Hi ,

I am having the NCLOB field in stored procedure , when i tried in one machine it is executing but in some other machine it is not executing.

getting the below error
ORA-22921: length of input buffer is smaller than amount requested

could any one help us to resolve the problem ?
Nov 4 '08 #1
15 6821
Pilgrim333
127 100+
Hi,

Could you give a better description of what you are doing? When you say other machine, do you mean another database or another client?

Pilgrim.
Nov 4 '08 #2
debasisdas
8,127 Expert 4TB
are you using DBMS_LOB to handle NCLOB ?
Nov 4 '08 #3
Hi,

Could you give a better description of what you are doing? When you say other machine, do you mean another database or another client?

Pilgrim.
Hi ,

Thanks.

i am trying to run the procedure in different machines (same kind of database maintained in both machines)
Version :
Oracle9i Enterprise Edition Release 9.2.0.4.0


But , if i run in my machine . i able to execute successfully.

in case of other machine , i am getting the below error
ORA-22921: length of input buffer is smaller than amount requested

I have a NCLOB Comparison in the procedure, but if I comment that comparison the procedure is running without any error ( in other machine also)
Nov 5 '08 #4
are you using DBMS_LOB to handle NCLOB ?
i am using NCLOB in for loop cursor to fetch records from table having NCLOB field.

BODY_TYPE_PARENT NCLOB;
for i in (select BODY_TYPE from VEHICLE_ACCESSORY)
loop
BODY_TYPE_PARENT := REC.BODY_TYPE; // reporting error
end loop;
Nov 5 '08 #5
Pilgrim333
127 100+
Hi,

What tool are you using to execute the procedure? It is a client side setting in your tool that needs to be set.

Pilgrim.
Nov 5 '08 #6
Hi,

What tool are you using to execute the procedure? It is a client side setting in your tool that needs to be set.

Pilgrim.
I am using SQLPLUS for executing. How to set buffer size ?
Nov 5 '08 #7
Pilgrim333
127 100+
Ok,

On both machines give the SQL command show long. It will return a number. If the numbers are different, then set the size on both machines to the same (the greatest amount of both) you can set the size by giving the command set long <size>

Let me know if this works or not.

Pilgrim.
Nov 5 '08 #8
Ok,

On both machines give the SQL command show long. It will return a number. If the numbers are different, then set the size on both machines to the same (the greatest amount of both) you can set the size by giving the command set long <size>

Let me know if this works or not.

Pilgrim.
it is showing 80 in both machine .
Nov 5 '08 #9
Pilgrim333
127 100+
Hi,

I am searching for the right parameter to adjust, but i am comming up with blank. What we can do, is that you do a show all in sql*plus on both machines, get the output into a file and do a compare and see what parameters are different and make them equal. Try that, try again with the procedure and post your results/findings.

Pilgrim.
Nov 5 '08 #10
Hi,

I am searching for the right parameter to adjust, but i am comming up with blank. What we can do, is that you do a show all in sql*plus on both machines, get the output into a file and do a compare and see what parameters are different and make them equal. Try that, try again with the procedure and post your results/findings.

Pilgrim.
i compared both outputs , but there is no difference in parameter. Could you specify the parameter which you tried ?
Nov 5 '08 #11
Pilgrim333
127 100+
I haven't tried anything yet.

Some research led to a difference in the nls_lang settings with the database and the client. The machine it works on can have the same nls_lang settings as the database and the machine it doesn't work on has a different one. Could you check if the nls_lang settings are the same on both machines and that they match the settings on the database?

Info on how you can do this, can be found at:

NLS_LANG

Pilgrim.
Nov 5 '08 #12
amitpatel66
2,367 Expert 2GB
How you are writing a data to NCLOB variable?
Please post the source code here for reference
Nov 5 '08 #13
I haven't tried anything yet.

Some research led to a difference in the nls_lang settings with the database and the client. The machine it works on can have the same nls_lang settings as the database and the machine it doesn't work on has a different one. Could you check if the nls_lang settings are the same on both machines and that they match the settings on the database?

Info on how you can do this, can be found at:

NLS_LANG

Pilgrim.
Hi ,

I execute the above NLS_LANG query in both machines and only one parameter is different.

NLS_NCHAR_CHARACTERSET = AL16UTF16 ( my machine)
NLS_NCHAR_CHARACTERSET = UTF8 ( other machine)

is this make any difference ?
Nov 7 '08 #14
Pilgrim333
127 100+
Hi,

Make sure the char_sets are the same as the one on the machine on which the procedure is working, and then try to run the procedure again on the machine where it is not woking.

Pilgrim.
Nov 7 '08 #15
Hi,

Make sure the char_sets are the same as the one on the machine on which the procedure is working, and then try to run the procedure again on the machine where it is not woking.

Pilgrim.

-----------

Hi ,

After changing the CHAR_SET in both machines , it's working fine.

Thank you for your valuable inputs..
Nov 15 '08 #16

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by stephane.traumat | last post: by
2 posts views Thread by der | last post: by
48 posts views Thread by Michel Rouzic | last post: by
reply views Thread by Biztalk Migration | last post: by
12 posts views Thread by Tarique | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.