Not an area of Oracle where I have information at my fingertips, but it might
simply be that the NLS_LANG at the client side is configured incorrectly -
perhaps through an O/S setting.
There may also be some history involved - was the database created at 19c (or
12.2) where the default install gives you AL32UTF8, or was it an upgrade from
an earlier version that had a different default character set installed. Maybe
the client (or network layer) thinks you're using a character set that you
aren't.
Signing out for the evening - so no further comments until tomorrow.
Regards
Jonathan Lewis
________________________________________
From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
Sent: 03 October 2019 22:35
To: oracle-l
Subject: Re: Troubleshooting ORA-12704 errors
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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Chris Stephens <cstephens16@xxxxxxxxx>
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@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>> 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@xxxxxxxxx<mailto:cstephens16@xxxxxxxxx>>
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@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx><mailto:jonathan@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>>>
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@xxxxxxxxx<mailto:cstephens16@xxxxxxxxx><mailto:cstephens16@xxxxxxxxx<mailto:cstephens16@xxxxxxxxx>>>
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@xxxxxxxxx<mailto:cstephens16@xxxxxxxxx><mailto:cstephens16@xxxxxxxxx<mailto:cstephens16@xxxxxxxxx>><mailto:cstephens16@xxxxxxxxx<mailto:cstephens16@xxxxxxxxx><mailto:cstephens16@xxxxxxxxx<mailto:cstephens16@xxxxxxxxx>>>>
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@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx><mailto:jonathan@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>><mailto:jonathan@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx><mailto:jonathan@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>>>>
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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx><mailto:oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>><mailto:oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx><mailto:oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>>>
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx><mailto:oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>><mailto:oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx><mailto:oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>>>>
on behalf of Chris Stephens
<cstephens16@xxxxxxxxx<mailto:cstephens16@xxxxxxxxx><mailto:cstephens16@xxxxxxxxx<mailto:cstephens16@xxxxxxxxx>><mailto:cstephens16@xxxxxxxxx<mailto:cstephens16@xxxxxxxxx><mailto:cstephens16@xxxxxxxxx<mailto:cstephens16@xxxxxxxxx>>>>
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> @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!
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
