Monday, November 30, 2009

Interesting APEX with dblink issue

We recently ran in to a problem when connecting across a database link to a Postgres database.  The query looked like this
select "column1", "column2"
  from "my_schema"."my_long_named_view"@my_db_link
It works fine from sql*plus and sql Developer, but when running the same query in Application Express (in an app or in the SQL Workshop) we got this error:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Generic Connectivity Using ODBC]DRV_QspecDescribe: DB_ODBC_RECORD (189): ; [OpenLink][ODBC][PostgreSQL Server] current transaction is aborted, commands ignored until end of transaction block (SQL State: S1000; SQL Code: 1) ORA-02063: preceding 2 lines from MY_DB_LINK
Dave Rydzewski came up with the solution.  Shorten the name of the view in Postgres and change the query to look like this (note two fewer double quotes "):
select "column1", "column2"
  from "my_schema.short_v"@my_db_link
I'm still not sure what APEX does to make it blow up.  I wonder how many people use APEX with a db link to Postgres.


Doug said...

Funny you should ask. That was the very first projects I did with HTML-DB 1.5 back in the day.

I dont' think we ran into that problem, but then again I created local synonyms for all the remote Postgres tables and used those in HTML-DB.

I was amazed at the ease at which connecting to an external non-Oracle database could be achieved.

Gary Myers said...

"I'm still not sure what APEX does to make it blow up"

I'd suspect something with DBMS_SQL.DESCRIBE_COLUMNS.

Neelesh Shah said...

Anton, When I set up the handshake to pg from Oracle, I used Open Link's Driver and never ran a query directly from Apex to a pg object, instead I created a view in the Oracle database ..

create view a_view as
select "column1" column1
,"column2" column2
from "pg_table"@db_link_to_pg

and used the view in APEX.

This way, I didnt have to double quote anything. And did not run into the problem with length of view names.

And all objects accessed from pg where created in a separate Oracle schema as views, to have them in one place and have better control.


I LOVE YOU said...