Building DBD::Oracle on cygwin
Hi, I'm trying to build DBD::Oracle 1.12 on the latest cygwin, and perl 5.8 - without much success. I have Oracle 9.2 installed on the XP side of my machine, and have verified it is functional. I can run sql*plus from cygwin (if that proves anything). Every other Perl module (including DBI) has installed and worked flawlessly. As you will see from the output below, my $ORACLE_HOME is set to /cygdrive/f/oracle/ora92 (which translates to f:\oracle\ora92 in the XP world). I followed the instructions in README.wingcc to build liboci.a. Here is the output from Makefile.PL ... [DBD-Oracle-1.12]$ perl Makefile.PL Using DBI 1.30 installed in /usr/lib/perl5/site_perl/5.8.0/cygwin-multi-64int/auto/DBI !!! Duplicate specification S=s for option s Configuring DBD::Oracle ... Remember to actually *READ* the README file! Especially if you have any problems. Using Oracle in /cygdrive/f/oracle/ora92 Found header files in rdbms/demo. Found oci directory Using OCI directory 'oci' Using liboci.a (did you build it?) System: perl5.008 cygwin_nt-5.0 kmbestst 1.3.12(0.5432) 2002-07-06 02:16 i686 unknown Compiler: gcc -O2 -DPERL_USE_SAFE_PUTENV -fno-strict-aliasing Linker: /usr/bin/ld Sysliblist: Warning: If you have problems you may need to rebuild perl with -Uusemymalloc. Checking if your kit is complete... Looks good !!! Note (probably harmless): No library found for -loci !!! Use of uninitialized value in concatenation (.) or string at Makefile.PL line 1129. LD_RUN_PATH=/cygdrive/f/oracle/ora92/lib:/cygdrive/f/oracle/ora92/rdbms/ lib Using DBD::Oracle 1.12. Using DBD::Oracle 1.12. Using DBI 1.30 installed in /usr/lib/perl5/site_perl/5.8.0/cygwin-multi-64int/auto/DBI Writing Makefile for DBD::Oracle *** If you have problems... read all the log printed above, and the README and README.help files. (Of course, you have read README by now anyway, haven't you?) There appear to be a couple of errors (lines preceeded by !!!), but I'm not sure if they are of any consequence. When I try 'make', I get the following ... ...snip.. (many lines similar to this)oci8.c:841: warning: passing arg 1 of `fprintf' from incompatible pointer type ...snip.. LD_RUN_PATH=/cygdrive/f/oracle/ora92/lib:/cygdrive/f/oracle/ora92/rdbms /lib ld2 -s -L/usr/local/lib Oracle.o dbdimp.o oci7.o oci8.o -o blib/arch/auto/DBD/Oracle/Oracle.dll /usr/lib/perl5/5.8.0/cygwin-multi-64int/CORE/libperl.dll.a gcc -shared -o Oracle.dll -Wl,--out-implib=libOracle.dll.a -Wl,--export-all-symbols -Wl,--enable-auto-import -Wl,--stack,8388608 \ -s -L/usr/local/lib Oracle.o dbdimp.o oci7.o oci8.o /usr/lib/perl5/5.8.0/cygwin-multi-64int/CORE/libperl.dll.a Creating library file: libOracle.dll.a dbdimp.o(.text+0xc08):dbdimp.c: undefined reference to `_OCIInitialize' dbdimp.o(.text+0xc72):dbdimp.c: undefined reference to `_OCIEnvInit' dbdimp.o(.text+0xcc0):dbdimp.c: undefined reference to `_OCIHandleAlloc' dbdimp.o(.text+0xd0b):dbdimp.c: undefined reference to `_OCIHandleAlloc' dbdimp.o(.text+0xd56):dbdimp.c: undefined reference to `_OCIHandleAlloc' : : dbdimp.o(.text+0x6459):dbdimp.c: undefined reference to `_OCIDescriptorFree' dbdimp.o(.text+0x64f1):dbdimp.c: undefined reference to `_OCIDescriptorFree' dbdimp.o(.text+0x65cc):dbdimp.c: undefined reference to `_OCIHandleFree' oci8.o(.text+0x98e):oci8.c: undefined reference to `_OCIErrorGet' oci8.o(.text+0x12e6):oci8.c: undefined reference to `_OCIHandleAlloc' oci8.o(.text+0x134a):oci8.c: undefined reference to `_OCIStmtPrepare' oci8.o(.text+0x13a2):oci8.c: undefined reference to `_OCIHandleFree' oci8.o(.text+0x146f):oci8.c: undefined reference to `_OCIAttrGet' : oci8.o(.text+0x802f):oci8.c: undefined reference to `_OCILobWrite' oci8.o(.text+0x81e3):oci8.c: undefined reference to `_OCILobTrim' oci8.o(.text+0x8451):oci8.c: undefined reference to `_OCIHandleFree' collect2: ld returned 1 exit status perlld: *** system() failed to execute gcc -shared -o Oracle.dll -Wl,--out-implib=libOracle.dll.a -Wl,--export-all-symbols -Wl,--enable-auto-import -Wl,--stack,8388608 \ -s -L/usr/local/lib Oracle.o dbdimp.o oci7.o oci8.o /usr/lib/perl5/5.8.0/cygwin-multi-64int/CORE/libperl.dll.a Is this do-able, or am I bashing my head against a brick wall ? Any hints much appreciated. Thanks, Steve
Errors building DBI 1.30 on AIX 4.3 on Bull Escala - error in Tes t.pm for t/zz_15array_pp
Has any-one run into these errors, and have a quick solution, or know
whether or not they can be ignored safely ?
I'm installing it into a private directory because I have no system access,
and have had no problem installing other perl modules and earlier versions
of the DBI this way in the past.
Cheers, Mark.
perl Makefile.PL LIB=~/perl/lib
Creating extra DBI::PurePerl test: t/zz_01basics_pp.t
Creating extra DBI::PurePerl test: t/zz_02dbidrv_pp.t
Creating extra DBI::PurePerl test: t/zz_03hleak_pp.t
Creating extra DBI::PurePerl test: t/zz_04mods_pp.t
Creating extra DBI::PurePerl test: t/zz_05thrclone_pp.t
Creating extra DBI::PurePerl test: t/zz_10examp_pp.t
Creating extra DBI::PurePerl test: t/zz_15array_pp.t
Creating extra DBI::PurePerl test: t/zz_20meta_pp.t
Creating extra DBI::PurePerl test: t/zz_30subclass_pp.t
Creating extra DBI::PurePerl test: t/zz_40profile_pp.t
Creating extra DBI::PurePerl test: t/zz_60preparse_pp.t
Creating extra DBI::PurePerl test: t/zz_70shell_pp.t
Creating extra DBI::PurePerl test: t/zz_80proxy_pp.t
Checking if your kit is complete...
Looks good
Writing Makefile for DBI
Remember to actually *read* the README file!
Use 'make' to build the software (dmake or nmake on Windows).
Then 'make test' to execute self tests.
Then 'make install' to install the DBI and then delete this working
directory before unpacking and building any DBD::* drivers.
make
/usr/bin/perl -I/usr/opt/perl5/lib/5.00503/aix
-I/usr/opt/perl5/lib/5.00503 -MExtUtils::Command -e mkpath blib/lib/DBI
mkdir blib
mkdir blib/lib
mkdir blib/lib/DBI
rm -f blib/lib/DBI/Changes.pm
cp Changes blib/lib/DBI/Changes.pm
cp lib/DBI/Profile.pm blib/lib/DBI/Profile.pm
cp Driver_xst.h blib/arch/auto/DBI/Driver_xst.h
cp lib/DBI/W32ODBC.pm blib/lib/DBI/W32ODBC.pm
cp lib/DBD/ExampleP.pm blib/lib/DBD/ExampleP.pm
cp lib/DBI/FAQ.pm blib/lib/DBI/FAQ.pm
cp lib/DBI/Shell.pm blib/lib/DBI/Shell.pm
cp lib/DBI/ProxyServer.pm blib/lib/DBI/ProxyServer.pm
cp lib/Bundle/DBI.pm blib/lib/Bundle/DBI.pm
cp lib/DBI/Const/GetInfo/ANSI.pm blib/lib/DBI/Const/GetInfo/ANSI.pm
cp lib/DBD/Proxy.pm blib/lib/DBD/Proxy.pm
cp DBIXS.h blib/arch/auto/DBI/DBIXS.h
cp lib/DBI/Const/GetInfoReturn.pm blib/lib/DBI/Const/GetInfoReturn.pm
cp dbd_xsh.h blib/arch/auto/DBI/dbd_xsh.h
cp lib/DBI/Const/GetInfoType.pm blib/lib/DBI/Const/GetInfoType.pm
cp dbi_sql.h blib/arch/auto/DBI/dbi_sql.h
cp lib/DBD/NullP.pm blib/lib/DBD/NullP.pm
cp lib/DBD/Sponge.pm blib/lib/DBD/Sponge.pm
cp lib/DBI/Format.pm blib/lib/DBI/Format.pm
cp lib/DBI/Const/GetInfo/ODBC.pm blib/lib/DBI/Const/GetInfo/ODBC.pm
cp lib/DBI/DBD.pm blib/lib/DBI/DBD.pm
cp Driver.xst blib/arch/auto/DBI/Driver.xst
cp DBI.pm blib/lib/DBI.pm
cp lib/Win32/DBIODBC.pm blib/lib/Win32/DBIODBC.pm
cp dbipport.h blib/arch/auto/DBI/dbipport.h
cp lib/DBI/PurePerl.pm blib/lib/DBI/PurePerl.pm
/usr/bin/perl -I/usr/opt/perl5/lib/5.00503/aix
-I/usr/opt/perl5/lib/5.00503 -e 'use ExtUtils::Mksymlists; Mksymlists(N
AME = DBI, DL_FUNCS = { }, FUNCLIST = [], DL_VARS = []);'
/usr/bin/perl -p -e s/~DRIVER~/Perl/g
blib/arch/auto/DBI/Driver.xst Perl.xsi
/usr/bin/perl -I/usr/opt/perl5/lib/5.00503/aix
-I/usr/opt/perl5/lib/5.00503 /usr/opt/perl5/lib/5.00503/ExtUtils/xsubpp
-typ
emap /usr/opt/perl5/lib/5.00503/ExtUtils/typemap Perl.xs xstmp.c mv
xstmp.c Perl.c
cc -c -D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=16384
-O -DVERSION=\1.30\ -DXS_VERSION=\1.30\ -I/us
r/opt/perl5/lib/5.00503/aix/CORE -DDBI_NO_THREADS Perl.c
1506-507 (W) No licenses available. Contact your program supplier to add
additional users. Compilation will proceed shortly.
/usr/bin/perl -I/usr/opt/perl5/lib/5.00503/aix
-I/usr/opt/perl5/lib/5.00503 /usr/opt/perl5/lib/5.00503/ExtUtils/xsubpp
-typ
emap /usr/opt/perl5/lib/5.00503/ExtUtils/typemap DBI.xs xstmp.c mv
xstmp.c DBI.c
cc -c -D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=16384
-O -DVERSION=\1.30\ -DXS_VERSION=\1.30\ -I/us
r/opt/perl5/lib/5.00503/aix/CORE -DDBI_NO_THREADS DBI.c
1506-507 (W) No licenses available. Contact your program supplier to add
additional users. Compilation will proceed shortly.
Running Mkbootstrap for DBI ()
chmod 644 DBI.bs
LD_RUN_PATH= ld -o blib/arch/auto/DBI/DBI.so -bhalt:4 -bM:SRE
-bI:/usr/opt/perl5/lib/5.00503/aix/CORE/perl.exp -bE:DBI.ex
p -b noentry -lc DBI.o
chmod 755 blib/arch/auto/DBI/DBI.so
cp DBI.bs blib/arch/auto/DBI/DBI.bs
chmod 644 blib/arch/auto/DBI/DBI.bs
mkdir blib/lib/auto/DBI
/usr/bin/perl -Iblib/arch -Iblib/lib
-I/usr/opt/perl5/lib/5.00503/aix -I/usr/opt/perl5/lib/5.00503 dbiproxy.PL
dbiproxy
Extracted dbiproxy from dbiproxy.PL with variable substitutions.
mkdir blib/script
cp dbiproxy blib/script/dbiproxy
/usr/bin/perl -I/usr/opt/perl5/lib/5.00503/aix
-I/usr/opt/perl5/lib/5.00503 -MExtUtils::MakeMaker -e MY-fixin(shift)
bli
b/script/dbiproxy
/usr/bin/perl -Iblib/arch -Iblib/lib
RE: Errors building DBI 1.30 on AIX 4.3 on Bull Escala - error in Tes t.pm for t/zz_15array_pp
I would just ignore it myself. The compliation worked OK so anything else is probably a test harness problem. Go with it, you'll soon know if it works for what you need. Ken. -Original Message- From: Mark Buckle [mailto:[EMAIL PROTECTED]] Sent: 18 November 2002 11:32 To: [EMAIL PROTECTED] Subject: Errors building DBI 1.30 on AIX 4.3 on Bull Escala - error in Tes t.pm for t/zz_15array_pp Has any-one run into these errors, and have a quick solution, or know whether or not they can be ignored safely ? I'm installing it into a private directory because I have no system access, and have had no problem installing other perl modules and earlier versions of the DBI this way in the past. Cheers, Mark. snip Results
Execute an Oracle Function (not a Procedure)
Hello,
I have a function in our Oracle database (a function, not a procedure).
I would like to execute this function from my perl script using DBI.
I wrote something like this :
my $sql=qq[
BEGIN
OWNER.MY_FUNCTION(?,$action,?,$meta_type);
END;
];
my $dbh=DBI-connect(dbi:Oracle:$db_name,$db_user,$db_passwd);
$dbh-{AutoCommit}=0;
$dbh-{RaiseError}=1;
my $sth=$dbh-prepare($sql);
while(my($id,$name)=each %$data){
$sth-execute($id,$name);
}
$dbh-commit();
$dbh-disconnect();
But I'm getting the following error message:
DBD::Oracle::st execute failed: ORA-06550: line 2, column 48:
PLS-00201: identifier 'MAIN' must be declared
ORA-06550: line 2, column 13:
How can I fix it ? Any idea is welcome.
Thanks in advance for your help.
José.
DISCLAIMER
This e-mail and any attachment thereto may contain information which is confidential
and/or protected by intellectual property rights and are intended for the sole use of
the recipient(s) named above.
Any use of the information contained herein (including, but not limited to, total or
partial reproduction, communication or distribution in any form) by other persons than
the designated recipient(s) is prohibited.
If you have received this e-mail in error, please notify the sender either by
telephone or by e-mail and delete the material from any computer.
Thank you for your cooperation.
For further information about Proximus mobile phone services please see our website at
http://www.proximus.be or refer to any Proximus agent.
RE: Execute an Oracle Function (not a Procedure)
Generally a function returns a value which you must assign to something. So
you could do
my $sql = qq[select OWNER.MY_FUNCTION(?,$action,?,$meta_type) from dual];
instead of trying to execute an anonymous PLSQL block, which is probably
more efficient anyway.
If you need to do it in PLSQL because of pragma restrictions etc then you
would have to
my $sql = qq[BEGIN
:outval := OWNER.MY_FUNCTION(:in_id,$action,:in_name,$meta_type);
END;]
then also do bind_param_inout(:outval, \$outval.)
and bin_param_in(:in_id etc for the other parameters.
I believe it is all in the DBD::Oracle perldoc.
Ken.
-Original Message-
From: NYIMI Jose (BMB) [mailto:[EMAIL PROTECTED]]
Sent: 18 November 2002 14:33
To: [EMAIL PROTECTED]
Subject: Execute an Oracle Function (not a Procedure)
Hello,
I have a function in our Oracle database (a function, not a procedure).
I would like to execute this function from my perl script using DBI.
I wrote something like this :
my $sql=qq[
BEGIN
OWNER.MY_FUNCTION(?,$action,?,$meta_type);
END;
];
my $dbh=DBI-connect(dbi:Oracle:$db_name,$db_user,$db_passwd);
$dbh-{AutoCommit}=0;
$dbh-{RaiseError}=1;
my $sth=$dbh-prepare($sql);
while(my($id,$name)=each %$data){
$sth-execute($id,$name);
}
$dbh-commit();
$dbh-disconnect();
But I'm getting the following error message:
DBD::Oracle::st execute failed: ORA-06550: line 2, column 48:
PLS-00201: identifier 'MAIN' must be declared
ORA-06550: line 2, column 13:
How can I fix it ? Any idea is welcome.
Thanks in advance for your help.
José.
DISCLAIMER
This e-mail and any attachment thereto may contain information which is
confidential and/or protected by intellectual property rights and are
intended for the sole use of the recipient(s) named above.
Any use of the information contained herein (including, but not limited to,
total or partial reproduction, communication or distribution in any form) by
other persons than the designated recipient(s) is prohibited.
If you have received this e-mail in error, please notify the sender either
by telephone or by e-mail and delete the material from any computer.
Thank you for your cooperation.
For further information about Proximus mobile phone services please see our
website at http://www.proximus.be or refer to any Proximus agent.
Re: Building DBD::Oracle on cygwin
On Mon, 18 Nov 2002 20:26:05 +1100 Steve Baldwin [EMAIL PROTECTED] wrote: I'm trying to build DBD::Oracle 1.12 on the latest cygwin, and perl 5.8 - without much success. I have Oracle 9.2 installed on the XP side of my machine, and have verified it is functional. I can run sql*plus from cygwin (if that proves anything). Every other Perl module (including DBI) has installed and worked flawlessly. As you will see from the output below, my $ORACLE_HOME is set to /cygdrive/f/oracle/ora92 (which translates to f:\oracle\ora92 in the XP world). I followed the instructions in README.wingcc to build liboci.a. Here is the output from Makefile.PL ... [DBD-Oracle-1.12]$ perl Makefile.PL Using DBI 1.30 installed in /usr/lib/perl5/site_perl/5.8.0/cygwin-multi-64int/auto/DBI Using liboci.a (did you build it?) !!! Note (probably harmless): No library found for -loci Is this do-able, or am I bashing my head against a brick wall ? Any hints much appreciated. Where did you save the liboci.a? It should probably be in DBD-Oracle-1.12/, but it needs to be somewhere in the library search path used by Makefile.PL before you run Makefile.PL. -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Execute an Oracle Function (not a Procedure)
On Mon, 18 Nov 2002 15:32:52 +0100 NYIMI Jose (BMB) [EMAIL PROTECTED] wrote:
I have a function in our Oracle database (a function, not a procedure).
I would like to execute this function from my perl script using DBI.
I wrote something like this :
my $sql=qq[
BEGIN
OWNER.MY_FUNCTION(?,$action,?,$meta_type);
Unless you've quote()ed $action and $meta_type, you are pasting unquoted
text into the SQL. You would be better off using placeholders for them
along with the ones you already have.
You are not providing anything to receive the value returned by the
function. You will need either a PL/SQL variable that will be discarded or
a bind variable as shown in the examples in
http://search.cpan.org/author/TIMB/DBD-Oracle-1.12/Oracle.pm and
http://search.cpan.org/src/TIMB/DBD-Oracle-1.12/Oracle.ex/proc.pl . This
will require you to use bind_param() and bind_param_inout() since there is
no provision for inout parameters in execute(). An up side to that is that
you can bind $action and $meta_type outside the loop.
END;
];
my $dbh=DBI-connect(dbi:Oracle:$db_name,$db_user,$db_passwd);
$dbh-{AutoCommit}=0;
$dbh-{RaiseError}=1;
my $sth=$dbh-prepare($sql);
while(my($id,$name)=each %$data){
$sth-execute($id,$name);
}
$dbh-commit();
$dbh-disconnect();
But I'm getting the following error message:
DBD::Oracle::st execute failed: ORA-06550: line 2, column 48:
PLS-00201: identifier 'MAIN' must be declared
ORA-06550: line 2, column 13:
How can I fix it ? Any idea is welcome.
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
DBI::ODBC bug
I don't know if this is the right place to post DBI/DBD bug reports. If there is a
better place please let me know. The small program below results in an error that
reads:
The instruction at 0x1f8fe002 referenced memory at 0x1f8fe002. The memory could
not be read.
I am running a fairly recent version of activestate Perl under Windows 2000 with the
latest versions of DBI and DBD.
Detailed version information appears below the program.
use DBI;
package baz;
my $sth;
my $a;
sub bozzle {
my $dbh = shift;
$sth = $dbh-prepare(
select 'abc'
);
}
package main;
my $dbh = DBI-connect(
dbi:ODBC:insurance_miner, '', '',
{RaiseError = 1, PrintError = 0}
);
baz::bozzle($dbh);
print done\n;
*** end of program
$DBD::ODBC::VERSION = '0.28';
$DBI::VERSION = 1.28;
Output of perl -V
Summary of my perl5 (revision 5 version 6 subversion 1) configuration:
Platform:
osname=MSWin32, osvers=4.0, archname=MSWin32-x86-multi-thread
uname=''
config_args='undef'
hint=recommended, useposix=true, d_sigaction=undef
usethreads=undef use5005threads=undef useithreads=define usemultiplicity=def
ine
useperlio=undef d_sfio=undef uselargefiles=undef usesocks=undef
use64bitint=undef use64bitall=undef uselongdouble=undef
Compiler:
cc='cl', ccflags ='-nologo -O1 -MD -DNDEBUG -DWIN32 -D_CONSOLE -DNO_STRICT -
DHAVE_DES_FCRYPT -DPERL_IMPLICIT_CONTEXT -DPERL_IMPLICIT_SYS -DPERL_MSVCRT_READ
FIX',
optimize='-O1 -MD -DNDEBUG',
cppflags='-DWIN32'
ccversion='', gccversion='', gccosandvers=''
intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=1234
d_longlong=undef, longlongsize=8, d_longdbl=define, longdblsize=10
ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='off_t', lseeksize
=4
alignbytes=8, usemymalloc=n, prototype=define
Linker and Libraries:
ld='link', ldflags ='-nologo -nodefaultlib -release -libpath:C:\Perl\lib\C
ORE -machine:x86'
libpth=C:\Perl\lib\CORE
libs= oldnames.lib kernel32.lib user32.lib gdi32.lib winspool.lib comdlg32
..lib advapi32.lib shell32.lib ole32.lib oleaut32.lib netapi32.lib uuid.lib wsoc
k32.lib mpr.lib winmm.lib version.lib odbc32.lib odbccp32.lib msvcrt.lib
perllibs= oldnames.lib kernel32.lib user32.lib gdi32.lib winspool.lib comd
lg32.lib advapi32.lib shell32.lib ole32.lib oleaut32.lib netapi32.lib uuid.lib
wsock32.lib mpr.lib winmm.lib version.lib odbc32.lib odbccp32.lib msvcrt.lib
libc=msvcrt.lib, so=dll, useshrplib=yes, libperl=perl56.lib
Dynamic Linking:
dlsrc=dl_win32.xs, dlext=dll, d_dlsymun=undef, ccdlflags=' '
cccdlflags=' ', lddlflags='-dll -nologo -nodefaultlib -release -libpath:C:
\Perl\lib\CORE -machine:x86'
Characteristics of this binary (from libperl):
Compile-time options: MULTIPLICITY USE_ITHREADS PERL_IMPLICIT_CONTEXT PERL_IMP
LICIT_SYS
Locally applied patches:
ActivePerl Build 631
Built under MSWin32
Compiled at Jan 2 2002 17:16:22
@INC:
C:/Perl/lib
C:/Perl/site/lib
.
Re: Execute an Oracle Function (not a Procedure)
Maybe I am missing something herre (It is Monday after all) but
shouldn't a function be called in a select statement:
select OWNER.MY_FUNCTION(?,$action,?,$meta_type) from dual
or something similar?
On Mon, 2002-11-18 at 10:43, Michael A Chase wrote:
On Mon, 18 Nov 2002 15:32:52 +0100 NYIMI Jose (BMB) [EMAIL PROTECTED]
wrote:
I have a function in our Oracle database (a function, not a procedure).
I would like to execute this function from my perl script using DBI.
I wrote something like this :
my $sql=qq[
BEGIN
OWNER.MY_FUNCTION(?,$action,?,$meta_type);
Unless you've quote()ed $action and $meta_type, you are pasting unquoted
text into the SQL. You would be better off using placeholders for them
along with the ones you already have.
You are not providing anything to receive the value returned by the
function. You will need either a PL/SQL variable that will be discarded or
a bind variable as shown in the examples in
http://search.cpan.org/author/TIMB/DBD-Oracle-1.12/Oracle.pm and
http://search.cpan.org/src/TIMB/DBD-Oracle-1.12/Oracle.ex/proc.pl . This
will require you to use bind_param() and bind_param_inout() since there is
no provision for inout parameters in execute(). An up side to that is that
you can bind $action and $meta_type outside the loop.
END;
];
my $dbh=DBI-connect(dbi:Oracle:$db_name,$db_user,$db_passwd);
$dbh-{AutoCommit}=0;
$dbh-{RaiseError}=1;
my $sth=$dbh-prepare($sql);
while(my($id,$name)=each %$data){
$sth-execute($id,$name);
}
$dbh-commit();
$dbh-disconnect();
But I'm getting the following error message:
DBD::Oracle::st execute failed: ORA-06550: line 2, column 48:
PLS-00201: identifier 'MAIN' must be declared
ORA-06550: line 2, column 13:
How can I fix it ? Any idea is welcome.
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
RE: Execute an Oracle Function (not a Procedure)
I have changed the code and
it works, now !
Thanks a lot to all of you there.
If you see some improvement
That I can do on the following code,
Fill free to advice me :).
Performance ?
I have 8000 rows to insert and the script took about 7 min.
The function is a simple insert statement.
Is this performance out of value you would expect ?
Thanks again for your support ...
José.
= PERL CODE
sub insert_into_buffer{
my($data,$action,$meta_type)=@_;
my $this_sub=(caller(0))[3];
print localtime(). -- running $this_sub\n;
my($out_val,$id,$name,);
my $sql=qq[
BEGIN
:out_val
:=CNI.F_SET_MAXIMO_BT_EQP_HRY_NE(:in_id,:in_action,:in_name,:in_meta_type);
END;
];
my $dbh=DBI-connect(dbi:Oracle:$db_name,$db_user,$db_passwd);
$dbh-{AutoCommit}=0;
$dbh-{RaiseError}=1;
my $sth=$dbh-prepare($sql);
$sth-bind_param_inout(:out_val,\$out_val,10);
$sth-bind_param(:in_action,$action);
$sth-bind_param(:in_meta_type,$meta_type);
while(($id,$name)=each %$data){
$sth-bind_param(:in_id,$id);
$sth-bind_param(:in_name,$name);
$sth-execute();
}
$dbh-commit();
$dbh-disconnect();
}
= ORACLE FUNCTION
[snip]
if as_metatype='CNI_FUNCTION' then
insert into cni.maximo_bt_eqp_hry
(select
as_action,
null,
'INPRG',
as_name,
null,
null,
NOP_SUB_AREA,
f_Get_network_system(class_id),
F_GETCLASSNAME(class_id),
F_GETNESITECOWCODE(ne_id),
vendor,
release,
f_getNeName(ne_id),
f_get_cell_info(id,'DIAMOND'),
f_get_cell_info(id,'CI'),
null,
f_get_role_user(id,'CNI_FUNCTION','OWNER'),
null,
null,
null,
null,
null,
user,
sysdate(),
null,
f_get_next_key()
from cni_function
where id=al_id
);
else
[snip]
-Original Message-
From: Michael A Chase [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 18, 2002 4:43 PM
To: [EMAIL PROTECTED]; NYIMI Jose (BMB)
Subject: Re: Execute an Oracle Function (not a Procedure)
On Mon, 18 Nov 2002 15:32:52 +0100 NYIMI Jose (BMB)
[EMAIL PROTECTED] wrote:
I have a function in our Oracle database (a function, not a
procedure). I would like to execute this function from my
perl script
using DBI.
I wrote something like this :
my $sql=qq[
BEGIN
OWNER.MY_FUNCTION(?,$action,?,$meta_type);
Unless you've quote()ed $action and $meta_type, you are
pasting unquoted text into the SQL. You would be better off
using placeholders for them along with the ones you already have.
You are not providing anything to receive the value returned
by the function. You will need either a PL/SQL variable that
will be discarded or a bind variable as shown in the examples
in
http://search.cpan.org/author/TIMB/DBD-Oracle- 1.12/Oracle.pm
and
http://search.cpan.org/src/TIMB/DBD-Oracle-1.12/Oracle.ex/proc
..pl . This will require you to use bind_param() and bind_param_inout() since there
is no provision for inout parameters in execute(). An up side to that is that you can
bind $action and $meta_type outside the loop.
END;
];
my $dbh=DBI-connect(dbi:Oracle:$db_name,$db_user,$db_passwd);
$dbh-{AutoCommit}=0;
$dbh-{RaiseError}=1;
my $sth=$dbh-prepare($sql);
while(my($id,$name)=each %$data){
$sth-execute($id,$name);
}
$dbh-commit();
$dbh-disconnect();
But I'm getting the following error message:
DBD::Oracle::st execute failed: ORA-06550: line 2, column 48:
PLS-00201: identifier 'MAIN' must be declared
ORA-06550: line 2, column 13:
How can I fix it ? Any idea is welcome.
--
Mac :})
** I normally forward private questions to the appropriate mail list. ** Ask Smarter:
http://www.tuxedo.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
DISCLAIMER
This e-mail and any attachment thereto may contain information which is confidential
and/or protected by intellectual property rights and are intended for the sole use of
the recipient(s) named above.
Any use of the information contained herein (including, but not limited to, total or
partial reproduction, communication or distribution in any form) by other persons than
the designated recipient(s) is prohibited.
If you have received this e-mail in error, please notify the sender either by
telephone or by e-mail and delete the material from any computer.
Thank you for your cooperation.
For further information about Proximus mobile phone services please see our website at
http://www.proximus.be or refer to any Proximus agent.
Re: Execute an Oracle Function (not a Procedure)
On 18 Nov 2002 11:31:56 -0500 Jeff Seger [EMAIL PROTECTED] wrote: Maybe I am missing something herre (It is Monday after all) but shouldn't a function be called in a select statement: select OWNER.MY_FUNCTION(?,$action,?,$meta_type) from dual or something similar? On Mon, 2002-11-18 at 10:43, Michael A Chase wrote: You are not providing anything to receive the value returned by the function. You will need either a PL/SQL variable that will be discarded or a bind variable as shown in the examples in http://search.cpan.org/author/TIMB/DBD-Oracle-1.12/Oracle.pm and http://search.cpan.org/src/TIMB/DBD-Oracle-1.12/Oracle.ex/proc.pl . Look at the function examples in the URIs I cited. PL/SQL functions can often be used in a SELECT, but not always. Unless you are getting arguments for the function from a table, there is no advantage to using a SELECT instead of an anonymous block and there may be some additional overhead. -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
execute fails in loops only
Hi all,
I have a strange problem. I have a script that runs a series of queries
writes the results to files. The core part of this script looks like:
for (my $i = 1; $i 7; $i++) {
process(635, 'T', $i);
process(635, 'C', $i);
process(635, 'A', $i);
process(532, 'T', $i);
process(532, 'C', $i);
process(532, 'A', $i);
}
Now, that sub function, process($$$) checks the input parameters and
chooses the correct statement-handle based on that. For instance:
if ($_[0] == 532) {
if ($_[1] eq 'A') {
$sth = $sth_crosstab_all;
}
else {
$sth = $sth_crosstab;
}
}
The sub then goes ahead and does gets the data from $sth.
I should mention here that the queries being run are all crosstab queries
(MS-Access queries that aggregate data by two variables, much like a pivot
table in Excel).
The strange part is this:
- all queries work individually
- if I set the program running, it works for one full loop and then dies
midway through the second loop
As a result, to accomplish my for loop 6 times, I have to run the program
six different times!
The error message is:
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver]
Operation is not supported for this type of object. (SQL-S1000)(DBD:
st_execute/SQLExecute err=-1) at get_all_mm_data.pl line 204.
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver]
Operation is not supported for this type of object. (SQL-S1000)(DBD:
st_execute/SQLExecute err=-1) at get_all_mm_data.pl line 204.
And to further add to my confusion, lines 204-206 are:
if ($_[1] eq 'T') {
$sth-execute($_[2], 0);
}
And the error is coming on line 204 -- at the start of the if-statement.
Finally, let me repeat -- each query works perfectly individually.
Further, if the entire program will successfully run through one complete
iteration of the for {} loop each time, before crashing during the second
iteration.
Any suggestions, ideas, or guidance on this would be extremely welcome!
One confused programmer,
Paul
Re: execute fails in loops only
On Mon, 18 Nov 2002 12:44:44 -0500 (EST), Paul Boutros wrote:
Now, that sub function, process($$$) checks the input parameters and
chooses the correct statement-handle based on that. For instance:
if ($_[0] == 532) {
if ($_[1] eq 'A') {
$sth = $sth_crosstab_all;
}
else {
$sth = $sth_crosstab;
}
}
The sub then goes ahead and does gets the data from $sth.
Just an aside: you could use a hash containing the statement handles.
For example:
$sth{532}{A} = $sth_crosstab_all;
Since you seem to want a fallback, you could do:
$sth{532}{'*'} = $sth_crosstab;
Thus:
my $sth = $sth{0+$_[0]}{$_[1]} || $sth{0+$_[0]}{'*'};
I should mention here that the queries being run are all crosstab queries
(MS-Access queries that aggregate data by two variables, much like a pivot
table in Excel).
The strange part is this:
- all queries work individually
- if I set the program running, it works for one full loop and then dies
midway through the second loop
The error message is:
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver]
Operation is not supported for this type of object. (SQL-S1000)(DBD:
st_execute/SQLExecute err=-1) at get_all_mm_data.pl line 204.
And to further add to my confusion, lines 204-206 are:
if ($_[1] eq 'T') {
$sth-execute($_[2], 0);
}
Could it be that Access doesn't like two such queries running at the
same time? Thus, perhaps you should do
$sth-finish;
at the end of your loop.
--
Bart.
use of ORA_LONGRAW versus ORA_BLOB as ora_type attribute
I am rather new to DBI and a few weeks ago I was geven a program by my
Oracle DBA that showed I can write to a
BLOB data type by setting the ora_type attribute in bind to 24, as in:
$sth-bind_param(1, $data, { ora_type = 24 } );
I have use this now for several weeks and it seems like my code is working
fine.
By looking through some code in my install directory of DBD::Oracle, I found
that
24 is equivalent to ORA_LONGRAW and it seems I should have used the symbol
ORA_BLOB.
Can anyone advise me if I should switch? Is it a coincidence that I have not
noticed an error?
-
Harry Duin (pronounced as hari [as in Hari Krishna] dine [as in eat out])
[EMAIL PROTECTED] tel:(312) 974-6379 pgr:(630) 362-9981
GMI IL1-231-18-32
Bank of America 231 S. LaSalle Street Chicago IL 60604
Re: execute fails in loops only
Hi,
Thanks for your reply. I turned on trace(2, trace.txt) on the advice of
one responder. Let me summarize my code a little more clearly now -- this
is a simpler fragment than I posted previously, and still displays the
error.
sub process($$$);
$sql_a = ;
$sql_b = ;
$sth_a = $dbh-prepare($sql_a);
$sth_b = $dbh-prepare($sql_b);
for (my $i = 1; $i 7; $i++) {
process(532, 'A', $i);
process(635, 'A', $i);
}
sub process($$$) {
my $sth;
if ($_[0] == 532) {
$sth = $sth_a;
}
else {
$sth = $sth_b;
}
$sth-execute($_[0], $_[1], $_[2]);
while (my @row = $sth-fetchrow_array() ) {
}
$sth-finish();
}
So, I both finish the statement-handle at the end of the sub, and I do
instantiate a new sth variable with function-level scope each time. I
worry about the idea of *assigning* one sth to another -- in other words
I'm not sure it's legitimate to be doing:
$sth = $sth_a;
Either way, here is my trace output from the last successfully fetched row
onwards:
- fetchrow_array= ( 'Rossant 9' '22.0' '37.0' '61.0' undef undef
'90.0' undef undef undef undef undef '55.0' ) [13 items] row11127 at
get_all_mm_data.pl line 231
- fetchrow_array for DBD::ODBC::st
(DBI::st=HASH(0x1afce68)~0x1afa32c)
- fetchrow_array= ( ) [0 items] row11127 at get_all_mm_data.pl line
231
- finish for DBD::ODBC::st (DBI::st=HASH(0x1afce68)~0x1afa32c)
- finish= 1 at get_all_mm_data.pl line 265
- execute for DBD::ODBC::st (DBI::st=HASH(0x1afcda8)~0x1afcde4 5)
bind 1 == '5' (attribs: )
bind 1 == 5 (size 1/2/1, ptype 6, otype 1)
bind 1: CTy=1, STy=VARCHAR, CD=80, Sc=0, VM=1.
dbd_st_execute (for sql f28975368 after)...
st_execute/SQLExecute error -1 recorded: [Microsoft][ODBC Microsoft Access
Driver] Operation is not supported for this type of object.
(SQL-S1000)(DBD: st_execute/SQLExecute err=-1)
!! ERROR: -1 '[Microsoft][ODBC Microsoft Access Driver] Operation is
not supported for this type of object. (SQL-S1000)(DBD:
st_execute/SQLExecute err=-1)'
- execute= undef at get_all_mm_data.pl line 207
-- DBI::END
- disconnect_all for DBD::ODBC::dr
(DBI::dr=HASH(0x1b7119c)~0x1afcc10)
- disconnect_all= '' at DBI.pm line 552
- DESTROY for DBD::ODBC::st (DBI::st=HASH(0x1afcde4)~INNER)
- DESTROY= undef during global destruction
- DESTROY for DBD::ODBC::st (DBI::st=HASH(0x1afa32c)~INNER)
- DESTROY= undef during global destruction
- DESTROY for DBD::ODBC::db (DBI::db=HASH(0x1afcafc)~INNER)
- DESTROY= undef during global destruction
- DESTROY in DBD::_::common for DBD::ODBC::dr
(DBI::dr=HASH(0x1afcc10)~INNER)
- DESTROY= undef during global destruction
- DESTROY for DBD::ODBC::st (DBI::st=HASH(0x183f214)~INNER)
- DESTROY= undef during global destruction
- DESTROY for DBD::ODBC::st (DBI::st=HASH(0x1afcd0c)~INNER)
- DESTROY= undef during global destruction
And, by way of comparison here is the appropriate trace-output from one of
the queries that *did* work:
bind 1 == '4' (attribs: )
bind 1 == 4 (size 1/2/0, ptype 6, otype 1)
bind 1: CTy=1, STy=VARCHAR, CD=80, Sc=0, VM=1.
dbd_st_execute (for sql f28975368 after)...
Thanks to everybody who has replied so far -- help is greatly appreciated.
Still-confused,
Paul
Re: execute fails in loops only
On Mon, 18 Nov 2002 13:49:27 -0500 (EST) Paul Boutros [EMAIL PROTECTED] wrote: Thanks for your reply. I turned on trace(2, trace.txt) on the advice of one responder. Let me summarize my code a little more clearly now -- this is a simpler fragment than I posted previously, and still displays the error. sub process($$$); $sql_a = ; $sql_b = ; $sth_a = $dbh-prepare($sql_a); $sth_b = $dbh-prepare($sql_b); I'm not sure about Access, but some underlying databases don't like multiple statements open at the same time on a single database handle. Just for testing, try opening separate database connections for each statement and see if that helps. # For example (untested): $sth_a = $dbh_a - prepare( $sql_a ); $sth_b = $dbh_b - prepare( $sql_b ); So, I both finish the statement-handle at the end of the sub, and I do instantiate a new sth variable with function-level scope each time. I worry about the idea of *assigning* one sth to another -- in other words I'm not sure it's legitimate to be doing: $sth = $sth_a; The finish() shouldn't be necessary since you are looping until all rows are fetched. finish() is basically a hint to DBI that you won't be fetching more rows unless you execute() the handle again. $sth = $sth_a just assigns the same handle to another variable. It is perfectly legitimate, both variables contain the same value which is a reference to the statement information. Since $sth goes out of scope as soon as the procedure returns, it has no impact outside the procedure. Any operations performed on the statement through it are indistinguishable from those done using $sth_a. -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: execute fails in loops only
On Mon, 18 Nov 2002 11:11:47 -0800 (PST), Michael A Chase wrote: I'm not sure about Access, but some underlying databases don't like multiple statements open at the same time on a single database handle. Just for testing, try opening separate database connections for each statement and see if that helps. # For example (untested): $sth_a = $dbh_a - prepare( $sql_a ); $sth_b = $dbh_b - prepare( $sql_b ); If that helps, I would cache the SQL strings, not the statement handles, and use just one $dbh. -- Bart.
RE: Building DBD::Oracle on cygwin
Thanks for that Michael. My LD_LIBRARY_PATH env var was not set, so I
set it to /usr/lib, and copied liboci.a to /usr/lib. This enabled me to
successfully do a 'make'. However, on doing a 'make test', I was
getting this sort of thing ...
[DBD-Oracle-1.12]$ make test
/usr/bin/perl.exe -MExtUtils::Command::MM -e test_harness(0,
'blib/lib', 'blib/arch') t/*.t
t/base...ok
t/generalDBI-connect() failed: (UNKNOWN OCI STATUS 1804)
OCIInitialize. Check ORACLE_HOME and NLS settings etc. at t/general.t
line 20
Unable to connect to Oracle ((UNKNOWN OCI STATUS 1804) OCIInitialize.
Check ORACLE_HOME and NLS settings etc.)
Tests skiped.
This was with ORACLE_HOME set to /cygdrive/f/oracle/ora92. I then
tried sqlplus in this config, and got the following ...
[DBD-Oracle-1.12]$ export ORACLE_USERID=sbdev/sbdev@sb9i
[DBD-Oracle-1.12]$ sqlplus $ORACLE_USERID
Error 6 initializing SQL*Plus
Message file sp1lang.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software
directory
So, I unset ORACLE_HOME, and tried sqlplus again ...
[DBD-Oracle-1.12]$ unset ORACLE_HOME
[DBD-Oracle-1.12]$ sqlplus $ORACLE_USERID
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Nov 19 06:26:40 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 -
Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
I then tried 'make test', and it sort of worked, but I got all these
messages about ORACLE_HOME not set ...
[DBD-Oracle-1.12]$ make test
/usr/bin/perl.exe -MExtUtils::Command::MM -e test_harness(0,
'blib/lib', 'blib/arch') t/*.t
t/base...ok
t/generalORACLE_HOME environment variable not set!
t/generalok
t/long...ORACLE_HOME environment variable not set!
t/long...ok
t/ph_typeORACLE_HOME environment variable not set!
t/ph_typeok
t/plsql..ORACLE_HOME environment variable not set!
t/plsql..ok
t/reauth.skipped
all skipped: no reason given
All tests successful, 1 test skipped.
Files=6, Tests=281, 21 wallclock secs ( 1.07 cusr + 0.74 csys = 1.81
CPU)
:
I then wrote my own tiny test script ...
use strict ;
use DBI ;
my $dbh = DBI-connect('dbi:Oracle:', 'sbdev/sbdev@sb9i', '',
{'RaiseError'=1}) ;
my $sth = $dbh-prepare(select 'hello' from dual) ;
$sth-execute ;
my $row = $sth-fetchrow_arrayref() ;
print $row-[0] \n ;
$sth-finish ;
$dbh-disconnect ;
On running it, if ORACLE_HOME is set, I get this ...
[DBD-Oracle-1.12]$ export ORACLE_HOME=/cygdrive/f/oracle/ora92
[DBD-Oracle-1.12]$ perl sbtest.pl
DBI-connect() failed: (UNKNOWN OCI STATUS 1804) OCIInitialize. Check
ORACLE_HOME and NLS settings etc. at sbtest.pl line 3
If I unset it, I get this ...
[DBD-Oracle-1.12]$ ORACLE_HOME= perl sbtest.pl
ORACLE_HOME environment variable not set!
hello
So, it sort of works, but I get these annoying ORACLE_HOME environment
variable not set! messages.
What have I stuffed up now ???
Thanks,
Steve
-Original Message-
From: Michael A Chase [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, 19 November 2002 2:30 AM
To: [EMAIL PROTECTED]; Steve Baldwin
Subject: Re: Building DBD::Oracle on cygwin
On Mon, 18 Nov 2002 20:26:05 +1100 Steve Baldwin
[EMAIL PROTECTED] wrote:
I'm trying to build DBD::Oracle 1.12 on the latest cygwin, and perl
5.8
- without much success. I have Oracle 9.2 installed on the XP side of
my machine, and have verified it is functional. I can run sql*plus
from
cygwin (if that proves anything). Every other Perl module (including
DBI) has installed and worked flawlessly. As you will see from the
output below, my $ORACLE_HOME is set to /cygdrive/f/oracle/ora92
(which
translates to f:\oracle\ora92 in the XP world).
I followed the instructions in README.wingcc to build liboci.a.
Here is the output from Makefile.PL ...
[DBD-Oracle-1.12]$ perl Makefile.PL
Using DBI 1.30 installed in
/usr/lib/perl5/site_perl/5.8.0/cygwin-multi-64int/auto/DBI
Using liboci.a (did you build it?)
!!! Note (probably harmless): No library found for -loci
Is this do-able, or am I bashing my head against a brick wall ? Any
hints much appreciated.
Where did you save the liboci.a? It should probably be in
DBD-Oracle-1.12/, but it needs to be somewhere in the library search
path used by Makefile.PL before you run Makefile.PL.
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
RE: Execute an Oracle Function (not a Procedure)
Actually, because José mentioned he was doing an insert in his function, it cannot be called from SQL, but only from an anonymous PL/SQL block. In any instance, it is *always* faster to execute a PL/SQL function from an anonymous PL/SQL block than from a SELECT. In terms of the throughput you are seeing (8000 rows in 7 minutes), that does seem slow, but it could be one of many things. Are there any triggers on the table(s) you are inserting into ? Have you tried tracing the session, and running the trace file through tkprof ? If you're not sure how to do this, in your Perl code, you need to execute 'alter session set sql_trace = true'. You then run your script, and ask your DBA where trace files end up. It will be wherever the Oracle parameter USER_DUMP_DEST is pointing to. Note that this is on the database server machine which may not be the same as your client machine. Once you have located your trace file, you use tkprof to format it into something useful - eg 'tkprof tracefile outfile'. If you just type 'tkprofreturn' on the command line, you will see all the options. Hope this helps, Steve -Original Message- From: Michael A Chase [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 19 November 2002 4:00 AM To: Jeff Seger Cc: [EMAIL PROTECTED] Subject: Re: Execute an Oracle Function (not a Procedure) On 18 Nov 2002 11:31:56 -0500 Jeff Seger [EMAIL PROTECTED] wrote: Maybe I am missing something herre (It is Monday after all) but shouldn't a function be called in a select statement: select OWNER.MY_FUNCTION(?,$action,?,$meta_type) from dual or something similar? On Mon, 2002-11-18 at 10:43, Michael A Chase wrote: You are not providing anything to receive the value returned by the function. You will need either a PL/SQL variable that will be discarded or a bind variable as shown in the examples in http://search.cpan.org/author/TIMB/DBD-Oracle-1.12/Oracle.pm and http://search.cpan.org/src/TIMB/DBD-Oracle-1.12/Oracle.ex/proc.pl . Look at the function examples in the URIs I cited. PL/SQL functions can often be used in a SELECT, but not always. Unless you are getting arguments for the function from a table, there is no advantage to using a SELECT instead of an anonymous block and there may be some additional overhead. -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
ppm version 3.0.1 Repository errors when installing DBD:Oracle
Hello All, I am trying to install DBD::Oracle using ppm version 3.0.1. I add the repository using the following command in ppm: rep add http://www.activestate.com/PPMPackages/5.6/ Then I get the following error when I try to do the install: ppm install DBD::Oracle Missing base argument at C:/Perl/site/lib/PPM/Repository.pm line 174 It seems like a problem with the repository setting. And ideas how to get around this. Janet.
RE: Building DBD::Oracle on cygwin
OK, I have the solution (thanks David !). The answer is to set
ORACLE_HOME to what it would be on the XP side of the world (i.e. in my
case f:\oracle\ora92). I incorrectly assumed I needed to set it to the
cygwin equivalent (/cygdrive/f/oracle/ora92). Once I set it correctly,
all tests ran through OK. Maybe this could go into the README.wingcc in
some future release. The thing is, Oracle now seems to frown on having
an ORACLE_HOME environment variable set in the Windows environment. I
did have this, but when I re-ran the Oracle installer, it told me it was
removing it, because it limited my ability to have multiple Oracle
Home's.
Thanks,
Steve
-Original Message-
From: Steve Baldwin [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, 19 November 2002 6:53 AM
To: 'Michael A Chase'; [EMAIL PROTECTED]
Subject: RE: Building DBD::Oracle on cygwin
Thanks for that Michael. My LD_LIBRARY_PATH env var was not set, so I
set it to /usr/lib, and copied liboci.a to /usr/lib. This enabled me to
successfully do a 'make'. However, on doing a 'make test', I was
getting this sort of thing ...
[DBD-Oracle-1.12]$ make test
/usr/bin/perl.exe -MExtUtils::Command::MM -e test_harness(0,
'blib/lib', 'blib/arch') t/*.t t/base...ok
t/generalDBI-connect() failed: (UNKNOWN OCI STATUS 1804)
OCIInitialize. Check ORACLE_HOME and NLS settings etc. at t/general.t
line 20 Unable to connect to Oracle ((UNKNOWN OCI STATUS 1804)
OCIInitialize. Check ORACLE_HOME and NLS settings etc.) Tests skiped.
This was with ORACLE_HOME set to /cygdrive/f/oracle/ora92. I then
tried sqlplus in this config, and got the following ...
[DBD-Oracle-1.12]$ export ORACLE_USERID=sbdev/sbdev@sb9i
[DBD-Oracle-1.12]$ sqlplus $ORACLE_USERID Error 6 initializing SQL*Plus
Message file sp1lang.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software
directory
So, I unset ORACLE_HOME, and tried sqlplus again ...
[DBD-Oracle-1.12]$ unset ORACLE_HOME
[DBD-Oracle-1.12]$ sqlplus $ORACLE_USERID
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Nov 19 06:26:40 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options JServer
Release 9.2.0.1.0 - Production
SQL exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 -
Production With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
I then tried 'make test', and it sort of worked, but I got all these
messages about ORACLE_HOME not set ...
[DBD-Oracle-1.12]$ make test
/usr/bin/perl.exe -MExtUtils::Command::MM -e test_harness(0,
'blib/lib', 'blib/arch') t/*.t t/base...ok
t/generalORACLE_HOME environment variable not set! t/generalok
t/long...ORACLE_HOME environment variable not set! t/long...ok
t/ph_typeORACLE_HOME environment variable not set! t/ph_typeok
t/plsql..ORACLE_HOME environment variable not set! t/plsql..ok
t/reauth.skipped
all skipped: no reason given
All tests successful, 1 test skipped.
Files=6, Tests=281, 21 wallclock secs ( 1.07 cusr + 0.74 csys = 1.81
CPU)
:
I then wrote my own tiny test script ...
use strict ;
use DBI ;
my $dbh = DBI-connect('dbi:Oracle:', 'sbdev/sbdev@sb9i', '',
{'RaiseError'=1}) ;
my $sth = $dbh-prepare(select 'hello' from dual) ; $sth-execute ; my
$row = $sth-fetchrow_arrayref() ; print $row-[0] \n ;
$sth-finish ; $dbh-disconnect ;
On running it, if ORACLE_HOME is set, I get this ...
[DBD-Oracle-1.12]$ export ORACLE_HOME=/cygdrive/f/oracle/ora92
[DBD-Oracle-1.12]$ perl sbtest.pl
DBI-connect() failed: (UNKNOWN OCI STATUS 1804) OCIInitialize. Check
ORACLE_HOME and NLS settings etc. at sbtest.pl line 3
If I unset it, I get this ...
[DBD-Oracle-1.12]$ ORACLE_HOME= perl sbtest.pl
ORACLE_HOME environment variable not set!
hello
So, it sort of works, but I get these annoying ORACLE_HOME environment
variable not set! messages.
What have I stuffed up now ???
Thanks,
Steve
-Original Message-
From: Michael A Chase [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, 19 November 2002 2:30 AM
To: [EMAIL PROTECTED]; Steve Baldwin
Subject: Re: Building DBD::Oracle on cygwin
On Mon, 18 Nov 2002 20:26:05 +1100 Steve Baldwin
[EMAIL PROTECTED] wrote:
I'm trying to build DBD::Oracle 1.12 on the latest cygwin, and perl
5.8
- without much success. I have Oracle 9.2 installed on the XP side of
my machine, and have verified it is functional. I can run sql*plus
from
cygwin (if that proves anything). Every other Perl module (including
DBI) has installed and worked flawlessly. As you will see from the
output below, my $ORACLE_HOME is set to /cygdrive/f/oracle/ora92
(which
translates to f:\oracle\ora92 in the XP world).
I followed the instructions in README.wingcc to build liboci.a.
Here is the output from Makefile.PL ...
[DBD-Oracle-1.12]$ perl Makefile.PL
Using DBI 1.30
Re: execute fails in loops only
On Mon, 18 Nov 2002 20:46:09 +0100 Bart Lateur [EMAIL PROTECTED] wrote: On Mon, 18 Nov 2002 11:11:47 -0800 (PST), Michael A Chase wrote: I'm not sure about Access, but some underlying databases don't like multiple statements open at the same time on a single database handle. Just for testing, try opening separate database connections for each statement and see if that helps. # For example (untested): $sth_a = $dbh_a - prepare( $sql_a ); $sth_b = $dbh_b - prepare( $sql_b ); If that helps, I would cache the SQL strings, not the statement handles, and use just one $dbh. Why? Database connections aren't that expensive and keeping the statement handles avoids having to re-prepare() the SQL. (We still haven't heard whether using multiple database handles helped.) -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Count of returned rows plus
Looked at QA for databases at perl.org, been through the Cheetah book
(several times) and can't seem to find an archive of this list so I'm down
to asking for help what is probably a FAQ. But I hope to put a twist on
it.
I'm querying a table what may return more than one row. If there is more
than one row I display a selection list (so the users can pick the one
they want) or else the results of the query.
So far it is a brute force effort. I use fetchall_arrayref and determine
the count of rows returned. Question is is the correct method to return
the count of an array of references 'scalar(@$array_ref)' or is there an
easier/trickier/DBI way to check on the count?
And here's some code. (Please be gentle. I'm trying to get back into
this after several years of hit and miss coding.)
my ($dbh, $sth, $cnt, $qry_domainname);
my $domain_cnt_qry = SELECT tbl_key, domainname .
FROM domain_info .
WHERE domain_name LIKE '%?%' .
ORDER BY domainname ;
$sth = $dbh-prepare($domain_cnt_qry);
$sth-execute($qry_domainname);
$domain_array = $sth-fetchall_arrayref();
$cnt = scalar(@$domain_array);
if ($cnt 1) {
### Print a selection list of domains
} else {
### Print the domain information
}
TIA,
Rod
--
Open Source Software - Sometimes you get more than you paid for...
Sybase 'print' statements
I have some SQL with Sybase 'print' statements. I have a syb_err_handler setup, and I see the print statements coming there, but they also get printed to stderr. Is there any way to stop the output to stderr? -- David Good[EMAIL PROTECTED] This space intentionally left blank.
Building DBD::Oracle on cygwin : HOWTO
Here's what worked for me ... Platforms Windows XP Pro Cygwin 1.3.15-2 (including Perl 5.8.0) I have installed the following Oracle products : Oracle 9i Database 9.2.0.1 Oracle Net Listener 9.2.0.1 Oracle Database Utilities 9.2.0.1 Oracle Verify Utility 9.2.0.1 Oracle Call Interface (OCI) 9.2.0.1 Oracle Trace 9.2.0.1 PL/SQL 9.2.0.1 Recovery Manager 9.2.0.1 SQL*Plus 9.2.0.1 Oracle JVM 9.2.0.1 Required Support Files 9.2.0.1 Oracle Intermedia 9.2.0.1 Database Configuration Assistant 9.2.0.1 Migration Utility 9.2.0.1 Oracle Intelligent Agent 9.2.0.1 Generic Connectivity Using ODBC 9.2.0.1 Generic Connectivity Using OLEDB - FS 9.2.0.1 Generic Connectivity Using OLEDB - SQL 9.2.0.1 XML 9.2.0.1 Oracle Ultra Search Server 9.2.0.1 Oracle Starter Database 9.2.0.1 Oracle Text 9.2.0.1 Enterprise Edition Options 9.2.0.1 Oracle Net Services 9.2.0.1 Oracle Enterprise Manager Products 9.2.0.1 Oracle 9i Development Kit 9.2.0.1 Oracle HTTP Server 9.2.0.1 Oracle Windows Interfaces 9.2.0.1 iSQL*Plus 9.2.0.1 Oracle JDBC/OCI Interfaces 9.2.0.1 Installation Common Files Oracle 9i Client 9.2.0.1 Oracle Internet Directory Tools 9.2.0.1 Pro*COBOL 1.8.77.0.0 Oracle Workflow Client 2.6.2.0 Pro*COBOL 9.2.0.1 Oracle Migration Workbench 9.2.0.1 Pro*C/C++ 9.2.0.1 I know not all of these are required to get DBD::Oracle working, but that's what I've got, and it works. I suspect for client only stuff, the key components would be : Oracle Call Interface (OCI) 9.2.0.1 Required Support Files 9.2.0.1 Oracle Net Services 9.2.0.1 Oracle 9i Development Kit 9.2.0.1 Oracle Windows Interfaces 9.2.0.1 Installation Common Files Oracle 9i Client 9.2.0.1 Pro*C/C++ 9.2.0.1 In my cygwin environment, I installed DBI-1.30 as normal. That is ... perl Makefile.PL make make test make install I then downloaded DBD-Oracle-1.12.tar.gz. From there, here's what I did $ tar xfz DBD-Oracle-1.12.tar.gz $ cd DBD-Oracle-1.12 $ dlltool --input-def oci.def --output-lib liboci.a $ cp liboci.a /usr/lib $ export LD_LIBRARY_PATH=/usr/lib $ export ORACLE_HOME='f:\oracle\ora92' $ export ORACLE_USERID='sbdev/sbdev@sb9i' $ perl Makefile.PL (** Here's the output ... ***) Using DBI 1.30 installed in /usr/lib/perl5/site_perl/5.8.0/cygwin-multi-64int/auto/DBI Duplicate specification S=s for option s Configuring DBD::Oracle ... Remember to actually *READ* the README file! Especially if you have any problems. Using Oracle in f:/oracle/ora92 Found header files in rdbms/demo. Found oci directory Using OCI directory 'oci' Using liboci.a (did you build it?) System: perl5.008 cygwin_nt-5.0 kmbestst 1.3.12(0.5432) 2002-07-06 02:16 i686 unknown Compiler: gcc -O2 -DPERL_USE_SAFE_PUTENV -fno-strict-aliasing Linker: /usr/bin/ld Sysliblist: Warning: If you have problems you may need to rebuild perl with -Uusemymalloc. Checking if your kit is complete... Looks good Use of uninitialized value in concatenation (.) or string at Makefile.PL line 1129. LD_RUN_PATH=/usr/lib Using DBD::Oracle 1.12. Using DBD::Oracle 1.12. Using DBI 1.30 installed in /usr/lib/perl5/site_perl/5.8.0/cygwin-multi-64int/auto/DBI Writing Makefile for DBD::Oracle *** If you have problems... read all the log printed above, and the README and README.help files. (Of course, you have read README by now anyway, haven't you?) (** Output End ***) $ make (** This gave a heap of warnings like ...) dbdimp.c:206: warning: passing arg 1 of `fprintf' from incompatible pointer type (** but ended with ...) Running Mkbootstrap for DBD::Oracle () chmod 644 Oracle.bs rm -f blib/arch/auto/DBD/Oracle/Oracle.dll LD_RUN_PATH=/usr/lib ld2 -s -L/usr/local/lib Oracle.o dbdimp.o oci7.o oci8.o -o blib/arch/auto/DBD/Oracle/Oracle.dll /usr/lib/perl5/5.8.0/cygwin-multi-64int/CORE/libperl.dll.a -loci gcc -shared -o Oracle.dll -Wl,--out-implib=libOracle.dll.a -Wl,--export-all-symbols -Wl,--enable-auto-import -Wl,--stack,8388608 \ -s -L/usr/local/lib Oracle.o dbdimp.o oci7.o oci8.o /usr/lib/perl5/5.8.0/cygwin-multi-64int/CORE/libperl.dll.a -loci Creating library file: libOracle.dll.a mv Oracle.dll libOracle.dll.a blib/arch/auto/DBD/Oracle/ chmod 755 blib/arch/auto/DBD/Oracle/Oracle.dll cp Oracle.bs blib/arch/auto/DBD/Oracle/Oracle.bs chmod 644 blib/arch/auto/DBD/Oracle/Oracle.bs /usr/bin/perl.exe -Iblib/arch -Iblib/lib ora_explain.PL ora_explain Extracted ora_explain from ora_explain.PL with variable substitutions. cp ora_explain blib/script/ora_explain /usr/bin/perl.exe -MExtUtils::MY -e MY-fixin(shift) blib/script/ora_explain Manifying blib/man3/DBD.Oracle.3pm
RE: Count of returned rows plus
As an unfortunate user recently found out, this depends on the database. DBI offers
the 'rows' method that you would call on your statement handle to tell you the number
of rows it returned (this is NOT matched rows, for example if you use a LIMIT these
are two very different things).
I know MySQL supports this method, but I can't tell you about any other DBs.
http://danconia.org
On Mon, 18 Nov 2002 13:26:56 -0800 (PST), Roderick A. Anderson [EMAIL PROTECTED]
wrote:
Looked at QA for databases at perl.org, been through the Cheetah book
(several times) and can't seem to find an archive of this list so I'm down
to asking for help what is probably a FAQ. But I hope to put a twist on
it.
I'm querying a table what may return more than one row. If there is more
than one row I display a selection list (so the users can pick the one
they want) or else the results of the query.
So far it is a brute force effort. I use fetchall_arrayref and determine
the count of rows returned. Question is is the correct method to return
the count of an array of references 'scalar(@$array_ref)' or is there an
easier/trickier/DBI way to check on the count?
And here's some code. (Please be gentle. I'm trying to get back into
this after several years of hit and miss coding.)
my ($dbh, $sth, $cnt, $qry_domainname);
my $domain_cnt_qry = SELECT tbl_key, domainname .
FROM domain_info .
WHERE domain_name LIKE '%?%' .
ORDER BY domainname ;
$sth = $dbh-prepare($domain_cnt_qry);
$sth-execute($qry_domainname);
$domain_array = $sth-fetchall_arrayref();
$cnt = scalar(@$domain_array);
if ($cnt 1) {
### Print a selection list of domains
} else {
### Print the domain information
}
TIA,
Rod
--
Open Source Software - Sometimes you get more than you paid for...
Re: Sybase 'print' statements
On Mon, 2002-11-18 at 13:24, David L. Good wrote: I have some SQL with Sybase 'print' statements. I have a syb_err_handler setup, and I see the print statements coming there, but they also get printed to stderr. Is there any way to stop the output to stderr? Return 0 from the syb_err_handler when you see a print statement (i.e. when the error message number is 0). Michael -- Michael Peppler / [EMAIL PROTECTED] / http://www.mbay.net/~mpeppler [EMAIL PROTECTED] / ZetaTools, Inc / http://www.zetatools.com ZetaTools: Call perl functions as Sybase stored procedures! signature.asc Description: This is a digitally signed message part
RE: Count of returned rows plus
On Mon, 18 Nov 2002 [EMAIL PROTECTED] wrote: As an unfortunate user recently found out, this depends on the database. DBI offers the 'rows' method that you would call on your statement handle to tell you the number of rows it returned (this is NOT matched rows, for example if you use a LIMIT these are two very different things). Geez. I forgot to say - PostgreSQL 7.2.1. Here I was thinking I was so careful and would get all the important pieces in the message. The Cheetah book says rows() should not be used and especially not with select statements. Thanks for the suggestion though. In the same area it's suggested to use a 'count(*)' with the same where clause but that seems really brute force. I'll get the snippets of code together and test the scalar() route. Cheers, Rod -- Open Source Software - Sometimes you get more than you paid for...
Re: Sybase 'print' statements
On Mon, Nov 18, 2002 at 01:46:36PM -0800, Michael Peppler [EMAIL PROTECTED] wrote: On Mon, 2002-11-18 at 13:24, David L. Good wrote: I have some SQL with Sybase 'print' statements. I have a syb_err_handler setup, and I see the print statements coming there, but they also get printed to stderr. Is there any way to stop the output to stderr? Return 0 from the syb_err_handler when you see a print statement (i.e. when the error message number is 0). Michael That did it! Seems so obvious once I have the answer :) Thanks! -- David Good[EMAIL PROTECTED] This space intentionally left blank.
RE: Count of returned rows plus
That seems odd to me, I will have to crack open my cheetah when I get home. Honestly I have always used DBD direct for my stuff, though have been hitting myself on the hand everytime I do it ;-). The select count should be optimized at the database level, and I have used that method many times, so that I can get a total of the matching rows, and then only select a sub set of those rows using a limit, to save on the memory resources, so while it is brute forcing it should be optimized, granted the slow part is the extra call to the database at all. Theoretically your scalar method should work, but then you are sacrificing memory over processing, so it is really more an environmental decision than anything. http://danconia.org On Mon, 18 Nov 2002 13:50:58 -0800 (PST), Roderick A. Anderson [EMAIL PROTECTED] wrote: On Mon, 18 Nov 2002 [EMAIL PROTECTED] wrote: As an unfortunate user recently found out, this depends on the database. DBI offers the 'rows' method that you would call on your statement handle to tell you the number of rows it returned (this is NOT matched rows, for example if you use a LIMIT these are two very different things). Geez. I forgot to say - PostgreSQL 7.2.1. Here I was thinking I was so careful and would get all the important pieces in the message. The Cheetah book says rows() should not be used and especially not with select statements. Thanks for the suggestion though. In the same area it's suggested to use a 'count(*)' with the same where clause but that seems really brute force. I'll get the snippets of code together and test the scalar() route. Cheers, Rod -- Open Source Software - Sometimes you get more than you paid for...
rt.cpan.org id #1797
Given this example:
--START--
package Foo::DBIx;
our @ISA = qw(DBI);
package Foo::DBIx::db;
our @ISA = qw(DBI::db);
package Foo::DBIx::st;
our @ISA = qw(DBI::st);
package main;
use DBI;
$dbh = DBI-connect(
'dbi:Oracle:', 'scott', 'tiger',
{ RootClass = 'Foo::DBIx' }
);
---END---
DBI (v1.30) throws back the following error:
Can't locate Foo/DBIx.pm in @INC (@INC contains:
/home/prod/perl5/lib/5.6.1/aix /home/prod/perl5/lib/5.6.1
/home/prod/perl5/lib/site_perl/5.6.1/aix
/home/prod/perl5/lib/site_perl/5.6.1 /home/prod/perl5/lib/site_perl .) at
/home/prod/perl5/lib/site_perl/5.6.1/aix/DBI.pm line 776.
...propagated at /home/prod/perl5/lib/site_perl/5.6.1/aix/DBI.pm
line 780.
The DBI documentation says that this should be a non-fatal error:
[...] that using an explicit RootClass attribute will make the DBI
automatically attempt to load a module by that name (and not
complain if such a module can't be found) [...]
and code in DBI.pm looks like it tries to ignore this type of error:
sub _load_module {
(my $module = shift) =~ s!::!/!g;
eval {
require $module.'.pm';
};
return 1 unless $@;
return 0 if $@ =~ /^\b\@INC\b/;
die; # propagate $@;
}
But the regex used doesn't match the error thrown back by perl 5.6.1
(the regex itself may be flawed, since '@' !~ /\w/).
If Foo::DBIx is stored in Foo/DBIx.pm, the error isn't thrown.
If the regex is changed to /\@INC\b/, the error also goes away.
Thanks.
--
j.
James FitzGibbon
Consultant, Ajilon Services, TTS-3D@CC-950
[EMAIL PROTECTED]
voice/fax 612-304-6161/3277
RE: Count of returned rows plus
On Mon, 18 Nov 2002 [EMAIL PROTECTED] wrote: Theoretically your scalar method should work, but then you are sacrificing memory over processing, so it is really more an environmental decision than anything. Well there was a little method to the madness. If there is more than one row returned I'll use the returned rows to build the selection list so it won't go to waste. Indications from the users are that they will be guessing at the domain name quite often. I.e., the word 'free' is in the domain name but not sure if it is 'freestuff.com' or stuffforfree.com' There could be a problem if they did a values like 'th' and there were hundreds of thousands of domains (not this week :-) but I have sucked up 4 MBtye files on an old 386 with 8 MByte RAM running DOS using perl to save on disk accesses. I've got a heftier system now - OS and hardware. ;-) Rod -- Open Source Software - Sometimes you get more than you paid for...
Re: rt.cpan.org id #1797
On Mon, 18 Nov 2002 16:12:02 -0600 James.FitzGibbon [EMAIL PROTECTED]
wrote:
Given this example:
--START--
package Foo::DBIx;
our @ISA = qw(DBI);
package Foo::DBIx::db;
our @ISA = qw(DBI::db);
package Foo::DBIx::st;
our @ISA = qw(DBI::st);
package main;
use DBI;
$dbh = DBI-connect(
'dbi:Oracle:', 'scott', 'tiger',
{ RootClass = 'Foo::DBIx' }
);
---END---
DBI (v1.30) throws back the following error:
Can't locate Foo/DBIx.pm in @INC (@INC contains:
/home/prod/perl5/lib/5.6.1/aix /home/prod/perl5/lib/5.6.1
/home/prod/perl5/lib/site_perl/5.6.1/aix
/home/prod/perl5/lib/site_perl/5.6.1 /home/prod/perl5/lib/site_perl .) at
/home/prod/perl5/lib/site_perl/5.6.1/aix/DBI.pm line 776.
...propagated at /home/prod/perl5/lib/site_perl/5.6.1/aix/DBI.pm
line 780.
The DBI documentation says that this should be a non-fatal error:
[...] that using an explicit RootClass attribute will make the DBI
automatically attempt to load a module by that name (and not
complain if such a module can't be found) [...]
and code in DBI.pm looks like it tries to ignore this type of error:
sub _load_module {
(my $module = shift) =~ s!::!/!g;
eval {
require $module.'.pm';
};
return 1 unless $@;
return 0 if $@ =~ /^\b\@INC\b/;
die; # propagate $@;
}
But the regex used doesn't match the error thrown back by perl 5.6.1
(the regex itself may be flawed, since '@' !~ /\w/).
If Foo::DBIx is stored in Foo/DBIx.pm, the error isn't thrown.
If the regex is changed to /\@INC\b/, the error also goes away.
It probably shouldn't go away. Is there a file Foo/DBIx.pm somewhere? The
directory Foo/ should be in ./ or in one of the directories mentioned in
@INC.
If you've made sure all the applicable classes already exist, perhaps you
want to use Foo::DBIx-connect() instead.
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Error Message - No suitable target found for package DBD-Oracle
Hello, I'm making progress on trying to install DBD-Oracle. Turns out my previous problem with the repository errors was because I had a \ at the end of the URL spec. Now that I've fixed that, I'm onto the next problem which is the following error I get. ppm search DBD-Oracle Searching in Active Repositories 1. DBD-Oracle [1.06] Oracle database driver for the DBI module 2. DBD-Oracle8 [1.06] Oracle 8 database driver for the DBI module ppm install DBD-Oracle Error: no suitable installation target found for package DBD-Oracle. I'm just wondering how I set a suitable target for this. Janet.
Meaning of error code 1054?
Hi all, I've got a problem that I absolutely can't figure out, and I was hoping somebody here might know what's up. I'm running MySQL on Solaris 8 with perl version 5.6.1, DBI version 1.13, mysql DBD 2.1020, and CGI 2.752. I have a perl script running on a webpage, which takes form data input, and puts it into a database table. This is a straight insert, which works with no problems when the insert is given straight into the database, or through a small test perl program that makes the insert, also with no problems. However, when the program tries to make the insert through the CGI program, it only works some of the time. DBI return an error code 1054, with no additional information in $DBI::errstr. I'm also not seeing any direct errors in the mysql logs, so I assume there's something going on in the DBI somewhere. I've searched the web for some documentation on what error 1054 actually means, but haven't been able to find out what it is. Does anybody know what this error means? -- -- www.grass.org --- Christopher P. Gill --- www.chrisgill.net -- Man will occasionally stumble over the truth, but most of - the time he will pick himself up and continue on Winston Churchill
RE: :ODBC bug
Ronald, I don't know if this is the right place to post DBI/DBD bug reports. If there is a better place please let me know. The small program below results in an error that reads: It's the right place. Exactly! The instruction at 0x1f8fe002 referenced memory at 0x1f8fe002. The memory could not be read. I am running a fairly recent version of activestate Perl under Windows 2000 with the latest versions of DBI and DBD. There's the problem. Normally, FYI, I like to see which driver you are using, but this was a common problem with Foxpro. It wouldn't be limited to foxpro, but that was one of the popular drivers that brought out the error. This has been fixed in later versions of DBD::ODBC, but ActiveState's repository is sadly out of date. The good news is that the 5.8.0 repository (beta) has the latest official release. In the mean time, you can go to the DBI FAQ and read about private repositories and install my latest release. Regards, Jeff = See the DBI FAQ at www.xmlproj.com/cgi/fom.cgi
RE: execute fails in loops only
On Mon, 18 Nov 2002 11:11:47 -0800 (PST), Michael A Chase wrote: I'm not sure about Access, but some underlying databases don't like multiple statements open at the same time on a single database handle. Just for testing, try opening separate database connections for each statement and see if that helps. # For example (untested): $sth_a = $dbh_a - prepare( $sql_a ); $sth_b = $dbh_b - prepare( $sql_b ); If that helps, I would cache the SQL strings, not the statement handles, and use just one $dbh. It shouldn't matter, though, as, from looking at how he's using it, the implicit finish should be called, thus only one statement at a time would be running. Regards, Jeff = See the DBI FAQ at www.xmlproj.com/cgi/fom.cgi
RE: execute fails in loops only
Paul,
I would like to know a few things:
1) What version of DBD::ODBC are you using?
2) What version of Access are you using? What version of the ODBC driver
for Access are you using? (see the ODBC control panel applet for this)
3) Does it always stop at the same point?
4) Do you see if memory consumption is going up through the loop?
5) Can you try to re-prepare the query inside the loop to see if that
resolves it?
Please post (at least to me) a *complete* trace (including the statement
prepare calls). I know it may be big but, it's worth it. Let's start at
level two, to get a feel for what's going on.
Regards,
Jeff
Hi all,
I have a strange problem. I have a script that runs a series of queries
writes the results to files. The core part of this script looks like:
for (my $i = 1; $i 7; $i++) {
process(635, 'T', $i);
process(635, 'C', $i);
process(635, 'A', $i);
process(532, 'T', $i);
process(532, 'C', $i);
process(532, 'A', $i);
}
Now, that sub function, process($$$) checks the input parameters and
chooses the correct statement-handle based on that. For instance:
if ($_[0] == 532) {
if ($_[1] eq 'A') {
$sth = $sth_crosstab_all;
}
else {
$sth = $sth_crosstab;
}
}
The sub then goes ahead and does gets the data from $sth.
I should mention here that the queries being run are all crosstab queries
(MS-Access queries that aggregate data by two variables, much like a pivot
table in Excel).
The strange part is this:
- all queries work individually
- if I set the program running, it works for one full loop and then dies
midway through the second loop
As a result, to accomplish my for loop 6 times, I have to run the program
six different times!
The error message is:
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver]
Operation is not supported for this type of object. (SQL-S1000)(DBD:
st_execute/SQLExecute err=-1) at get_all_mm_data.pl line 204.
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver]
Operation is not supported for this type of object. (SQL-S1000)(DBD:
st_execute/SQLExecute err=-1) at get_all_mm_data.pl line 204.
And to further add to my confusion, lines 204-206 are:
if ($_[1] eq 'T') {
$sth-execute($_[2], 0);
}
And the error is coming on line 204 -- at the start of the if-statement.
Finally, let me repeat -- each query works perfectly individually.
Further, if the entire program will successfully run through one complete
iteration of the for {} loop each time, before crashing during the second
iteration.
Any suggestions, ideas, or guidance on this would be extremely welcome!
One confused programmer,
Paul
