using
Toad for Oracle DBA Edition (64-bit)
Add-Ons: DB Admin Module
26.1.193.8012
using oracle client (21c)
using oracle db (19c)
when querying table with XMLTYPE datatype I'm getting
[Error] Execution (1: 1): ORA-03120: two-task conversion routine: integer overflow
Not facing this in toad 25 version.
in sqlplus no issue.
if i don't use oracle client, but default client with toad - it's ok but then it's masking the issue with toad + oracle cleint
Toad uses the OCI in unicode mode, and XMLTYPE with Unicode has caused a lot of problems over the years. I thought recent versions have solved it, but maybe not in your case. SQL*Plus does not use unicode, which may be why you don't see the problem there.
I just connected to 19c client on a 21c database. I created a table with two XMLTYPE columns, inserted a few rows including data in both XMLTYPE columns and did not have a problem. It may be due to the data in your table, or some other factor.
If you can send me your case (table definition and a row that can reproduce the problem) I will try again.
CREATE TABLE CONFIG_MAT_MERGER
(
PROFILE VARCHAR2(20 BYTE),
CONFIG SYS.XMLTYPE
)
XMLTYPE CONFIG STORE AS CLOB ;
SET DEFINE OFF;
Insert into CONFIG_MAT_MERGER
(PROFILE, CONFIG)
Values
('JP_MAT', XMLTYPE(''||CHR(10)||
' accessions'||CHR(10)||
' updates'||CHR(10)||
' assignees'||CHR(10)||
' inventors'||CHR(10)||
' agents'||CHR(10)||
' examiners'||CHR(10)||
' publications'||CHR(10)||
' corrections'||CHR(10)||
' applications'||CHR(10)||
' relateds'||CHR(10)||
' priorities'||CHR(10)||
' metaData'||CHR(10)||
' miscellaneous'||CHR(10)||
' classificationIpcCurrent'||CHR(10)||
' classificationIpc'||CHR(10)||
' classificationEclaCurrent'||CHR(10)||
' classificationLocarno'||CHR(10)||
' classificationUsCurrent'||CHR(10)||
' classificationUs'||CHR(10)||
' classificationNational'||CHR(10)||
' classificationJpCurrent'||CHR(10)||
' classificationJp'||CHR(10)||
' pubTitles'||CHR(10)||
' trialAppealData'||CHR(10)||
' legalStatusData'||CHR(10)||
' abstracts'||CHR(10)||
' imageRefs'||CHR(10)||
' claimed'||CHR(10)||
' descriptionsOriginal'||CHR(10)||
' amendments'||CHR(10)||
' titleEnhanced'||CHR(10)||
' abstractEnhanced'||CHR(10)||
' abstractImageRefs'||CHR(10)||
' abstractDocumentation'||CHR(10)||
' abstractDocumentationImgRefs'||CHR(10)||
' abstractDocumentationAsImgRef'||CHR(10)||
' classificationDwpi'||CHR(10)||
' manualCodesChemical'||CHR(10)||
' manualCodesEngineering'||CHR(10)||
' manualCodesElectrical'||CHR(10)||
' chemicalLinkCodes'||CHR(10)||
' chemicalUnlinkCodes'||CHR(10)||
' keywordIndexing'||CHR(10)||
' polymerIndexing'||CHR(10)||
' polymerCodes'||CHR(10)||
' patentCitations'||CHR(10)||
' literatureCitations'||CHR(10)||
' citingPatents'||CHR(10)||
''));
COMMIT;
i'm on 19c db , with 21c client
Thanks. Yes, I got the client/db versions backwards.
I get the same problem as you when I use a 21c or 23.26 client, but a 19c client works fine. Specifically, I am using a 19.13 client, but I would guess that any 19.x would work.
I think this is an Oracle issue. I also get errors in Toad 25 when using my 23.26 client. Did you use an older client with Toad 25?
Also, and maybe not important at this point because I reproduced the problem with another table, but the insert statement you gave me a few replies up gives an error.
Earlier you said "if i don't use oracle client, but default client with toad - it's ok but then it's masking the issue with toad + oracle client". It's not really masking an issue. In this case, Toad is using its own code to connect to the database, not the Oracle client. It's not masking anything, it's just that whatever problem the 21.x and 23.x clients have, we don't have.
further testing
client 21c
server 19.22 - issue
server 19.30 - no issue
after using the below workaround then it works for me
alter session set statistics_level=ALL;
--default TYPICAL
--reference
19c: ORA-03120: Two-task Conversion Routine: Integer Overflow Error When Running coe_xfr_sql_profile.sql Script
KB771892
Thanks for the follow up.
Incase anyone else is interested, here is a link to the Oracle KB article.
https://support.oracle.com/support/?anchorId=&documentId=KB771892&page=sptemplate&sptemplate=km-article
