Oracle String (Varchar) Parameters

[Curtis]

Hello,

We are using Telerik Reporting Q2 2010 (winForms).  Usually, our report datasources are pointing to SQL Server stored procedures (i.e., property window shows

datasource SelectCommand property set to a stored procedure, SelectCommandType StoredProcedure) that take parameters, and that approach has been

straightforward.  However, one report we are working on requires a datasource that points to a SQL text (i.e., property window shows datasource SelectCommand

property set to the SQL text, SelectCommandType Text).  The challenge we are running into is to get this text to accept a parameter; in particular, a string

parameter.  Everything works fine when the datasource references a SQL Server database for both String and Int32 parameters, as well as Oracle when using an

Int32 parameter.  We discovered that using a colon ":" prefix on the parameter for Oracle works, whereas the "@" prefix works for SQL Server.  However, the

"fun" begins when using a String parameter with Oracle.  Each time with do so (prefixing the parameter with a ":" as with an Int32 parameter), we get the

following error message:  An error has occurred while processing Report "":  ORA-12704:character set mismatch.  The SelectCommand is something like this

where the SelectCommand references an Oracle varchar2(60) field as a String parameter: "SELECT <field1>, <field2>, <field3>, <field4> FROM Customers WHERE

CustomerName = :custname>."  The same SelectCommand slightly adjusted to reference an Oracle Number field as an Int32 parameter works just fine, as with the

following:  "SELECT <field1>, <field2>, <field3>, <field4> FROM Customers WHERE CustomerNumber = :customernumber."  Only the String parameter version throws

the error mentioned above.  How do handle passing a String parameter to Oracle.  By the way, the Oracle version we are on is Oracle 8i (8.1.7.4).

Regards,

Curtis Butler

[Chuck Harrington]

Hi Curtis
Unless I misunderstood your question, the problem is the way you are defining you parameter values for Oracle using .NET.  You are correct in using the bind variable for the parameter (i.e. - :custname), however you need to also indicate to oracle the dbtype of the parameter and in some cases, whether it is an input only or and inout parameter.  If you could post the actual code you are using, I can probably give you and example how to accomplish this.

[Steve]

Hello Curtis,

We've tried our scenario on our end and it worked without exceptions. However we have suspicions that this might be related to the language settings of your Oracle instance. To verify this, please try executing the same query programmatically using ADO.NET and see what is the result. If the above suggestions works properly (i.e. the issue surfaces only when using our SqlDataSource component), please prepare a sample project (with sample database) that exhibits the issue and we would investigate what is going on.

Sincerely yours,
Steve
the Telerik team

[Curtis]

Through experimenting with the Type of Parameter, we discovered that AnsiString is the choice instead of String.

Thanks for the prompt reply, however.

Curtis