Toad for Oracle Base Edition (64-bit)
25.1.20.5192
Oracle Client : 23.7.0.25.01
If I have DBMS_Output enabled on the "DBMS_OUTPUT" tab I intermittently get ORA-03120: two-task conversion routine: integer overflow, errors when calling code which might be outputting dbms_output.put_lines. For some anonymous blocks, it will happen at the same point every time.
Observations:
If I run in SQL*Plus with server output on, I don't see the error
If I run in SQL*Developer, I don't see the error
In TOAD on the DBMS_OUTPUT tab:
a) If I have output enabled and Run a block, I see this issue intermittently
b) If I have output disabled and Run a block, I don't see this issue
c) If I run via Execute as Script, with set serveroutput on, I don't see this issue and DBMS_OUTPUT tab also displays output correctly
This points to an issue with the DBMS_OUTPUT tab in Toad and prevents me from debugging using dbms_output, which I use a lot.
A few have reported this but I haven't been able to reproduce it. I think it has to do with what is actually outputted (number/length of lines, maybe something else)
Can you send me the output you were expecting when the error occurred?
Also, as a workaround, put this in Toad.ini.
Under [SETTINGS], add this line: FetchDBMSOutputByLob=0
As is often the case, sigh. I have many instances with a range of Oracle versions and at the moment it seems to be happening on one particular on Oracle Database 19c Standard Edition 2, 19.25.0.0.0 on AWS, regardless of schema on that instance.
I have another 19.25 AWS instance and it doesn't happen. The problem is, the one it's happening on is our Dev instance, so it's crippling development at the moment.
Tried FetchDBMSOutputByLob=0, even restarted Toad, still happens.
An update, I had changed FetchDBMSOutputByLob=0, whilst Toad was open and it seemed to remove it when I closed Toad. So I set it whilst Toad was closed and the error is currently not happening .
Yeah, I forgot to mention, you should only attempt to change Toad.ini settings while Toad is not running. Toad reads that file into memory when it starts up, and writes to it occasionally (including on shutdown)
Interesting. I have that setting checked too, but couldn't reproduce it with your pl/sql block.
Can you take out the FetchDBMSOutputByLob setting and uncheck Trim Whitespace to see if the that setting really solves it?
For what it's worth, this is the PL/SQL block that I use to fetch DBMS Output when there are more than 10 lines of data returned using the line-by-line method. I started with the CLOB technique because users with lots of dbms output reported that the line-by-line method (which is what we used before, and which is what we use when FetchDBMSOutputByLob=0 is set) was too slow.
So, maybe someone smarter than me can find the problem in the pl/sql block below.
DECLARE
v_lines SYS.DBMS_OUTPUT.chararr;
v_line NVARCHAR2 (32767);
v_clob CLOB;
v_cnt NUMBER;
v_output NVARCHAR2 (32767);
BEGIN
v_cnt := :inOutCnt;
SYS.DBMS_OUTPUT.get_lines (v_lines, v_cnt);
:inOutCnt := v_cnt;
IF v_lines.COUNT > 0 THEN
DBMS_LOB.createtemporary (v_clob, FALSE, DBMS_LOB.session);
DBMS_LOB.open (v_clob, DBMS_LOB.lob_readwrite);
v_output := NULL;
FOR line IN 1 .. v_lines.COUNT
LOOP
v_line := regexp_replace(v_lines(line), '^[[:space:]]+|[[:space:]]+$');
v_line := REPLACE(v_line, CHR (0));
IF LENGTHB(v_output || v_line || CHR(13) || CHR(10)) > 32767 THEN
DBMS_LOB.writeappend (v_clob, LENGTH(v_output), v_output);
v_output := null;
END IF;
v_output := v_output || v_line || CHR (13) || CHR (10);
END LOOP;
if v_output is not null then
DBMS_LOB.writeappend (v_clob, LENGTH(v_output), v_output);
END IF;
:outLines := v_clob;
DBMS_LOB.freetemporary (v_clob);
ELSE
:outLines := null;
END IF;
END;
SYS.DBMS_OUTPUT.chararr is defined as type chararr is table of varchar2(32767) index by binary_integer;
Which could be UTF-8 character set and you are assigning to nvarchar2, which could UTF-16. I think v_line and v_output should be varchar2(32767)
I don't think you should do this v_line := regexp_replace(v_lines(line), '^[[:space:]]+|[[:space:]]+);
as that will get rid of leading whitespace. v_line := regexp_replace(v_lines(line), '[[:space:]]+$');
Is more correct.
I have this comment in our source re: nvarchar2 // keep as NVARCHAR2 to account for multibyte chars
I think someone had arrows or somesuch. Maybe I should only use NVARCHAR2 on non-utf8 DBs. Also, it's interesting - if you just DBMS-outputted 10 lines, as in your pl/sql block, then my block really has no work to do other than to determine that there is nothing else to fetch. So I think the problem is in the call to SYS.DBMS_OUTPUT.get_lines (v_lines, v_cnt). The :inOutCnt param a few lines up is set to 1000.
The regex to remove leading and trailing is intentional. Right or wrong, the "trim whitespace" option removes leading and trailing. I'm not saying that can't change, but that's why it is the way it is.
Do you have any more info on that ORA-03120? Sometimes the error messages include "on line N in package BLAH".
Sorry, I read the whitespace trimming as end of line. I actually apply indentation to dbms_output sometimes, to help follow flow, so that explains why I lose that! It would be more useful as a dropdown (Trim all, Trim trailing, trim none) perhaps?
It's not safe to assign varchar2 (e.g. ANSI or UTF-8) to Nvarchar2 (always uses UTF-16). The codepoints don't match and it'll more than likely cause data corruption. Perhaps that is the source of the errors.
If you must concatenate, you need to use to_nchar(<your varchar2>)
DECLARE
v_lines SYS.DBMS_OUTPUT.chararr;
v_line NVARCHAR2 (32767);
v_clob CLOB;
v_cnt NUMBER;
v_output NVARCHAR2 (32767);
BEGIN
v_cnt := :inOutCnt;
SYS.DBMS_OUTPUT.get_lines (v_lines, v_cnt);
:inOutCnt := v_cnt;
IF v_lines.COUNT > 0 THEN
DBMS_LOB.createtemporary (v_clob, FALSE, DBMS_LOB.session);
DBMS_LOB.open (v_clob, DBMS_LOB.lob_readwrite);
v_output := NULL;
FOR line IN 1 .. v_lines.COUNT
LOOP
-- Convert the VARCHAR2 line to NVARCHAR2 before concatenating
v_line := TO_NCHAR(regexp_replace(v_lines(line), '^[[:space:]]+|[[:space:]]+$'));
v_line := REPLACE(v_line, CHR (0)); -- Only keep this if necessary
-- Concatenate single-byte characters CHR(13) and CHR(10) as NVARCHAR2
IF LENGTH(v_output || v_line || TO_NCHAR(CHR(13)) || TO_NCHAR(CHR(10))) > 32767 THEN
-- Write to CLOB
DBMS_LOB.writeappend (v_clob, LENGTH(v_output), v_output); -- Use LENGTH for characters
v_output := NULL;
END IF;
v_output := v_output || v_line || TO_NCHAR(CHR(13)) || TO_NCHAR(CHR(10));
END LOOP;
-- Handle remaining output
IF v_output IS NOT NULL THEN
DBMS_LOB.writeappend (v_clob, LENGTH(v_output), v_output); -- Use LENGTH for characters
END IF;
:outLines := v_clob;
DBMS_LOB.freetemporary (v_clob);
ELSE
:outLines := null;
END IF;
END;
I'm not sure about chr(0) replacement?? But it might pay to declare nvarchar2(1) constants for chr(13) and chr(10)
Uses VARCHAR2 instead of NVARCHAR2 (this seems better than trying to make NVARCHAR2 safe)
Adds an option to Preserve, Trim, Trim Leading, or Trim Trailing whitespace
Adds an option to use CLOBs to fetch dbms output (so you don't have to edit Toad.ini with FetchDBMSOutputByLob=0 if it is still broken, or if not using NVARCHAR2 causes problems for anyone)
Please let me know if I missed anything, and - if you can test the beta next week - if #1 above solves the ORA-03120.
.