Re: Any way to capture spatial bind data?

  You are correct..      

My point was to evaluate if the ora error could be   

bypassed with a different client version software..            

>
> On Dec 9, 2023 at 12:35 PM, <Jonathan Lewis (mailto:jlewisoracle_at_gmail.com)> wrote:
>
>
>
>
>
>
>
> 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
>
>
>
> 1 row selected.
>
>
>
>
> It's a very old problem - there are datatypes that Oracle can't interpret (for traces) and doesn't capture.
>
> Regards
>
> Jonathan Lewis
>
>
>
>
>
>
>
> On Sat, 9 Dec 2023 at 17:01, Rajeev Prabhakar <rprabha01_at_gmail.com (mailto:rprabha01_at_gmail.com)> wrote:
>
> >
> >
> >
> >
> > 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 (mailto: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 (mailto: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 (mailto: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 (mailto: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