Re: Troubleshooting ORA-12704 errors

From: Chris Stephens <cstephens16_at_gmail.com>
Date: Thu, 3 Oct 2019 10:57:11 -0500
Message-ID: <CAEFL0sz_27YLsEpcMCrO5JMC78s+Wu7YbYVd6Ry+bzvMSNybOQ_at_mail.gmail.com>



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> 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>
> 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 <oracle-l-bounce_at_freelists.org> on
>> behalf of Chris Stephens <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
Received on Thu Oct 03 2019 - 17:57:11 CEST

Original text of this message