Re: Troubleshooting ORA-12704 errors

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 3 Oct 2019 21:35:40 +0000
Message-ID: <CWLP265MB1748CF52CBD1E2B796CC3621A59F0_at_CWLP265MB1748.GBRP265.PROD.OUTLOOK.COM>


NLS Parameters:

    NLS_CHARACTERSET                 AL32UTF8
    NLS_NCHAR_CHARACTERSET           AL16UTF16


create table t1 (
        v1 varchar2(10),
        nv1 nvarchar2(10)

);

insert into t1 values('a','a');
commit;

variable m_v1 varchar2(10)
variable m_nv1 nvarchar2(10)

prompt Mismatch
select case v1 when :m_nv1 then 0 end from t1;

prompt Mismatch
select case nv1 when :m_v1 then 0 end from t1;

prompt Match
select case nv1 when :m_nv1 then 0 end from t1;

prompt Match
select case v1 when :m_v1 then 0 end from t1;

Regards
Jonathan Lewis



From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Chris Stephens <cstephens16_at_gmail.com> Sent: 03 October 2019 20:57
To: oracle-l
Subject: Re: Troubleshooting ORA-12704 errors

interesting.

when i change column data types from varchar2 to nvarchar2 everything works fine. i'm not sure i understand why.

On Thu, Oct 3, 2019 at 1:49 PM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>> wrote: I can emulate the behaviour in SQL*Plus, all it takes is a mix of varchar2() and nvarchar2(), and when the inputs are literals there's no error and when the inputs are binds I can get ORA-12704.

It's behaving as if either the dataset_collection.collection column is nvarchar2() and the binds are (the equivalent) of varchar2() or vice versa. The more probably seems to be that the column is varchar2() and something is making the client send in binds of nvarchar2()

Regards
Jonathan Lewis



From: Chris Stephens <cstephens16_at_gmail.com<mailto:cstephens16_at_gmail.com>> Sent: 03 October 2019 19:05
To: Jonathan Lewis
Cc: oracle-l
Subject: Re: Troubleshooting ORA-12704 errors

sorry, i was in a rush to get that out before heading to a meeting. yes, that change replaces the binds w/ literals before submitting to database. SQL is submitted "properly" w/ binds, an exception occurs and SQL is resubmitted from exception block w/:

mysql = self.sql.compile(dialect=oracle.dialect(), compile_kwargs={"literal_binds": True})

                result = self._connection.execute(mysql)

Here is the relevant section from resulting trace:



PARSING IN CURSOR #140266817941640 len=676 dep=0 uid=214 oct=3 lid=214 tim=473998048829 hv=2595044970 ad='a4b895e8' sqlid='fq6g9vqdaugma' SELECT "deepCoadd_skyMap".skymap, "deepCoadd_skyMap".dataset_id, "deepCoadd_skyMap".rank FROM (SELECT dataset.skymap AS skymap, dataset.dataset_id AS dataset_id, CASE dataset_collection.collection WHEN :param_1 THEN :param_2 WHEN :param_3 THEN :param_4 WHEN :param_5 THEN :param_6 WHEN :param_7 THEN  :param_8 WHEN :param_9 THEN :param_10 END AS rank FROM dataset JOIN dataset_collection ON dataset.dataset_id = dataset_collection.dataset_id WHERE dataset.dataset_type_name = :dataset_type_name_1 AND dataset_collection.collection IN (:collection_1, :collection_2, :collection_3, :collection_4, :collection_5)) "deepCoadd_skyMap" WHERE "deepCoadd_skyMap".skymap = :skymap_1 END OF STMT
PARSE #140266817941640:c=343,e=1038,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=473998048828
ORA-12704(1):  dty=1 typ=0 flg=00000000 xfl=000A0000 bfl=512 bfc=128 csfm=1 csid=873 csflg=0 collid=16382 cclvl=2
ORA-12704(2):  dty=1 typ=3 flg=00030081 xfl=000C0000 bfl=32766 bfc=16383 csfm=2 csid=2000 csflg=0 collid=16382 cclvl=3 styp=1
WAIT #140266817941640: nam='SQL*Net break/reset to client' ela= 8 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=473998052641
WAIT #140266817941640: nam='SQL*Net break/reset to client' ela= 396 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=473998053074
WAIT #140266817941640: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=473998053118 WAIT #140266817941640: nam='SQL*Net message from client' ela= 828 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=473998053990 CLOSE #140266817941640:c=7,e=6,dep=0,type=0,tim=473998054063 XCTEND rlbk=1, rd_only=1, tim=473998054112
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=473998054173
WAIT #0: nam='SQL*Net message from client' ela= 2703 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=473998056899
=====================

PARSING IN CURSOR #140266817941640 len=641 dep=0 uid=214 oct=3 lid=214 tim=473998057079 hv=3770172134 ad='ddb14d68' sqlid='9yh5xdmhbhhr6' SELECT "deepCoadd_skyMap".skymap, "deepCoadd_skyMap".dataset_id, "deepCoadd_skyMap".rank FROM (SELECT dataset.skymap AS skymap, dataset.dataset_id AS dataset_id, CASE dataset_collection.collection WHEN 'calib/hsc' THEN 0 WHEN 'raw/hsc' THEN 1 WHEN 'masks/hsc' THEN 2 WHEN 'ref_cats' THEN 3 WHEN 'skymaps' THEN 4 END AS rank FROM dataset JOIN dataset_collection ON dataset.dataset_id = dataset_collection.dataset_id WHERE dataset.dataset_type_name = 'deepCoadd_skyMap' AND dataset_collection.collection IN ('calib/hsc', 'raw/hsc', 'masks/hsc', 'ref_cats', 'skymaps')) "deepCoadd_skyMap" WHERE "deepCoadd_skyMap".skymap = 'discrete/ci_hsc' END OF STMT On Thu, Oct 3, 2019 at 11:07 AM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk><mailto:jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>>> wrote:

Does that change the appearance of the SQL that gets to the database ? Does it use literals or does it still use bind variables ? If bind variables does it now show the bind variable dump in the trace file ?

Regards
Jonathan Lewis



From: Chris Stephens <cstephens16_at_gmail.com<mailto:cstephens16_at_gmail.com><mailto:cstephens16_at_gmail.com<mailto:cstephens16_at_gmail.com>>> Sent: 03 October 2019 16:57
To: Jonathan Lewis
Cc: oracle-l
Subject: Re: Troubleshooting ORA-12704 errors

still struggling with this but discovered some additional info. when we use "literal_binds" in inline values, the error goes away.

mysql = self.sql.compile(dialect=oracle.dialect(), compile_kwargs={"literal_binds": True})

                result = self._connection.execute(mysql)


On Wed, Oct 2, 2019 at 6:45 PM Chris Stephens <cstephens16_at_gmail.com<mailto:cstephens16_at_gmail.com><mailto:cstephens16_at_gmail.com<mailto:cstephens16_at_gmail.com>><mailto:cstephens16_at_gmail.com<mailto:cstephens16_at_gmail.com><mailto:cstephens16_at_gmail.com<mailto:cstephens16_at_gmail.com>>>> wrote: That's another oddity.

i enabled by connecting to service trace_me after making a call to DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name => 'trace_me',binds => true); and there are bind values shown for other SQL in trace file but not for offending SQL.

On Wed, Oct 2, 2019 at 4:24 PM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk><mailto:jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>><mailto:jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk><mailto:jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>>>> wrote: Following on from Norman Dunbar's mail.
What level trace were you using ? If you enable bind variable tracing along with the basic trace then you might find that Oracle dumps the bind variables in the standard form - some of the information will match the content of the ORA-12704 fields so that may enable you to identify exactly where the problem is.

e.g. tracing with binds (level 4 - or 12 if you have wait states enabled too):

SELECT /*+ FIND THIS */ COUNT(*) FROM T1 WHERE ID = :B1 AND C1 = :B1 AND C2 = :B1 Part of the 10046 trace will show things like: BINDS #139780336612928:
 Bind#0
  oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00   oacflg=13 fl2=206001 frm=00 csi=00 siz=88 off=0   kxsbbbfp=7f2125505fc0 bln=22 avl=02 flg=05   value=1
 Bind#1
  oacdty=96 mxl=32(02) mxlc=00 mal=00 scl=00 pre=00   oacflg=13 fl2=206001 frm=01 csi=178 siz=0 off=24   kxsbbbfp=7f2125505fd8 bln=32 avl=02 flg=01   value="BV"
 Bind#2
  oacdty=96 mxl=32(02) mxlc=00 mal=00 scl=00 pre=00   oacflg=13 fl2=206001 frm=01 csi=178 siz=0 off=56   kxsbbbfp=7f2125505ff8 bln=32 avl=02 flg=01   value="GF"

Regards
Jonathan Lewis



From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org><mailto:oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>><mailto:oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org><mailto:oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>>> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org><mailto:oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>><mailto:oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org><mailto:oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>>>> on behalf of Chris Stephens <cstephens16_at_gmail.com<mailto:cstephens16_at_gmail.com><mailto:cstephens16_at_gmail.com<mailto:cstephens16_at_gmail.com>><mailto:cstephens16_at_gmail.com<mailto:cstephens16_at_gmail.com><mailto:cstephens16_at_gmail.com<mailto:cstephens16_at_gmail.com>>>> Sent: 02 October 2019 20:11
To: oracle-l
Subject: Troubleshooting ORA-12704 errors

Oracle RAC 19.4 on Centos 7

Below snippet is from 10046 trace of session issuing included SQL from SQLAlchemy/cx_Oracle application. Does anyone know how to interpret the arguments following ORA-12704(1) and (2) or have suggestions on figuring out what the actual problem is?

Here are the server NLS settings:

SQL> _at_nls

PARAMETER                      VALUE

------------------------------ --------------------------------------------------
NLS_CALENDAR GREGORIAN NLS_CHARACTERSET AL32UTF8 NLS_COMP BINARY NLS_CURRENCY $ NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS NLS_DATE_LANGUAGE AMERICAN NLS_DUAL_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_LANGUAGE AMERICAN NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_NCHAR_CONV_EXCP FALSE NLS_NUMERIC_CHARACTERS ., NLS_SORT BINARY NLS_TERRITORY AMERICA NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR

19 rows selected.

Not sure how to capture client NLS settings at the moment. I guess a logon trigger?



PARSING IN CURSOR #140265837326472 len=676 dep=0 uid=214 oct=3 lid=214 tim=398930374576 hv=2595044970 ad='a4b895e8' sqlid='fq6g9vqdaugma' SELECT "deepCoadd_skyMap".skymap, "deepCoadd_skyMap".dataset_id, "deepCoadd_skyMap".rank FROM (SELECT dataset.skymap AS skymap, dataset.dataset_id AS dataset_id, CASE dataset_collection.collection WHEN :param_1 THEN :param_2 WHEN :param_3 THEN :param_4 WHEN :param_5 THEN :param_6 WHEN :param_7 THEN  :param_8 WHEN :param_9 THEN :param_10 END AS rank FROM dataset JOIN dataset_collection ON dataset.dataset_id = dataset_collection.dataset_id WHERE dataset.dataset_type_name = :dataset_type_name_1 AND dataset_collection.collection IN (:collection_1, :collection_2, :collection_3, :collection_4, :collection_5)) "deepCoadd_skyMap" WHERE "deepCoadd_skyMap".skymap = :skymap_1 END OF STMT
PARSE #140265837326472:c=674,e=675,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=398930374575
ORA-12704(1):  dty=1 typ=0 flg=00000000 xfl=000A0000 bfl=512 bfc=128 csfm=1 csid=873 csflg=0 collid=16382 cclvl=2
ORA-12704(2):  dty=1 typ=3 flg=00030081 xfl=000C0000 bfl=8192 bfc=4096 csfm=2 csid=2000 csflg=0 collid=16382 cclvl=3 styp=1

Any help is *greatly* appreciated!

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Oct 03 2019 - 23:35:40 CEST

Original text of this message