Hi,
I have a problem with the following code:
my $dbh = DBI->connect ("dbi:Oracle:$db", $user, $password) ; die "Cannot connect to $db ($DBI::errstr)"
unless defined $dbh;my @lst = $dbh->tables();
die "Cannot list database tables ($DBI::errstr)"
unless @lst > 0;printf "%u\n", scalar @lst;
exit 0;
If DBD::Oracle is build with Oracle 8.0.6 it works with Oracle Server of Version 7.3.4, 8.0.6 and 8.1.7. But if DBD::Oracle is build with Oracle 8.1.7 it works only with the 8.0.6 and 8.1.7. Using a 7.3.4 Server it returns a empty list and prints a ORA-65285 error message (see attachment).
If I try the SQL statement build by $dbh->table_info() on the same 7.3.4 database using the 8.1.7 sqlplus it works.
BTW DBI::errstr will be lost in tables(). After fetchall_arrayref() it is set but after $dbh->get_info(29) it is empty. Is this OK?
regards, Silvio
DBI 1.42-nothread default trace level set to Ox3/0 (in pid 5337)
Note: perl is running without the recommended perl -w option
-> DBI->connect(dbi:Oracle:dbprodr, scott, ****)
-> DBI->install_driver(Oracle) for hpux perl=5.006001 pid=5337 ruid=375 euid=375
install_driver: DBD::Oracle version 1.15 loaded from
/prod/tools/lib/p5.6.1/DBD/Oracle.pm
New DBI::dr (for DBD::Oracle::dr, parent=, id=)
dbih_setup_handle(DBI::dr=HASH(0x40147188)=>DBI::dr=HASH(0x401ef368),
DBD::Oracle::dr, 0, Null!)
dbih_make_com(Null!, 00000000, DBD::Oracle::dr, 108, 00000000) thr#00000000
-> STORE in DBD::_::common for DBD::Oracle::dr
(DBI::dr=HASH(0x40147188)~0x401ef368 'ShowErrorStatement' 1)
STORE DBI::dr=HASH(0x401ef368) 'ShowErrorStatement' => 1
<- STORE= 1 at Oracle.pm line 64
<- install_driver= DBI::dr=HASH(0x40147188)
-> connect for DBD::Oracle::dr (DBI::dr=HASH(0x40147188)~0x401ef368 'dbprodr'
'scott' **** HASH(0x401f4660))
New DBI::db (for DBD::Oracle::db, parent=DBI::dr=HASH(0x401ef368), id=)
dbih_setup_handle(DBI::db=HASH(0x400f3f8c)=>DBI::db=HASH(0x401f4690),
DBD::Oracle::db, 401432c0, Null!)
dbih_make_com(DBI::dr=HASH(0x401ef368), 40030b08, DBD::Oracle::db, 124, 00000000)
thr#00000000
<- connect= DBI::db=HASH(0x400f3f8c) at DBI.pm line 587
-> STORE for DBD::Oracle::db (DBI::db=HASH(0x401f4690)~INNER 'PrintError' 1)
STORE DBI::db=HASH(0x401f4690) 'PrintError' => 1
<- STORE= 1 at DBI.pm line 631
-> STORE for DBD::Oracle::db (DBI::db=HASH(0x401f4690)~INNER 'AutoCommit' 1)
<- STORE= 1 at DBI.pm line 631
-> STORE for DBD::Oracle::db (DBI::db=HASH(0x401f4690)~INNER 'Username' 'scott')
STORE DBI::db=HASH(0x401f4690) 'Username' => 'scott'
<- STORE= 1 at DBI.pm line 634 via xxx.pl line 19
<> FETCH= 'scott' ('Username' from cache) at DBI.pm line 634
<- connect= DBI::db=HASH(0x400f3f8c)
-> STORE for DBD::Oracle::db (DBI::db=HASH(0x401f4690)~INNER 'dbi_connect_closure'
CODE(0x401f4134))
STORE DBI::db=HASH(0x401f4690) 'dbi_connect_closure' => CODE(0x401f4134)
<- STORE= 1 at DBI.pm line 648 via xxx.pl line 19
-> tables in DBD::_::db for DBD::Oracle::db (DBI::db=HASH(0x400f3f8c)~0x401f4690)
1 -> table_info for DBD::Oracle::db (DBI::db=HASH(0x401f4690)~INNER undef undef
undef undef undef)
2 -> prepare for DBD::Oracle::db (DBI::db=HASH(0x401f4690)~INNER 'SELECT *
FROM
(
SELECT /*+ RULE*/
NULL TABLE_CAT
, t.OWNER TABLE_SCHEM
, t.TABLE_NAME TABLE_NAME
, decode(t.OWNER
, 'SYS' , 'SYSTEM '
, 'SYSTEM' , 'SYSTEM '
, '' ) || t.TABLE_TYPE TABLE_TYPE
, c.COMMENTS REMARKS
FROM ALL_TAB_COMMENTS c
, ALL_CATALOG t
WHERE c.OWNER (+) = t.OWNER
AND c.TABLE_NAME (+) = t.TABL...')
New DBI::st (for DBD::Oracle::st, parent=DBI::db=HASH(0x401f4690), id=)
dbih_setup_handle(DBI::st=HASH(0x401f4720)=>DBI::st=HASH(0x401f602c),
DBD::Oracle::st, 401f472c, Null!)
dbih_make_com(DBI::db=HASH(0x401f4690), 40025a08, DBD::Oracle::st, 208, 00000000)
thr#00000000
dbd_st_prepare'd sql SELECT (pl1, al1, cs1)
dbd_describe SELECT (EXPLICIT, lb 80)...
fbh 1: 'TABLE_CAT' NULLable, otype 1-> 5, dbsize 0/1, p0.s0
fbh 2: 'TABLE_SCHEM' NO null , otype 1-> 5, dbsize 30/31, p30.s0
fbh 3: 'TABLE_NAME' NO null , otype 1-> 5, dbsize 30/31, p30.s0
fbh 4: 'TABLE_TYPE' NULLable, otype 1-> 5, dbsize 18/19, p18.s0
fbh 5: 'REMARKS' NULLable, otype 1-> 5, dbsize 2000/2001, p2000.s0
dbd_describe'd 5 columns (row bytes: 2078 max, 1039 est avg, cache: 13)
2 <- prepare= DBI::st=HASH(0x401f4720) at Oracle.pm line 452
-> execute for DBD::Oracle::st (DBI::st=HASH(0x401f4720)~0x401f602c)
dbd_st_execute SELECT (out0, lob0)...
dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0)
<- execute= '0E0' at Oracle.pm line 453
1 <- table_info= DBI::st=HASH(0x401f4720) at DBI.pm line 1531 via xxx.pl line 25
-> fetchall_arrayref for DBD::Oracle::st (DBI::st=HASH(0x401f4720)~0x401f602c)
dbd_st_fetch 5 fields...
!! ERROR: 65285 'ORA-65285: Message 65285 not found; product=RDBMS; facility=ORA
(DBD ERROR: OCIStmtFetch)' (err#0)
<- fetchall_arrayref= [ ] at DBI.pm line 1532 via xxx.pl line 25
1 -> get_info for DBD::Oracle::db (DBI::db=HASH(0x401f4690)~INNER 29)
1 <- get_info= '"' at DBI.pm line 1534 via xxx.pl line 25
<- tables= ( ) [0 items] at xxx.pl line 25
<> DESTROY ignored for outer handle DBI::st=HASH(0x401f4720) (inner
DBI::st=HASH(0x401f602c) has ref cnt 1)
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x401f602c)~INNER)
<- DESTROY= undef at xxx.pl line 27
-> $DBI::errstr (&) FETCH from lasth=HASH
>> DBD::Oracle::db::errstr
<- $DBI::errstr= undef
Cannot list database tables () at xxx.pl line 27.
<> DESTROY ignored for outer handle DBI::db=HASH(0x400f3f8c) (inner
DBI::db=HASH(0x401f4690) has ref cnt 1)
-> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x401f4690)~INNER)
<- DESTROY= undef
-- DBI::END
-> disconnect_all for DBD::Oracle::dr (DBI::dr=HASH(0x40147188)~0x401ef368)
<- disconnect_all= (not implemented) at DBI.pm line 657 via xxx.pl line 0
! <> DESTROY ignored for outer handle DBI::dr=HASH(0x40147188) (inner
DBI::dr=HASH(0x401ef368) has ref cnt 1)
! -> DESTROY for DBD::Oracle::dr (DBI::dr=HASH(0x401ef368)~INNER)
! <- DESTROY= (not implemented) during global destruction
