Oracle - Input Buffer

[prasath342002]

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 ?

[Pilgrim333]

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.

[debasisdas]

are you using DBMS_LOB to handle NCLOB ?

[prasath342002]

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)

[prasath342002]

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_PAREN T NCLOB;
for i in (select BODY_TYPE from VEHICLE_ACCESSO RY)
loop
BODY_TYPE_PAREN T := REC.BODY_TYPE; // reporting error
end loop;

[Pilgrim333]

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.

[prasath342002]

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 ?

[Pilgrim333]

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.

[prasath342002]

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 .

[Pilgrim333]

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.

[prasath342002]

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 ?

[Pilgrim333]

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.

[amitpatel66]

How you are writing a data to NCLOB variable?
Please post the source code here for reference

[prasath342002]

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_CHARA CTERSET = AL16UTF16 ( my machine)
NLS_NCHAR_CHARA CTERSET = UTF8 ( other machine)

is this make any difference ?

[Pilgrim333]

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.