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

The following bug has been logged online:

Bug reference:      5994
Logged by:          Li Hongbing
Email address:      lihongbing@intasect.com.cn
PostgreSQL version: PostgreSQL 8.4.
Operating system:   x86_64_unknonwn_linux_gnu
Description:        Can't excute DBI->connect to oracle by client site
Details:

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=192.168.138.173;sid=smmacs;port=1521','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 = 'xxx';
my $user = 'rmtrsys';
my $passwd = 'rmtrsys';

my $dbh =
DBI->connect("dbi:Oracle:host=192.168.138.173;sid=smmacs;port=1521", $user,
$passwd,{ora_charset => '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=# 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=192.168.138.173;sid=smmacs;port=1521','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.

********** エラー **********

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ステート: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).

Versions:
Oracle-10.2.0.1.0
DBI-1.616
DBD-Oracle-1.28
YAML-0.72
perl, v5.8.8 built for x86_64-linux-thread-multi


Regards,
Li Hongbing,China