Posted By: EvROCS Strange issue with dbms_output - 07/21/23 02:06 PM
This is most probably some sort of settings problem, but I'm just hoping somebody here has an idea how I can fix this.

Below problem occurs on my customer laptop in PLSQL Developer.
My coworkers don't have this issue and I don't have this issue on my own laptop (having, as far as I know the same plsql developer settings as my work laptop).

plsql developer version: 15.0.4.2064
Database version : 19.19
OS: Windows 10 (customer laptop) Windows 11 (Own laptop)

The issue
dbms_output.put_line is supposed to accept varchar2 strings up to 32.767 in length.
But on my customer laptop, if I pass it a string of 32.513 or more I get a "PL/SQL numeric or value error: character string buffer too small"

See attached screenshot
Running:
begin
dbms_output.put_line (rpad('X',32513,'X'));
end;
/

produces the error.
Doing the same thing but rpadding it to 32512 runs without problems.
As stated above, on all other laptops doing the exact same thing but rpadded to the max of 32767 is not a problem at all.

The limit of 32512 is 255 lower than the limit of put_line, with is I think a suspicious number.

Anybody any idea what could be causing this??

Attached picture 2023-07-20_PD_Output_Error.png
Posted By: Marco Kalter Re: Strange issue with dbms_output - 07/24/23 09:23 AM
It's the limit of a dbms_output buffer line in PL/SQL Developer. We'll investigate if we can increase this.
Posted By: koja Re: Strange issue with dbms_output - 07/24/23 12:19 PM
Maybe combine this with: https://forums.allroundautomations.com/ubb/ubbthreads.php?ubb=showflat&Number=64458#Post64458 ?
Posted By: EvROCS Re: Strange issue with dbms_output - 07/25/23 09:30 AM
Are you sure Marco?

As I said, I only see this behavior on one single machine (customer laptop).
When I run this on my own laptop with the exact same version of PLSQL Developer (15.0.4.2064) I can go up to the 32.767 limit of the varchar2 input parameter of put_line without any problems. (See screenshot).

If this is due to a limit in PLSQL Developer, I would expect to hit this problem on any installation of (this version of) PLSQL Developer.
Or am I missing something?

Attached picture 2023-07-25_PD_Output_Okay.png
Posted By: Marcel Hoefs Re: Strange issue with dbms_output - 08/10/23 02:48 PM
May check if NLS_LANG and DB characterset are the same?
Posted By: EvROCS Re: Strange issue with dbms_output - 08/22/23 04:33 PM
Sorry Marcel, didn't notice your reply until now.

NLS_LANG shouldn't be set to the characterset of the DB but to the characterset of the client.
It is used so the DB knows FROM what to convert incoming stuff from the client.
Setting it equal to the DB Characterset is a mistake that is very often made, and can lead to misformed characters in the database.

That being said, I highly doubt that it's a characterset issue.
It could have been with any real-world data.
But the test statement I put in my original post uses just a long string of nothing but repeated capital X's.
If there was a characterset mismatch it would mean that the X consists of more bytes in one characterset than in the other.
Possible, but because the test string I'm trying to write is *only* X's
Each X on client side consists of the same amount of bytes.
Each X on DB side consists of the same amount of bytes.

So if e.g. on DB side the X would be 2 bytes and on client side 1 byte, I would expect to only be able to write floor(32.767 / 2) = 16.383 X's.
But what I see is that I can write 32.512 X's.
That's 255 bytes short of the maximum of 32.767, Which I can not explain with characterset issues.
Posted By: Worker Re: Strange issue with dbms_output - 08/23/23 07:32 PM
Since this only happens on a single machine, does that machine maybe have a different Oracle Client version installed? Compare the info in the top right corner of Help > About on this computer with one that doesn't have the problem.
Posted By: EvROCS Re: Strange issue with dbms_output - 08/23/23 08:16 PM
Hmmm? Interesting! Not sure, but could very well be.
I'll check tomorrow morning.
© Allround Automations forums