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.

4 comments:

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 ..

ex:
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.

N

I LOVE YOU said...

走光,色遊戲,情色自拍,kk俱樂部,好玩遊戲,免費遊戲,貼圖區,好玩遊戲區,中部人聊天室,情色視訊聊天室,聊天室ut,成人遊戲,免費成人影片,成人光碟,情色遊戲,情色a片,情色網,性愛自拍,美女寫真,亂倫,戀愛ING,免費視訊聊天,視訊聊天,成人短片,美女交友,美女遊戲,18禁,三級片,自拍,後宮電影院,85cc,免費影片,線上遊戲,色情遊戲,日本a片,美女,成人圖片區,avdvd,色情遊戲,情色貼圖,女優,偷拍,情色視訊,愛情小說,85cc成人片,成人貼圖站,成人論壇,080聊天室,080苗栗人聊天室,免費a片,視訊美女,視訊做愛,免費視訊,伊莉討論區,sogo論壇,台灣論壇,plus論壇,維克斯論壇,聊天室