determining Oracle bind types

In a SELECT with placeholders, what exactly in trace output
will tell me the Oracle type a bind argument to execute()
is being bound as? I have this output (snippet):

bind :p1 <== '002-4136482-2424839' (type 0)
rebinding :p1 (not-utf8, ftype 1, csid 0, csform 0, inout 0)
bind :p1 <== '002-4136482-2424839' (size 19/20/0, ptype 4, otype 1)
bind :p1 <== '002-4136482-2424839' (size 19/19, otype 1, indp 0,
at_exec 1)
bind :p1 <== '002-4136482-2424839' (in, not-utf8, csid 31->0->31,
ftype 1, csform 0->0, maxlen 19,
maxdata_size 0)
dbd_st_execute SELECT (out0, lob0)...
in ':p1' [0,0]: len 19, ind 0

I figured otype 1 meant ORA type with value 1 = VARCHAR2?

The symptom is that a series of digits and dashes (like above)
seems to be bound as something other than a VARCHAR2 according
to a DBA, and that's making the query run inefficiently. The
dash/digit string is the only execute argument; the SELECT has
a single question mark placeholder to accept it.

--
Steve Sapovits steves06@comcast.net

Re: determining Oracle bind types

If I am not mistaken DBD will defualt to varchar2 for all placeholder unless
you tell it otherwise.
Check out Padded Char Fields and Placeholer Binding Attributes in the
DBD::Oracle POD.
In this case DBD might see this bind as number not a varchar (depeneds on
the exact NLS settings of you Oracles) so you should tell DBD::Oracle that
the bind is a varchar2 like this

$sth->bind_param(1, '002-4136482-2424839' ,ORA_VARCHAR2 );

"Steve Sapovits" wrote in message
news:45AD4014.4080404@comcast.net...
>
> In a SELECT with placeholders, what exactly in trace output
> will tell me the Oracle type a bind argument to execute()
> is being bound as? I have this output (snippet):
>
> bind :p1 <== '002-4136482-2424839' (type 0)
> rebinding :p1 (not-utf8, ftype 1, csid 0, csform 0, inout 0)
> bind :p1 <== '002-4136482-2424839' (size 19/20/0, ptype 4, otype 1)
> bind :p1 <== '002-4136482-2424839' (size 19/19, otype 1, indp 0,
> at_exec 1)
> bind :p1 <== '002-4136482-2424839' (in, not-utf8, csid 31->0->31,
> ftype 1, csform 0->0, maxlen 19,
> maxdata_size 0)
> dbd_st_execute SELECT (out0, lob0)...
> in ':p1' [0,0]: len 19, ind 0
>
> I figured otype 1 meant ORA type with value 1 = VARCHAR2?
>
> The symptom is that a series of digits and dashes (like above)
> seems to be bound as something other than a VARCHAR2 according
> to a DBA, and that's making the query run inefficiently. The
> dash/digit string is the only execute argument; the SELECT has
> a single question mark placeholder to accept it.
>
> --
> Steve Sapovits steves06@comcast.net