Oracle Provider or Microsoft Provider

[Alok Jain]

Hi,

I am using .Net Framework 1.0 with Oracle. I am using the Oracle provided
data provider 'Oracle.DataAccess.Client' and I am using the parameterized
queries. Now I want to use the Microsoft provided data provider
'System.Data.OracleClient' for Oracle, but when I change the reference in my
project I start getting following error:
ORA-01036: illegal variable name/number SQL String= SELECT
u.opid,user_id,password,first_name,start_date,middle_name,last_name,INITIALS
,email,menu_color,predefined_bg_color,tab_color,body_color,security_type,fon
t,max_search_results,max_search_results_per_page,startup_page,url,dept from
UADUSR_USER u, UADUSP_USER_SCREEN_PREF p, UADAPP_APPLICATIONS a WHERE u.opid
= p.opid AND u.startup_app = a.app_name_pk AND u.enabled_flag = 'E' AND
LOWER(user_id) = :USERNAME AND password = ASSWORD

I guess this is a issue related to caramelized query related difference with
both the data provider. Can you suggest some thing here, I basically want to
make minimum changes in my code. I want my code to work on both Framework
1.0 and 1.1.

Thanks

Alok

 

[Cowboy \(Gregory A. Beamer\) [MVP]]

ODP.NET, the Oracle provider, should work in both 1.0 and 1.1, so I am not
sure why you want to switch, unless you are simply trying to make the
software compatible to all people's machines.

My suggestion is to make sure your SQL is ANSI compliant rather than PL/SQL.
That should solve your problem and work with both ODP.NET and the Microsoft
OracleClient classes. The problem most likely lies in the := arena.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************
Think Outside the Box!
************************************************

 

[Alok Jain]

Hi All,

This is solved, the new provider from microsoft follows a strict parameter
check while the old oracle provider was flexible on parameter names, for it
only the order of parameter was significant (blind matching).

You have to use ':' prefix for your parameter names.

Regards,
Alok