ORA-1458 :: OracleType.VarChar requires .Size spec?

[Terry Blankers]

Hi all, I'm getting strange errors when trying to execute stored procedures
with the MS .NET provider for Oracle. My SP parms are defined as VARCHAR. If
I don't specify a size for my OracleType.VarChar's in my
OracleCommand.Parameters.Add statements, I get ORA-1458 errors:

ORA-01458 invalid length inside variable character string

Cause: An attempt was made to bind or define a variable character string
with a buffer length less than the minimum requirement.

Action: Increase the buffer size or use a different type.

All documentation I've been able to get my hands on shows no specification
for size this variable-length datatype. If I use OracleCommandBuilder to
derive parameters, size is specified as 2000. I've tried specifying this in
my statements and sometimes it work and sometimes not. Also if the value of
the input parameter is "" that can cause the error as well.

Anyone have any ideas?

Thanks,

Terry

[Bob Beauchemin]

You are correct about not specifying the size of a VARCHAR2 or NUMERIC type
on the stored procedure declaration itself in PL/SQL. But the provider needs
to know the parameter size so if can send the correct length to the
database, make the buffer long enough, etc. Should be the same in any data
access API. In some cases, the provider may be able to infer the size of
your parameter by the size of the variable, in this case you will have the
code it.

Bob Beauchemin
http://staff.develop.com/bobb

"Terry Blankers" <test> wrote in message
news:##PAvs5VCHA.2328@tkmsftngp09...

[Terry Blankers]

I understand this, I've normally used a size spec with OLEDB & ADODB. And I
usually specify the size of the column in the provider.add command. However,
when using the new MS OracleClient provider and setting size to match the
destination column size, I get ORA-01458 errors if the string that I'm
trying to assign to the parameter is empty, ie = "".

"Bob Beauchemin" <no_bob...@develop.com> wrote in message
news:uAc2Z55VCHA.2416@tkmsftngp09...

[Bob Beauchemin]

I believe that the null string problem has been brought up a few times (at
least on this list) and is known.

Bob Beauchemin
http://staff.develop.com/bobb

"Terry Blankers" <test> wrote in message

news:OakwXW6VCHA.2644@tkmsftngp10...