Обсуждение: Trying to connect to an Oracle instance...

Hi,
I've been trying to perform a connection to an Oracle DB for a while,
to no avail. Here is what I get at my psql prompt:
postdb=# Select dblink_connect('login','hostaddr=<host IP> port=1521 \
dbname=orasid user=mylogin password=mypwd');
ERROR:  could not establish connection
DETAIL:  server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

I installed postgresql 8.1 on an Win XP PC a few days ago without
any problem, and I get no error at all when I run any other SQL
instructions or PostgreSQL functions. I ran the
%POSTGRES_HOME%\share\contrib\dblink.sql
file as the same user (which is the 'postgres' Windows user I created
-as I have admin privs on my PC- and with which I execute the following

every time I want to use postgresql:
DOS>runas /env /user:<pc_name>\postgres
d:\prgs\postgresql\81\bin\postmaster
DOS>runas /env /user:<pc_name>\postgres "d:\prgs\postgresql\81\bin\psql
postdb"
in a DOS box)

<host IP>, orasid, mylogin and mypwd are valid parameters necessary
to connect myself to an Oracle 'orasid' DB that resides on a
Sun/Solaris
server, they work in a SQL*Plus session for example (I use the IP
address
rather than the name of this server because if I don't I get the error
"could
not translate host name "host name" to address: Unknown host", yet I
can
ping it of course). Obviously I need to configure something for the
network
inside PostgreSQL directories, don't I ?

Hopefully I gave all details required to give me some hints... Thanks
for
any suggestion...
Spendius

Spendius wrote:
> Hi,
> I've been trying to perform a connection to an Oracle DB for a while,
> to no avail. Here is what I get at my psql prompt:
> postdb=# Select dblink_connect('login','hostaddr=<host IP> port=1521 \
>
If you are trying to connect to a Oracle DB, don't you need to be using
DBI Link instead of DBlink?
I thought DBLink was just for Postgresql databases?

Later,

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration

On Thu, Jul 13, 2006 at 05:46:55AM -0700, Spendius wrote:
> I've been trying to perform a connection to an Oracle DB for a while,
> to no avail. Here is what I get at my psql prompt:
> postdb=# Select dblink_connect('login','hostaddr=<host IP> port=1521 \
> dbname=orasid user=mylogin password=mypwd');
> ERROR:  could not establish connection
> DETAIL:  server closed the connection unexpectedly
>         This probably means the server terminated abnormally
>         before or while processing the request.

The contrib/dblink module is for connecting to other PostgreSQL
databases, not to Oracle or any other DBMS.  To connect to a non-
PostgreSQL database you could write a function in a server-side
language like PL/Perl and make the connection with DBI.  See also
DBI-Link:

http://pgfoundry.org/projects/dbi-link/

If the backend crashed then dblink might have a bug.  Can anybody
repeat this crash and get a stack trace?  I tried connecting to
MySQL and got a clean failure with the message "ERROR:  could not
establish connection".

--
Michael Fuhr

On Thu, July 13, 2006 11:03 am, Tony Caduto wrote:
> Spendius wrote:
>
>> Hi,
>> I've been trying to perform a connection to an Oracle DB for a while,
>> to no avail. Here is what I get at my psql prompt: postdb=# Select
>> dblink_connect('login','hostaddr=<host IP> port=1521 \
>>
> If you are trying to connect to a Oracle DB, don't you need to be using
> DBI Link instead of DBlink?
> I thought DBLink was just for Postgresql databases?

That's true. But why should dblink crash? That's a bug.

A.M. wrote:
> On Thu, July 13, 2006 11:03 am, Tony Caduto wrote:
>
>>Spendius wrote:
>>>I've been trying to perform a connection to an Oracle DB for a while,
>>>to no avail. Here is what I get at my psql prompt: postdb=# Select
>>>dblink_connect('login','hostaddr=<host IP> port=1521 \
>>
>>If you are trying to connect to a Oracle DB, don't you need to be using
>>DBI Link instead of DBlink?
>>I thought DBLink was just for Postgresql databases?
>
> That's true. But why should dblink crash? That's a bug.
>

Umm, where does he say anything about a crash? I see this from the
original email:

 > Here is what I get at my psql prompt:
 > postdb=# Select dblink_connect('login','hostaddr=<host IP> port=1521 \
 > dbname=orasid user=mylogin password=mypwd');
 > ERROR:  could not establish connection
 > DETAIL:  server closed the connection unexpectedly
 >        This probably means the server terminated abnormally
 >        before or while processing the request.

That's a properly handled error. dblink is merely echoing the error
message propagated up by libpq. This doesn't look to me as though the
session *running* dblink crashed. Apparently libpq believes that the
*other end* of the connection was terminated unexpectedly, but
considering it was an Oracle instance that it was communicating with,
I'm not sure what it could/should do better. In any case, the Oracle
side of this connection is what "closed unexpectedly".

Joe