Re: BUG #5994: Can't excute DBI->connect to oracle by client site - Mailing list pgsql-bugs

On Tue, Apr 26, 2011 at 5:36 AM, Li Hongbing <lihongbing@intasect.com.cn> w=
rote:
> I am trying to create a 64-bit postgresql 8.4 database server which can
> retrieve data from Oracle(10gR2) at an other server by DBI-LINK.
>
> DBI-LINK works good on postgresql server site when I connect to linux by
> telnet.When I connect to postgresql server by postgresql client just like
> pgAdmin or EMS SQL Manager,I got a error like this:
> error from Perl function "set_up_connection": DBI
> connect('host=3D192.168.138.173;sid=3Dsmmacs;port=3D1521','rmtrsys',...) =
failed:
> ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
> OCISessionBegin) at line 43 at line 35.
>
> so I write test code to confirm DBI connect function
>
> CREATE OR REPLACE FUNCTION dbi_link.test2()
>  RETURNS char AS
> $BODY$
> use DBI;
> use DBD::Oracle;
>
>
> my $dbname =3D 'xxx';
> my $user =3D 'rmtrsys';
> my $passwd =3D 'rmtrsys';
>
> my $dbh =3D
> DBI->connect("dbi:Oracle:host=3D192.168.138.173;sid=3Dsmmacs;port=3D1521"=
, $user,
> $passwd,{ora_charset =3D> 'UTF8'}) or
> die "Database connection not made: $DBI::errstr";
>
> $dbh -> disconnect;
>
> return "OK";
>
> $BODY$
>  LANGUAGE 'plperlu' VOLATILE
>  COST 100;
>
>
> When I connect to linux by telnet and excute psql -d dbi_link_test to run=
,it
> works good
>
> dbi_link_test=3D# select dbi_link.test2();
>  test2
> -------
>  OK
> (1 row)
>
> But when I connect by  postgresql client (pgAdmin or EMS SQL Manager) to =
run
> same sql, it returned an error:
> NOTICE:  DBI
> connect('host=3D192.168.138.173;sid=3Dsmmacs;port=3D1521','rmtrsys',...) =
failed:
> ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
> OCISessionBegin) at line 10
>
>
> ERROR:  error from Perl function "test2": Database connection not made:
> ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
> OCISessionBegin) at line 10.
>
> ********** =A5=A8=A5=E9=A9` **********
>
> ERROR: error from Perl function "test2": Database connection not made:
> ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
> OCISessionBegin) at line 10.
> SQL=A5=B9=A5=C6=A9`=A5=C8:XX000
>
>
> when got this error,I can't shutdown the server normally,ths shutdown
> command will not terminate unless I cancel it.
> [postgres@localhost ~]$ pg_ctl stop -D /usr/local/pgsql/data
> waiting for server to shut down...LOG:  received smart shutdown request
> .LOG:  autovacuum launcher shutting down
> ..........................
>
> Above test let me know the DBI-LINK crushed by DBI->connect, I think the
> really reason is one of these:
> 1.postgresql create backend process has a bug,most likely with bad process
> environment var
> 2.Orace OCI functions has a bug
> 3.DBD:Oracle has a bug
>
> However,I should report this as a postgresql bug ,becourse of run same sql
> must get same result anywhere(server or client).

My guess is that, depending on how you connect, something in your
environment is different, and that is what is making the Oracle
connection succeed or fail.

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company