Link DB in Oracle parameter problem

I've got this Oracle query that links to another db but it doesn't seem to accept my parameters that i pass to the query

Here's my code

cmd.CommandText = "SELECT * FROM table1@dev tb1 join table2 tb2 on tb1.id = tb2.id WHERE tb1.id = :id"

cmd.CommandType = CommandType.Text

cmd.BindByName = True

cmd.Parameters.Add(New OracleParameter("id", id))

I get this error when do this

ORA-03113: end-of-file on communication channel

But if i simply change the query to not be using parameters then it works

Any ideas on how I could get this working with parameters?

EDIT

On furthe开发者_高级运维r investigation if I try the following

cmd.CommandText = "SELECT * FROM table1@dev tb1 WHERE tb1.id = :id"

cmd.CommandType = CommandType.Text

cmd.BindByName = True

cmd.Parameters.Add(New OracleParameter("id", id))

I get a different error

ORA-28511: lost RPC connection to heterogeneous remote agent

I would really like a solution which allows parameters to be passed to the linked db but I can't seem to find anything about using parameters with linked db's

Thanks

ORA-03113 is a generic exception, hurled when something catastrophic causes the connection to drop. There may be some additional information in the alert log or a trace file. Check the background dump or diagnostics directory.

ORA-28511 is rather more specific. The error message points to a particular area for investigation; the advice is "Check for network problems and remote host crashes. The problem is probably in the agent software. "

So, it's not really a syntax problem. It's an environment (configuration/hardware) issue. I'm afraid you're going to have to do a some more digging.

Good luck.

have you tried specifying the parameter type ?:

OracleParameter p1 = new OracleParameter("id",OracleDbType.Decimal); 
p1.Value=id; 
cmd.Parameters.Add(p1); 

Personally, this whole Oracle driver/query stuff really made me go nuts; e.g. I experienced that certain SELECT * generated a DivideByZeroException while replacing the * by the really required column names worked like a charm.

It could be that you have a similar issue here.

What helped me resolving the issues was to choose the ODP.NET drivers really wisely; we are running a production system here with beta drivers since they were the only ones working in all circumstances in our project.