ODBC Connection Problem

[Keene Scouser]

I am using an ODBC connection to connect to a AS400 tables, when I run a query in an SQL Window for the first time, everything works great. But....if I run the same query again in the same window I get the following error:

'ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Generic Connectivity Using ODBC]

if I open a new query window, and run the same query from there, it works fine again, or if I close and re-open PL/SQL Developer it will work again.

Any ideas what's happening, I use the same ODBC connection with other applications and have no problems with it.

Thanks
Brian

 

[Marco Kalter]

I'm afraid that ODBC connections are not officially supported. It's a pity that it "almost" works though. What happens if you change the original statement? Can you execute it whithout closing the SQL Window?

 

[Keene Scouser]

I'll give that try....but perhaps I should clarify my connection details:

I have an ODBC DSN on the Oracle DB server, I then have a 'Linked Database' in my Oracle instance.

I am connecting to the Oracle instance with PL/SQ Dev in the normal manner (as sysdba from a client pc) and I run the following query 'select col from table@AS400;'

So, If I understand you correct, PL/SQL Dev does not support this?

This could be huge issue for me if that's the case

Brian

 

[Marco Kalter]

So you're using a database link? That is indeed supported. I think that it may be a parse/re-execute issue though, so if you can test if you can execute different SQL statements subsequenttly from the SQL Window, we may be able to provide a patch to work around the problem.

 

[Keene Scouser]

Great, I've tried several ways to reproduce this error, unfortunately it seems to be random.
I executed a simple select * statement and all rows were returned, I then changed the statement to return just one col and I got the following eror: 'ORA-28500:connection from ORACLE to a non-Oracle system returned this message:[Generic Connectivity Using ODBC][S1000][9013]General error in nvITrans_BeginT -rc = -1. Pleasae refer to the log file for details. ORA-02063receding 2 lines from AS400' from that point on, I could not execute any statement from that SQL window.
However; I copied the same statement into a new SQL window and it worked fine. Since then, I have not been able to reproduce the error.
Thanks...

 

[Marco Kalter]

It's a pity that the problem is so unpredictable. Do you ever have similar problems with other tools than PL/SQL Developer?

 

[czn]

I had a similar problem with ODBC:
I exported from Access through an ODBC connection into an Oracle DB.
In PLSQLDev,Select * from myTable worked fine but Select col from MyTable didn't(column not found error).
In Access,I uppercased the columns and table names, re-export in Oracle and worked fine.
So, it seems like ODBC put some strange chars in tables/columns names