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>
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>
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>> 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>>
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>>> 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>>> 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>> <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>>>
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
