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