Re: Any way to capture spatial bind data?

Rajeev,

If you run the trace and dumping bind values for a simple SQL statement like the one Osman showed, you get the following pattern for the SDO_GEOMETRY type:

 Bind#0
  oacdty=121 mxl=656(656) mxlc=00 mal=00 scl=00 pre=00   oacflg=00 fl2=206001 frm=00 csi=00 siz=656 off=0 toid ptr value=78D19078 length=16
3796C48378FE407AE03400400B407D
  kxsbbbfp=7f418ba51650 bln=656 avl=16 flg=15   value=Unhandled datatype (121) found in kxsbndinf Dumping '' addr=0x7f418ba51650 size=16 bytes Dump of memory from 0x7f418ba51650 to 0x7f418ba51660 7F418BA51650 17854A40 00000000 8BA51660 00007F41 [_at_J......`...A...]

Notice the mxl (=max length). If you query v$sql_bind_capture for the SQL_ID you get

SQL> l
  1 select name, position, was_captured, datatype, datatype_string, value_string, value_anydata
  2 from V$sql_bind_capture
  3* where sql_id = 'gb33ctuxzb7ux'
SQL> /

NAME           POSITION WAS   DATATYPE DATATYPE_STRING VALUE_STRING
             VALUE_ANYDATA()

-------------------------------- --------------------------------
:B1                   1 NO         121 ADT

> Is there a possibility of trying this select stmt
> with a different version of the client driver ?
>
> On Dec 9, 2023 at 10:59 AM, <Osman DINC <dinch.osman_at_gmail.com>> wrote:
>
> Thanks Jonathan for your time.
>
> I will check with disabling ASO (Advanced Security Option) parameters and
> try a client level tracing using TRACE_LEVEL_CLIENT parameter in sqlnet.ora
> if it helps. Also I will coordinate with the application team if they can
> catch it in the app layer.
>
> Regards,
> Osman DİNÇ
>
>
>
> Jonathan Lewis <jlewisoracle_at_gmail.com>, 9 Ara 2023 Cmt, 14:44 tarihinde
> şunu yazdı:
>
>> I had a little play around with this, but I couldn't find a way to get
>> at the bind value.
>>
>> The 10046 trace shows only the first 16 bytes of the raw data.
>> (Interesting detail - I was using a simple rectangle as the bind
>> sdo_geometry), on disc it took about 90 bytes, but the bind variable was
>> declared as 656 bytes.
>>
>> I tried dumping the error stack at level 3, and still couldn't find the
>> bind, all I got was the same description, pointer and 16 bytes.
>>
>> It did occur to me that even if I found a way to dump the (whole) bind we
>> still might not see it for this error if the error is occurring in the
>> SQL*Net transport layer before it gets to the session layer that we
>> normally trace. I've probably missed or forgotten some dump/trace we could
>> invoke, but I think it would be a case of dumping the entire session state
>> when the error occured then finding the short description of the bind and
>> following the pointer into the full memory dump of the session. Possibly
>> someone else will be able to suggest a way of doing that.
>>
>> One simple guess - an SDO_GEOMETRY includes two declarations of
>> varray(1048576) of number (stored as LOBs). Maybe the particular value that
>> causes the problem is just too large for some part of the code that is
>> trying to handle it. It might be worth checking where the bind values come
>> from, and checking to see if there are any very large LOB values stored in
>> the table.
>>
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>
>> On Fri, 8 Dec 2023 at 07:26, Osman DINC <dinch.osman_at_gmail.com> wrote:
>>
>>> Hi Jonathan;
>>>
>>> Error is like below:
>>>
>>> Database version is 19.20.
>>>
>>> ORA-03137: malformed TTC packet from client rejected[3146] [94] [] [] []
>>> [] [] []
>>> ORA-03146: invalid buffer length for TTC field.
>>>
>>> sql statement is like below: (fully replaced)
>>>
>>> SELECT V_EW_ORG_DEVICE.DEVICE_ID,
>>> V_EW_ORG_DEVICE.PAR_ORG,
>>> V_EW_ORG_DEVICE.ORG_ID,
>>> V_EW_ORG_DEVICE.ORG_NAME,
>>> V_EW_ORG_DEVICE.SYS_ID,
>>> V_EW_ORG_DEVICE.SYS_NAME,
>>> V_EW_ORG_DEVICE.SYS_TASK_TYPE,
>>> V_EW_ORG_DEVICE.SYS_TASK,
>>> V_EW_ORG_DEVICE.SYS_DESC,
>>> V_EW_ORG_DEVICE.SYS_SER_NU,
>>> V_EW_ORG_DEVICE.OP1,
>>> V_EW_ORG_DEVICE.OP1_NAME,
>>> V_EW_ORG_DEVICE.OP1_PHONE,
>>> V_EW_ORG_DEVICE.OP1_ORG,
>>> V_EW_ORG_DEVICE.OP2,
>>> V_EW_ORG_DEVICE.OP2_NAME,
>>> V_EW_ORG_DEVICE.OP2_PHONE,
>>> V_EW_ORG_DEVICE.OP2_ORG,
>>> V_EW_ORG_DEVICE.TRANS,
>>> V_EW_ORG_DEVICE.GEOG_LATI,
>>> V_EW_ORG_DEVICE.GEOG_LONG,
>>> V_EW_ORG_DEVICE.DEC_LATI,
>>> V_EW_ORG_DEVICE.DEC_LONG,
>>> V_EW_ORG_DEVICE.GEO
>>> FROM V_EW_ORG_DEVICE
>>> WHERE ( V_EW_ORG_DEVICE.SYS_NAME = 'ORANGE A'
>>> OR V_EW_ORG_DEVICE.SYS_NAME = 'ORANGE B')
>>> AND (sdo_filter (V_EW_ORG_DEVICE.GEO, *:1*) = 'TRUE');
>>>
>>> V_EW_ORG_DEVICE.GEO column type is SDO_GEOMETRY
>>>
>>> Error is thrown according to the* :1* parameter bind value. But this
>>> value is not logged in the application layer. And i am curious about is
>>> there a way to capture SPATIAL bind data.
>>>
>>> Thanks.
>>>
>>> Osman DINC <dinch.osman_at_gmail.com>, 7 Ara 2023 Per, 20:19 tarihinde
>>> şunu yazdı:
>>>
>>>>
>>>>
>>>> Hi Jonathan,
>>>>
>>>> I will supply more information tomorrow morning.
>>>>
>>>> Regards
>>>> Osman Dinç
>>>>
>>>

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