Print Thread
Strange issue with dbms_output
#64730 07/21/23 02:06 PM
Joined: Nov 2011
Posts: 28
Netherlands
E
EvROCS Offline OP
Member
OP Offline
Member
E
Joined: Nov 2011
Posts: 28
Netherlands
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 Files
2023-07-20_PD_Output_Error.png (28.86 KB, 85 downloads)

Erik van Roon
EvROCS

Re: Strange issue with dbms_output
EvROCS #64731 07/24/23 09:23 AM
Joined: Aug 1999
Posts: 22,206
Member
Offline
Member
Joined: Aug 1999
Posts: 22,206
It's the limit of a dbms_output buffer line in PL/SQL Developer. We'll investigate if we can increase this.


Marco Kalter
Allround Automations
Re: Strange issue with dbms_output
EvROCS #64732 07/24/23 12:19 PM
Joined: Mar 2007
Posts: 110
Hazerswoude-Dorp, NL
Member
Offline
Member
Joined: Mar 2007
Posts: 110
Hazerswoude-Dorp, NL


Certainty of death, small chance of succes,
What are we waiting for... (Gimly)
Re: Strange issue with dbms_output
EvROCS #64736 07/25/23 09:30 AM
Joined: Nov 2011
Posts: 28
Netherlands
E
EvROCS Offline OP
Member
OP Offline
Member
E
Joined: Nov 2011
Posts: 28
Netherlands
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 Files
2023-07-25_PD_Output_Okay.png (48.51 KB, 64 downloads)

Erik van Roon
EvROCS

Re: Strange issue with dbms_output
EvROCS #64755 08/10/23 02:48 PM
Joined: May 2007
Posts: 102
Tiel, The Netherlands
M
Member
Offline
Member
M
Joined: May 2007
Posts: 102
Tiel, The Netherlands
May check if NLS_LANG and DB characterset are the same?

Re: Strange issue with dbms_output
EvROCS #64768 08/22/23 04:33 PM
Joined: Nov 2011
Posts: 28
Netherlands
E
EvROCS Offline OP
Member
OP Offline
Member
E
Joined: Nov 2011
Posts: 28
Netherlands
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.


Erik van Roon
EvROCS

Re: Strange issue with dbms_output
EvROCS #64772 08/23/23 07:32 PM
Joined: Jul 2004
Posts: 592
W
Member
Offline
Member
W
Joined: Jul 2004
Posts: 592
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.

Re: Strange issue with dbms_output
EvROCS #64773 08/23/23 08:16 PM
Joined: Nov 2011
Posts: 28
Netherlands
E
EvROCS Offline OP
Member
OP Offline
Member
E
Joined: Nov 2011
Posts: 28
Netherlands
Hmmm? Interesting! Not sure, but could very well be.
I'll check tomorrow morning.


Erik van Roon
EvROCS


Moderated by  support 

Link Copied to Clipboard
Powered by UBB.threads™ PHP Forum Software 7.7.4
(Release build 20200307)
Responsive Width:

PHP: 7.1.33 Page Time: 0.071s Queries: 17 (0.031s) Memory: 2.5315 MB (Peak: 3.0380 MB) Data Comp: Off Server Time: 2024-04-29 15:09:19 UTC
Valid HTML 5 and Valid CSS