Successful DBD::Oracle DBI build under Perl 5.8.3 on HP-UX 11.11?

[Alzhy]

I think I finally was able to build out DBD::Oracle DBI Perl environment after many failed attempts. I've built it for Oracle 9.2.0.6. And Here's what I used:

HP C Compiler C.03.37.01 (B3913DB)
DBI-1.50
DBD-Oracle-1.16
Perl 5.8.3 from software.hp.com for HP-UX 11.11. v1.0:

[ /root/PERL/DBD-Oracle-1.16 ]
root@pgdbsrv1 # perl -v

This is perl, v5.8.3 built for PA-RISC1.1-thread-multi
(with 8 registered patches, see perl -V for more detail)

Copyright 1987-2003, Larry Wall

Binary build 809 provided by ActiveState Corp. http://www.ActiveState.com
ActiveState is a division of Sophos.
Built May 17 2005 18:12:56

ORACLE_BASE=/oracle/app/oracle
ORACLE_SID=ptars
ORACLE_OWNER=oracle
ORACLE_TERM=xterm
ORACLE_HOME=/oracle/app/oracle/product/9.2.0.6
ORACLE_USERID=user/password
LD_LIBRARY_PATH=/oracle/app/oracle/product/9.2.0.6/lib:/usr/lib
LD_PRELOAD=/oracle/app/oracle/product/9.2.0.6/JRE/lib/PA_RISC/native_threads/libjava.sl

make test yields:

# make test
PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
t/01base................ok
t/10general.............ok
t/15nls.................ok
t/20select..............ok
t/21nchar............... Database and client versions and character sets:
Database 9.2.0.6.0 CHAR set is WE8ISO8859P1 (Non-Unicode), NCHAR set is AL16UTF16 (Unicode)
Client 9.2.0.6 NLS_LANG is '', NLS_NCHAR is ''
t/21nchar...............ok
t/22nchar_al32utf8......ok
t/22nchar_utf8..........ok
t/23wide_db.............skipped
all skipped: Database character set is not Unicode
t/23wide_db_8bit........skipped
all skipped: Database character set is not Unicode
t/23wide_db_al32utf8....skipped
all skipped: Database character set is not Unicode
t/24implicit_utf8.......ok
t/25plsql...............ok
t/30long................ok
122/470 skipped: various reasons
t/31lob.................ok
t/40ph_type.............ok 11/19 Placeholder behaviour for ora_type=1 (the default) varies with Oracle version.
Oracle 7 didn't strip trailing spaces, Oracle 8 did, until 9.2.x
Your system doesn't. If that seems odd, let us know.
t/40ph_type.............ok
t/50cursor..............ok
t/60reauth..............ORACLE_USERID_2 not defined. Tests skipped.
skipped
all skipped: no reason given
t/70meta................ok
All tests successful, 4 tests and 122 subtests skipped.
Files=18, Tests=1883, 145 wallclock secs (19.70 cusr + 2.28 csys = 21.98 CPU)
PERL_DL_NONLAZY=1 /usr/bin/perl "-Iblib/lib" "-Iblib/arch" test.pl


Extra tests. These are less formal and you need to read the output
to see if it looks reasonable and matches what the tests says is expected.

Oraperl emulation interface version 1.44
DBD::Oracle 1.16 using OCI9.2.0.6 by Tim Bunce
DBI 1.50 by Tim Bunce

Data sources:
dbi:Oracle:dummy


Connecting
to '' (from command line, else uses ORACLE_SID or TWO_TASK - recommended)
as 'system/manager' (via ORACLE_USERID env var or default - recommend name/passwd@dbname)
(ORACLE_SID='ptars', TWO_TASK='ptars.xyz.com')
Fields: 6
Names: 'NUM_T' 'DATE_T' 'CHAR_T' 'ROWID_T' 'RAW_T''NULL_T'
Lengths: 172 76 31 21 3 1
OraTypes: 2 12 1 104 23 1
SQLTypes: 8 93 12 -9104 -2 12
Scale: 0 0 0 0 0 0
Precision: 126 75 30 20 2 0
Nullable: 1 1 1 1 1 1
Est row width: 32
Data rows:
fetch: '7.2', '19-JAN-06', 'SYSTEM', 'AAAADeAABAAAAZaAAA', '7D', undef

ora_logoff...
lda out of scope...

Testing repetitive connect/open/close/disconnect:
If this test hangs then read the README.help file.
Expect sequence of digits, no other messages:
1 2 3 4 5

Test interaction of explicit close/logoff and implicit DESTROYs
Expect just 'done.', no other messages:
done.

Testing row cache (5).
Test completed in 1 seconds.

Test complete (6 seconds).
If the tests above have produced the 'expected' output then they have passed.


My compile/make appears to have no errors.

QUESTIONS:

1.) Does my build appear right? I do not know (yet) how to write Perl code using DBI so.

2.) Can I just copy my entire /opt/perl tree to my other machines? I do not have C compiler on my production environments.

3.) Any simple perl code to check for the installed module?


Thanks a lot.

[Steven E. Protter]

1) Let a perl developer test it.

2) You can copy your perl binaries whereever yo uput them ot other machines with the same OS. Perhaps other OS' but I doubt it.

3) Someone else can post that.

Good job.

Good Luck.

Hope you are well Harley Man.

SEP

[David Bellamy]

#!/usr/bin/perl

use DBI

$dbh=DBI->connect("DBI:oracle:orainst:table","oracle userid","passwd");
$sth=$dbh->prepare("select * from table");
$sth->execute;
while($rst=$sth->fetchrow_array()) {
print "$rst\n";
}
$dbh->disconnect;

if you get back some rows from the table you know that the module works :)

[Alzhy]

David, thanks. Here's what transpired:

#!/usr/bin/perl

use DBI

$dbh=DBI->connect("DBI:Oracle:ptars:ALL_TABLES","system","manager");
$sth=$dbh->prepare("select * from ALL_TABLES");
$sth->execute;
while($rst=$sth->fetchrow_array()) {
print "$rst\n";
}
$dbh->disconnect;


Result:

# ./testdbi.pl
DBI connect('ptars:ALL_TABLES','system',...) failed: ORA-06401: NETCMN: invalid driver designator (DBD ERROR: OCIServerAttach) at ./testdbi.pl line 5
"" is not exported by the DBI module
Can't continue after import errors at ./testdbi.pl line 5
BEGIN failed--compilation aborted at ./testdbi.pl line 5.


I earlier changed DBI:oracle to DBI:Oracle in the syntax / sample code you provided. Anything I am missing? I can connect manually via SQLPLUS.

[Ralph Grothe]

Hi Nelson,

probably only a spelling typo in David's example code.
You ought to check your available data sources.
DBI aids you in that like so

$ perl -MDBI -le 'print "@{[DBI->data_sources(grep /oracle/i, DBI->available_drivers)]}"'
dbi:Oracle:FREMD dbi:Oracle:LOLA dbi:Oracle:TEST3


Undoubtedly, the hardest thing in programming with the Perl DBI is to get your connect() calls proper.
Especially Oracle is a candidate which by its many incantations of conceivable connects can give you a hard time.
Please, read the DBD::Oracle POD carefully
viz.
"perldoc DBD::Oracle"
or here
http://search.cpan.org/~timb/DBD-Oracle-1.16/Oracle.pm

[Ralph Grothe]

Have you checked what the DBI->data_sources class method returns in your case?
I'm not sure whether case is significant here,
but to me it looks as if the connect() constructor dismissed your upper case "DBI".

[Alzhy]

No luck..

# perl -MDBI -le 'print "@{[DBI->data_sources(grep /oracle/i, DBI->available_drivers)]}"'

dbi:Oracle:dummy

I have the correct module name.

# ./testdbi.pl
DBI connect('ptars:ALL_TABLES','Oracle system',...) failed: ORA-06401: NETCMN: invalid driver designator (DBD ERROR: OCIServerAttach) at ./testdbi.pl line 5
"" is not exported by the DBI module
Can't continue after import errors at ./testdbi.pl line 5
BEGIN failed--compilation aborted at ./testdbi.pl line 5.



My test code:

#!/usr/bin/perl

use DBI

$dbh=DBI->connect("DBI:Oracle:ptars:ALL_TABLES","system","manager");
$sth=$dbh->prepare("select * from ALL_TABLES");
$sth->execute;
while($rst=$sth->fetchrow_array()) {
print "$rst\n";
}
$dbh->disconnect;

[H.Merijn Brand (procura]

Why don't you let DBI tell you what's wrong?

use DBI
DBI->trace (9);

Enjoy, Have FUN! H.Merijn

[Alzhy]

Ahh the Wizard of Perl, Merijn...

I am attaching the debugged/verbose output as a result of the trace.

Thanks a lot.

[H.Merijn Brand (procura]

ORACLE_SID=dummy

is passed to the driver
I think you wanted 'ptars'

--8<---
BEGIN {
$ENV{ORACLE_SID} = "ptars";
}
use DBI;
-->8---

Does that help?

BTW, I think your log might contain private info. You could ask the moderators to remove it if you are in working order.

Enjoy, Have FUN! H.Merijn

[Alzhy]

ENVIRONMENT:

ORACLE_BASE=/oracle/app/oracle
ORA_DUMP=/oracle/app/oracle/admin/dummy/bdump
LD_PRELOAD=/oracle/app/oracle/product/9.2.0.6/JRE/lib/PA_RISC/native_threads/libjava.sl
ORACLE_SID=ptars
ORACLE_OWNER=oracle
TNS_ADMIN=/oracle/app/oracle/product/9.2.0.6/network/admin
ORACLE_TERM=xterm
LD_LIBRARY_PATH=/opt/cobol/lib:/oracle/app/oracle/product/9.2.0.6/lib:/usr/lib
TWO_TASK=ptars.xyz.com
ORACLE_HOME=/oracle/app/oracle/product/9.2.0.6
ORAENV_ASK=NO


CODE:
#!/usr/bin/perl

BEGIN {
$ENV{ORACLE_SID} = "ptars";
}
use DBI

$dbh=DBI->connect("DBI:Oracle:ptars:ALL_TABLES","system","manager");
$sth=$dbh->prepare("select * from ALL_TABLES");
$sth->execute;
while($rst=$sth->fetchrow_array()) {
print "$rst\n";
}
$dbh->disconnect;


RESULT:

# ./testdbi.pl
DBI connect('ptars:ALL_TABLES','system',...) failed: ORA-06401: NETCMN: invalid driver designator (DBD ERROR: OCIServerAttach) at ./testdbi.pl line 8
"" is not exported by the DBI module
Can't continue after import errors at ./testdbi.pl line 8
BEGIN failed--compilation aborted at ./testdbi.pl line 8.

Still no good...

[H.Merijn Brand (procura]

use DBI

is missing a semi-colon at the end. I hope your real code *does* have it!

Now start adding the vital bits:

-->8---
#!/your/path/to/perl

use strict;
use warnings;

BEGIN {
# :
# :
}

use DBI;
:
:
:
-->8---
Enjoy, Have FUN! H.Merijn

[Alzhy]

Code:

#!/usr/bin/perl

BEGIN {
$ENV{ORACLE_SID} = "ptars";
}

use DBI;
use strict;
use warnings;

$dbh=DBI->connect("DBI:Oracle:ptars:ALL_TABLES","system","manager");
$sth=$dbh->prepare("select * from ALL_TABLES");
$sth->execute;
while($rst=$sth->fetchrow_array()) {
print "$rst\n";
}
$dbh->disconnect;


Result:

# ./testdbi.pl
Global symbol "$dbh" requires explicit package name at ./testdbi.pl line 11.
Global symbol "$sth" requires explicit package name at ./testdbi.pl line 12.
Global symbol "$dbh" requires explicit package name at ./testdbi.pl line 12.
Global symbol "$sth" requires explicit package name at ./testdbi.pl line 13.
Global symbol "$rst" requires explicit package name at ./testdbi.pl line 14.
Global symbol "$sth" requires explicit package name at ./testdbi.pl line 14.
Global symbol "$rst" requires explicit package name at ./testdbi.pl line 15.
Global symbol "$dbh" requires explicit package name at ./testdbi.pl line 17.
Execution of ./testdbi.pl aborted due to compilation errors.

[H.Merijn Brand (procura]

#!/usr/bin/perl

use strict;
use warnings;

BEGIN {
$ENV{ORACLE_SID} = "ptars";
}
use DBI;
my $dbh = DBI->connect ("DBI:Oracle:ptars:ALL_TABLES", "system", "manager");
my $sth = $dbh->prepare ("select * from ALL_TABLES");
my $rst;
$sth->execute;
$sth->bind_columns (\$rst);
while ($sth->fetch) {
print "$rst\n";
}
$dbh->disconnect;


FWIW in your original code

while ($rst = $sth-fetchrow_array ()) {

does not work, as fetchrow_array () returns a list, so that should have been

while (($rst) = $sth-fetchrow_array ()) {

Enjoy, Have FUN! H.Merijn

[Alzhy]

Still no good Merijn:

root# cat testdbi.pl
#!/usr/bin/perl

use strict;
use warnings;

BEGIN {
$ENV{ORACLE_SID} = "ptars";
}
use DBI;
my $dbh = DBI->connect ("DBI:Oracle:ptars:ALL_TABLES", "system", "manager");
my $sth = $dbh->prepare ("select * from ALL_TABLES");
my $rst;
$sth->execute;
$sth->bind_columns (\$rst);
while ($sth->fetch) {
print "$rst\n";
}
$dbh->disconnect;


[ /root ]
root# ./testdbi.pl
DBI connect('ptars:ALL_TABLES','system',...) failed: ORA-06401: NETCMN: invalid driver designator (DBD ERROR: OCIServerAttach) at ./testdbi.pl line 10
Can't call method "prepare" on an undefined value at ./testdbi.pl line 11.

[H.Merijn Brand (procura]

Obviously, your $dbh does not get set, so the connect failed. Take smaller steps.
You cannot prepare if the connect failed.

FWIW I assume you can connect with SQLplus?

#!/usr/bin/perl
use strict;
use warnings;
BEGIN {$ENV{ORACLE_SID} = "ptars";}
use DBI;
my $dbh = DBI->connect ("DBI:Oracle:ptars:ALL_TABLES", "system", "manager",
{ RaiseError => 1, PrintError => 1) or or die $DBI::errstr;

Enjoy, Have FUN! H.Merijn

[Alzhy]

Yes I can access the DB via SqlPlus as follows:

ENV:
root# env|egrep "LD_|ORA|TNS|TWO"
ORACLE_BASE=/oracle/app/oracle
ORA_DUMP=/oracle/app/oracle/admin/dummy/bdump
LD_PRELOAD=/oracle/app/oracle/product/9.2.0.6/JRE/lib/PA_RISC/native_threads/libjava.sl
ORACLE_SID=dummy
ORACLE_OWNER=oracle
TNS_ADMIN=/oracle/app/oracle/product/9.2.0.6/network/admin
ORACLE_TERM=xterm
LD_LIBRARY_PATH=/opt/cobol/lib:/oracle/app/oracle/product/9.2.0.6/lib:/usr/lib:/oracle/app/oracle/product/9.2.0.6/lib:/usr/lib
ORACLE_HOME=/oracle/app/oracle/product/9.2.0.6
TWO_TASK=ptars.xyz.com
ORAENV_ASK=NO

SQLPLUS:

root# sqlplus system@ptars

SQL*Plus: Release 9.2.0.6.0 - Production on Fri Jan 20 11:04:58 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production


Also:

root# cat x.pl
#!/usr/bin/perl
use strict;
use warnings;
BEGIN {$ENV{ORACLE_SID} = "ptars";}
use DBI;
my $dbh = DBI->connect ("DBI:Oracle:ptars:ALL_TABLES", "system", "manager");



root# ./x.pl
DBI connect('ptars:ALL_TABLES','system',...) failed: ORA-06401: NETCMN: invalid driver designator (DBD ERROR: OCIServerAttach) at ./x.pl line 6



Cannot get the syntax right for the last line in your script.

[H.Merijn Brand (procura]

Next shot: let DBD::Oracle decide the DSN itself based on the env:

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $dbh = DBI->connect ("DBI:Oracle:", "system", "manager", { RaiseError => 1,PrintError => 1 }) or die $DBI::errstr;
print "YEAH! Connected!\n";

Enjoy, Have FUN! H.Merijn

[Alzhy]

I am happy to announce my Build indeed is in order. Special Thanks to Merijn for the guidance.

My test script:

#!/usr/bin/perl

$ORA_DB='dbi:Oracle:'. lc($ENV{"ORACLE_SID"});
use DBI;

$dbh = DBI->connect ($ORA_DB, "system", "manager");
$sth=$dbh->prepare("select table_name from ALL_TABLES");
$sth->execute;
while($rst=$sth->fetchrow_array()) {
print "$rst\n";
}

$dbh->disconnect;


It was just a syntax issue after all.

So this recipe works:

Perl 5.8.3 from HP's Official Release
DBD-Oracle-1.16
DBI-1.50

[Pelephone System]

i did the same whith perl 5.8.7 and it work fine.

i just need to replace the link from perl_32 to perl_64.

thank's.

good job.

[Alzhy]

Thanks for the note.

I also ditched the /opt installing current ActiveState/HP build of Perl 5.8.3 and instead built our own /usr/local/perl5.8.7 installing version. All the more popular modules are the latest.

I followed simply the Readmes in the source tree. I recall I had to declare an LD_PRELOAD envar for my successful DBD build.

My clients have tested it and all appears okay.

[ /root ]
root@fhd002a0 # /usr/local/perl5.8.7/bin/perl -V
Summary of my perl5 (revision 5 version 8 subversion 7) configuration:
Platform:
osname=hpux, osvers=11.11, archname=PA-RISC2.0-LP64
uname='hp-ux pgdbsrv1 b.11.11 u 9000800 692359306 unlimited-user license '
config_args='-Ubincompat5005 -Duselargefiles -A prepend:libswanted=cl pthread -Duse64bitall'
hint=recommended, useposix=true, d_sigaction=define
usethreads=undef use5005threads=undef useithreads=undef usemultiplicity=undef
useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
use64bitint=define use64bitall=define uselongdouble=undef
usemymalloc=n, bincompat5005=undef
Compiler:
cc='cc', ccflags =' +z -Ae -D_HPUX_SOURCE -Wl,+vnocompatwarnings +DD64 -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 ',
optimize='+O2 +Onolimit',
cppflags='-Aa -D__STDC_EXT__ -D_HPUX_SOURCE +z -Ae -D_HPUX_SOURCE -Wl,+vnocompatwarnings +DD64 -I/usr/local/include'
ccversion='B.11.11.06', gccversion='', gccosandvers=''
intsize=4, longsize=8, ptrsize=8, doublesize=8, byteorder=87654321
d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=16
ivtype='long', ivsize=8, nvtype='double', nvsize=8, Off_t='off_t', lseeksize=8
alignbytes=8, prototype=define
Linker and Libraries:
ld='/usr/bin/ld', ldflags =' +DD64 -L/usr/local/lib -L/lib/pa20_64'
libpth=/usr/local/lib /lib/pa20_64 /lib /usr/lib /usr/ccs/lib
libs=-lcl -lpthread -lnsl -lnm -ldl -ldld -lm -lsec -lc
perllibs=-lcl -lpthread -lnsl -lnm -ldl -ldld -lm -lsec -lc
libc=/lib/pa20_64/libc.sl, so=sl, useshrplib=false, libperl=libperl.a
gnulibc_version=''
Dynamic Linking:
dlsrc=dl_hpux.xs, dlext=sl, d_dlsymun=undef, ccdlflags='-Wl,-E -Wl,-B,deferred '
cccdlflags='+Z', lddlflags='-b +vnocompatwarnings -L/usr/local/lib -L/lib/pa20_64'


Characteristics of this binary (from libperl):
Compile-time options: USE_64_BIT_INT USE_64_BIT_ALL USE_LARGE_FILES
Built under hpux
Compiled at Jan 24 2006 08:57:44
@INC:
/usr/local/perl5.8.7/lib/5.8.7/PA-RISC2.0-LP64
/usr/local/perl5.8.7/lib/5.8.7
/usr/local/perl5.8.7/lib/site_perl/5.8.7/PA-RISC2.0-LP64
/usr/local/perl5.8.7/lib/site_perl/5.8.7
/usr/local/perl5.8.7/lib/site_perl
.

[ /root ]
root@fhd002a0 #

[Alzhy]

And btw, it is 64-bit (is it?):

root# file perl
perl: ELF-64 executable object file - PA-RISC 2.0 (LP64)


DBD::Oracle was built against Oracle 9.x.

[Stanley Merkx]

Have you found a solution yet?

I found this thread after googling for a the same error message ans managed to solve my problem through some of the stuff mentioned in this thread.

The following is what works for me:

use DBI;

my $orahost = "sbserver01";
my $orasid = "ORASID";
my $oraport = "1521";

my $user = "theuser";
my $password = "thepass";

my $data_source = "dbi:Oracle:sid=$orasid;host=$orahost;port=$oraport";

my $dbh = DBI->connect($data_source, $user, $password) or die "Can't connect to $data_source: $DBI::errstr";

HtH,
Stanley.