Oracle

6 posts

Invalid Parameter Binding error when using Oracle ODP.NET TableAdapter

If you use Oracle ODP.NET in Visual Studio 2010 (or earlier version) to create a DataTable by using TableAdapter wizard, you may get an Invalid Parameter Binding error if there is a parameter in your query. For example, this simple select query below can cause the error: select * from Person where personId=:person_uid The cause is strange because ODP.NET cannot recognize the parameter’s DbType and thus sets the DbType of the parameter to Object, so […]

How to use conditional CASE statement in Oracle WHERE clause to retrieve data

Here is the table structure that I need to query: UserID Type Name Department Status Classification Enrollment_Status Where “Type” and “Department” are foreign keys of the other two tables. The query criteria are: If the user type is “Student” (type ID = 1), then only retrieve enrolled students (Enrollment_Status = ‘EN’); If the user type is not “Student” (type ID <> 1), then check Classification field and only retrieve data with Classification <> ‘R’ It […]

ORA-01745: invalid host/bind variable name, caused by reserved word in query

I wrote a simple parameterized query the other day against an Oracle database as follows: SELECT DISTINCT E.ID, E.Name, E.Department FROM Employee E WHERE E.ID = :uid Then in my application, I assign the value to the parameter as follows: cmd.Parameters.Add(new OracleParameter(":uid", OracleDbType.Varchar2)); However, when I was trying to run my application, I got an error saying: ORA-01745: invalid host/bind variable name Can you see what is causing the error? It took me hours of […]

How to fix System.Data.OracleClient BadImageFormatException error

[UPDATE]: The fix in this post may not work on Windows Vista/7  64-bit operating system, if you are using Vista/7 and have the problem specified in this post, then check out the updated post here: http://35.227.136.90/index.php/2010/08/13/update-how-to-fix-system-data-oracleclient-badimageformatexception-error/ If you use Microsoft’s OracleClient libraries to connect Oracle database in .NET application, you may encounter BadImageFormatException error if the application is running on a x64 machine. The actual error message shows as follows: [InvalidOperationException: Attempt to load Oracle […]

MySQL conference video

Very interested conference keynote video on MySQL: State of the Dolphin, especially if you are interested in the future of MySQL database after Oracle acquired Sun recently. I am very excited about the future version of MySQL: MySQL 5.4, which has a lot of new features.

Oracel Date function

When it comes to handling date and time, Oracle PL/SQL is not as easy or convenient as Microsoft SQL. For instance, with MS SQL, you can write a query like: SELECT * FROM SomeTable WHERE DateAdded = ’09-29-2006 08:00:00 AM’ But with PL/SQL, the above query will not run and will generate an error message like ‘not a valid month’. To make it work, you have to use “To_Date” function to convert the DateTime string […]