RE: Error - DBD::Oracle / ORA-1008 problem with placeholders
Hello
Finally the bug I had was due to Oracle.
The patch Oracle that fix this bug is 5863277
- Desc: ORA-1008 THROWN ON SQL ON SECOND RUN WHEN CURSOR_SHARING=SIMILAR/FORCE
- Product RDBMS Server
- Release 10.2.0.3
Note : the first patch 5254759 that I mentioned in my previous mail and that we
believed could fix the issue did not work.
Hope that helps.
Thank you for your support.
Pascal
-Message d'origine-
De : John Scoles [mailto:[EMAIL PROTECTED]
Envoyé : mercredi 30 avril 2008 12:55
À : MONSCHEIN,PASCAL
Cc : dbi-users@perl.org
Objet : Re: Error - DBD::Oracle / ORA-1008 problem with placeholders
Thanks Pascal. I wanted the Table deff so I could exactly mimic your situation.
I managed to get your code to work on my 10XE DB but I will give this new Table
a shot and see what happens on my 10 linux box later to day or tomorrow
Would not surprize me if it is Oracle, It would not be the first time that they
had a bug in their code.
For now I might add this to the Readme
cheers
John Scoles .
[EMAIL PROTECTED] wrote:
Hello John,
Please find the table definition :
create table owner1.table1 (
col0CHAR(8) not null,
col1CHAR(5) not null,
col2CHAR(2) not null,
col3CHAR(3) not null,
col4NUMBER(5,2) not null,
col5CHAR(1),
col6NUMBER(18,3),
col7CHAR(1),
col8NUMBER(18,3),
col9DATE default SYSDATE not null
)
System information :
Unix / SUN or AIX (the case is not depending on OS, I can reproduce it in
all Unix)
Oracle 10gR2 PatchSet 2 (10.2.0.3.0) with 2 patches installed
5556081 and 5557962
The SQL is correct syntaxically and works fine when I execute it in sqlplus.
The only question I had is about col0 that is containing data like this
format dd/mm/yy but is a string. But ...
I analyzed this issue again and have news about it :
The problem is depend on an Oracle parameter : CURSOR_SHARING.
On our instances this parameter is set sometimes at the default (EXACT) and
sometimes to FORCE.
So the problem is the difference of the behaviour between Oracle 9i and
Oracle 10g.
With Oracle 9i, the value of the parameter could be EXACT or FORCE,
the Perl script works correctly With Oracle 10g, if the value is FORCE, the
Perl script report ORA-1008 error, else if the value is EXACT is works
correctly.
= so I have a workaround, it is to add and alter session after the
DBH-connect.
There is an Oracle patch that should fix Oracle 10g behaviour
5254759 for 10.2.0.3 - ORA-12801/ORA-1008 OCCURS ON A PARALLEL QUERY
WITH BIND VARIABLES But I didn't tested it, yet.
Regards
Pascal
-Message d'origine-
De : John Scoles [mailto:[EMAIL PROTECTED] Envoyé : mardi 29 avril
2008 17:12 À : MONSCHEIN,PASCAL Cc : dbi-users@perl.org Objet : Re:
Error - DBD::Oracle / ORA-1008 problem with placeholders
Can you give me what the table schema is?
I would like to know what datatypes
col1,col2,col3,col4
are so I can try and recreate the error.
As well what operation system are you using and which version of DBI?
anyway just looking at the SQL it doesn't look quite right
cheers
John Scoles
[EMAIL PROTECTED] wrote:
Hello all,
I encouter an error with DBD::Oracle and Oracle instances 10g that I
dont understand :
DBD::Oracle::st execute failed: ORA-01008: not all variables bound
(DBD ERROR: OCIStmtExecute)
This script works fine with Oracle 9i.
I compiled DBD::Oracle (1.19) based on Oracle 9i (on Sun-Solaris10) -
DBI 1.58
I tried to compile DBD::Oracle (1.21) based on Oracle 10g (on
Sun-Solaris10) with the same error.
I tried to use DBI_TRACE=2 or 3 but I dont find the trace usefull.
My tests against Oracle instances :
9i (new install) - ok (it is always ok with 9i)
10g (new install) - bad
10g (migrated from 9i) - bad
My tests against placeholders :
If I used placeholders like :param1, it works the first time,
then it will failed in general
My tests against the SQL query :
If I comment the first column returned (the TO_CHAR(...) AS DT, it
works the first time
then it failed
So the problem seems to be with CACHE or something like that ?
My script is :
#
-
-
--
use DBI;
$dsn=dbi:Oracle:host=my_server;port=1521;sid=my_sid;
my $query =
SELECT
TO_CHAR(TO_DATE(col1,'DD/MM/YY'),'MMDD') AS DT,
col2,col3,col4 FROM table1 WHERE SUBSTR(col1,4) = ?
;
$DBH = DBI-connect($dsn,toto,toto,{PrintError = 0, RaiseError
= 1}); $STH = $DBH - prepare($extraction_query); my
$month_MMAA=03/08; #$STH-bind_param(:pr1, $month_MMAA);
$STH-bind_param(1, $month_MMAA); $STH-execute();
How to execute a perl script at remote server
Hi All,
I have written a Perl script, which deals with the ftp ing
(transferring) the file to remote server and executing a Perl script
(AUT) at remote m/c.
The script is as below and here I have 2 doubts, so please help me to
resolve this.
1) I am getting the message like could not transfer the file to
server but it is ftp ing the flie to remote m/c, and I can see that
file ( root1.txt ) there.
2) And I want to run the perl script (AUT) at remote m/c depending
on the data we sent, so could you please let me know how can I achieve
this.
Is it possible for me to do system(test.pl), If I am at SFTP mode.
Please see comment line in the code below.
#!/usr/bin/perl -w
use strict;
use warnings;
use Net::SFTP;
my $server=A.BY.C.D;
my $user=roserag;
my $password=[EMAIL PROTECTED];
my %args = (user = $user, password = $password, ssh_args = []);
$args{debug} = 1;
$args{user} = root;
my $file=local.txt;
my $rfile = root1.txt;
my $sftp=Net::SFTP-new($server, %args) or die could not open
connection to $server\n;
$sftp-put($file,$rfile) or die could not transfer the file to server
\n;
# system(test.pl);Is this command work out.
exit;
[EMAIL PROTECTED] Examples]$ perl sftp3.pl
testbrix.wipro.com: Reading configuration data /home/gaurav/.ssh/config
testbrix.wipro.com: Reading configuration data /etc/ssh_config
...
...
testbrix.wipro.com: sftp: In write loop, got 510 offset 0
testbrix.wipro.com: sftp: Sent message T:10 I:2
testbrix.wipro.com: sftp: Sent message T:4 I:3
could not transfer the file to server
But I can see the file the file at server.
1,1 All
Regards,
Mustafa
Please do not print this email unless it is absolutely necessary.
The information contained in this electronic message and any attachments to
this message are intended for the exclusive use of the addressee(s) and may
contain proprietary, confidential or privileged information. If you are not the
intended recipient, you should not disseminate, distribute or copy this e-mail.
Please notify the sender immediately and destroy all copies of this message and
any attachments.
WARNING: Computer viruses can be transmitted via email. The recipient should
check this email and any attachments for the presence of viruses. The company
accepts no liability for any damage caused by any virus transmitted by this
email.
www.wipro.com
install_driver(Oracle) failed: wrong ELF class: DynaLoader.pm
Hello all, i wrote a skript which works fine for several Oracle Versions on several platforms (linux, solarias 8/9). if i run that script ineractively on solaris 10 it also works fine. if i run it as a cron job i get: install_driver(Oracle) failed: Can't load '/usr/perl5/site_perl/5.8.4/sun4-solaris-64int/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: ld.so.1: perl: fatal: /oracle/product/10.2.0/lib/libclntsh.so.10.1: wrong ELF class: ELFCLASS64 at /usr/perl5/5.8.4/lib/sun4-solaris-64int/DynaLoader.pm line 230. at (eval 8) line 3 Compilation failed in require at (eval 8) line 3. Perhaps a required shared library or dll isn't installed where expected Oracle 10.2.0.3 DBI Version: 1.601 DBD::Oracle Version: 1.20 Environment see bbelow. any hints? Thanx, Christian - most of the environmet variables are set in my skript: ~~ @INC: /oracle/dba/backup/ora19 /usr/perl5/5.8.4/lib/sun4-solaris-64int /usr/perl5/5.8.4/lib /usr/perl5/site_perl/5.8.4/sun4-solaris-64int /usr/perl5/site_perl/5.8.4 /usr/perl5/site_perl /usr/perl5/vendor_perl/5.8.4/sun4-solaris-64int /usr/perl5/vendor_perl/5.8.4 /usr/perl5/vendor_perl -- ~~ %ENV: CLASSPATH - :/oracle/product/10.2.0/classes/oracle:/oracle/product/10.2.0/jlib:/oracle/product/10.2.0/lib:/oracle/product/10.2.0/classes/oracle:/oracle/product/10.2.0/jlib:/oracle/product/10.2.0/lib HOME - /export/home/oracle LD_LIBRARY_PATH - /oracle/product/10.2.0/lib:/oracle/product/10.2.0/lib32 LOGNAME - oracle NLS_LANG - AMERICAN_AMERICA.WE8DEC ORACLE_HOME - /oracle/product/10.2.0 ORACLE_SID - ora19 ORA_NLS10 - /oracle/product/10.2.0/nls/data PATH - /oracle/product/10.2.0/bin:/usr/ccs/bin:/usr/bin:/bin SHELL - /usr/bin/sh TZ - MET - $ perl -V Summary of my perl5 (revision 5 version 8 subversion 4) configuration: Platform: osname=solaris, osvers=2.10, archname=sun4-solaris-64int uname='sunos localhost 5.10 sun4u sparc SUNW,Ultra-2' config_args='' 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=undef uselongdouble=undef usemymalloc=n, bincompat5005=undef Compiler: cc='cc', ccflags ='-D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -xarch=v8 -D_TS_ERRNO', optimize='-xO3 -xspace -xildoff', cppflags='' ccversion='Sun WorkShop', gccversion='', gccosandvers='' intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=87654321 d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=16 ivtype='long long', ivsize=8, nvtype='double', nvsize=8, Off_t='off_t', lseeksize=8 alignbytes=8, prototype=define Linker and Libraries: ld='cc', ldflags ='' libpth=/lib /usr/lib /usr/ccs/lib libs=-lsocket -lnsl -ldl -lm -lc perllibs=-lsocket -lnsl -ldl -lm -lc libc=/lib/libc.so, so=so, useshrplib=true, libperl=libperl.so gnulibc_version='' Dynamic Linking: dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags='-R /usr/perl5/5.8.4/lib/sun4-solaris-64int/CORE' cccdlflags='-KPIC', lddlflags='-G' Characteristics of this binary (from libperl): Compile-time options: USE_64_BIT_INT USE_LARGE_FILES Locally applied patches: 22667 The optree builder was looping when constructing the ops ... 22715 Upgrade to FileCache 1.04 22733 Missing copyright in the README. 22746 fix a coredump caused by rv2gv not fully converting a PV ... 22755 Fix 29149 - another UTF8 cache bug hit by substr. 22774 [perl #28938] split could leave an array without ... 22775 [perl #29127] scalar delete of empty slice returned garbage 22776 [perl #28986] perl -e open m crashes Perl 22777 add test for change #22776 (open m crashes Perl) 22778 add test for change #22746 ([perl #29102] Crash on assign ... 22781 [perl #29340] Bizarre copy of ARRAY make sure a pad op's ... 22796 [perl #29346] Double warning for int(undef) and abs(undef) ... 22818 BOM-marked and (BOMless) UTF-16 scripts not working 22823 [perl #29581] glob() misses a lot of matches 22827 Smoke [5.9.2] 22818 FAIL(F) MSWin32 WinXP/.Net SP1 (x86/1 cpu) 22830 [perl #29637] Thread creation time is hypersensitive 22831 improve hashing algorithm for ptr tables in perl_clone: ... 22839 [perl #29790] Optimization busted: '@a = b, sort @a' ... 22850 [PATCH] 'perl -v' fails if local_patches contains code snippets 22852 TEST needs to ignore SCM files 22886 Pod::Find should ignore SCM files and dirs 22888 Remove redundant %SIG assignments from FileCache 23006 [perl #30509] use encoding and eq cause memory leak 23074 Segfault using HTML::Entities 23106 Numeric comparison operators mustn't compare addresses of ... 23320 [perl #30066] Memory leak in nested
RE: How to execute a perl script at remote server
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2008 8:04 AM To: dbi-users@perl.org Subject: How to execute a perl script at remote server Hi All, You would be better served to search the Perlmonks list http://perlmonks.org/ They have all kinds of slick tricks for this kind of stuff. -- Stephen Carville [EMAIL PROTECTED] Systems Engineer Land America 1.626.667.1450 X1326 # That which does not kill us often hurts us a lot.
Can't locate DBI.pm
DISCLAIMER: --- The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any mail and attachments please check them for viruses and defect. ---
Re: Can't locate DBI.pm
On Wed, May 28, 2008 at 8:37 AM, Singaravelan S (HCL Financial Services) [EMAIL PROTECTED] wrote: DISCLAIMER: --- The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. Well, this is no help in the description. The error implied by the subject line means you have not installed DBI - or have not installed it where you can pick it up at runtime. Since you've given no other information, you can't get any more help. -- Jonathan Leffler [EMAIL PROTECTED] #include disclaimer.h Guardian of DBD::Informix - v2008.0229 - http://dbi.perl.org Blessed are we who can laugh at ourselves, for we shall never cease to be amused.
Re: install_driver(Oracle) failed: wrong ELF class: DynaLoader.pm
On Wed, May 28, 2008 at 8:30 AM, Christian Merz [EMAIL PROTECTED] wrote: i wrote a skript which works fine for several Oracle Versions on several platforms (linux, solarias 8/9). if i run that script ineractively on solaris 10 it also works fine. if i run it as a cron job i get: cron does not set the environment, so you must do so for it. install_driver(Oracle) failed: Can't load '/usr/perl5/site_perl/5.8.4/sun4-solaris-64int/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: ld.so.1: perl: fatal: /oracle/product/10.2.0/lib/libclntsh.so.10.1: wrong ELF class: ELFCLASS64 at /usr/perl5/5.8.4/lib/sun4-solaris-64int/DynaLoader.pm line 230. at (eval 8) line 3 Compilation failed in require at (eval 8) line 3. Perhaps a required shared library or dll isn't installed where expected 32-bit Client library - 64-bit Perl - won't work. Install 32-bit Perl or 64-bit client libraries. Or just set the environment. -- Jonathan Leffler [EMAIL PROTECTED] #include disclaimer.h Guardian of DBD::Informix - v2008.0229 - http://dbi.perl.org Blessed are we who can laugh at ourselves, for we shall never cease to be amused.
Re: install_driver(Oracle) failed: wrong ELF class: DynaLoader.pm
Hello! On Wed, 28 May 2008, Christian Merz wrote: module DBD::Oracle: ld.so.1: perl: fatal: /oracle/product/10.2.0/lib/libclntsh.so.10.1: wrong ELF class: ELFCLASS64 at ... LD_LIBRARY_PATH - /oracle/product/10.2.0/lib:/oracle/product/10.2.0/lib32 Change LD_LIBRARY_PATH to: LD_LIBRARY_PATH=/oracle/product/10.2.0/lib32:/oracle/product/10.2.0/lib
