Re: DBD::Oracle and bulk operations
Thank you, this is exactly what I was looking for.
best wishes
hannes
On 06/02/2010 02:31 PM, John Scoles wrote:
Johannes Gritsch wrote:
DBI does support bulk operations and what you are asking about is the
array interface.
It does it though the 'execute_array'
http://search.cpan.org/~timb/DBI-1.611/DBI.pm#execute_array method.
and it is available to all DBD drivers.
DBD::Oracle is special as it implements its own version of it that takes
advantage of Oracle's array interface. It has been in there since 1.18.
here is an example of its use
my @in_values=('1',2,'3','4',5,'6',7,'8',9,'10');
my @status;
my $sth = $dbh->prepare(qq( INSERT INTO foo (id, bar) VALUES
(foo_id_seq.nextval, ?)));
$sth->bind_param_array(1,\...@in_values);
$sth->execute_array({ArrayTupleStatus=>\...@status});
I have never done a full speed test but with 1.17 it took about 25min to
insert 2 million records using the same code and 1.18 it took only about
6 min.
hope this helps
cheers
John
Hi!
Since I came into this topic while writing a script: What does
DBD::Oracle know about bulk operations?
In PL/SQL you can speed up your cursor loops by using bulk fetches
and/or bulk updates/inserts. Instead of operating on single rows you
use arrays of rows. Since DBI does not support it directly (from what
I know), it could be used when doing select_all_xxx calls. Sounds
simple, and, yes, I know, it is not as easy as it sounds. But I think
it would speed up this calls. PL/SQL gets a boost by 5 to 10,
depending on hardware, OS and other things.
When you search for DBD::Oracle and bulk, google only comes up with
rather old documents dating back to 2004 and 2003. In one of those
(from this mailinglist) I found:
"It is not part of DBD::Oracle yet, though there are plans to include
it in a future version."
I'm a regular reader of this mailing list and I wonder that this topic
did not come up again in recent years - or I just missed it ...
cu
hannes
DBD::Oracle and bulk operations
Hi! Since I came into this topic while writing a script: What does DBD::Oracle know about bulk operations? In PL/SQL you can speed up your cursor loops by using bulk fetches and/or bulk updates/inserts. Instead of operating on single rows you use arrays of rows. Since DBI does not support it directly (from what I know), it could be used when doing select_all_xxx calls. Sounds simple, and, yes, I know, it is not as easy as it sounds. But I think it would speed up this calls. PL/SQL gets a boost by 5 to 10, depending on hardware, OS and other things. When you search for DBD::Oracle and bulk, google only comes up with rather old documents dating back to 2004 and 2003. In one of those (from this mailinglist) I found: "It is not part of DBD::Oracle yet, though there are plans to include it in a future version." I'm a regular reader of this mailing list and I wonder that this topic did not come up again in recent years - or I just missed it ... cu hannes -- Johannes Gritsch Unix consultant and Oracle trainer Vienna, Austria
Re: an explain plan for Oracle queries with placeholders
Try using :val instead of ?
Oracle does not understand that notation.
HTH
Hannes
E R wrote:
> Perhaps I should have divulged more of what I am already trying.
>
> I am getting this error:
>
> ORA-01036: illegal variable name/number (DBD ERROR: OCIBindByName)
> [for Statement "EXPLAIN PLAN SET STATEMENT_ID = '21623' FOR SELECT *
> FROM TRANSACTION_HISTORY WHERE ACCOUNT_ID = ?" with ParamValues:
> :p1='v8799']
>
> when I run this code:
>
> my $prefix = "EXPLAIN PLAN SET STATEMENT_ID = '$$' FOR ";
> my $sql = "SELECT * FROM TRANSACTION_HISTORY WHERE ACCOUNT_ID = ?";
> my @params = qw(v8799);
> ...
> my $esth = $dbh->prepare("$prefix$sql");
> unless ($esth) {
> die "prepare of EXPLAIN failed";
> }
>
> unless ($esth->execute(@params)) {
> die "execute of EXPLAIN failed";
> }
>
> On Mon, Apr 20, 2009 at 2:42 PM, Nelson, Erick [HDS]
> wrote:
>> Instead of a ? as a place holder, try using numeric place holders (eg.
>> :1, :2...etc)
>> Example:
>> Select * from sfile where id = :1
>>
>> -Original Message-
>> From: E R [mailto:pc88m...@gmail.com]
>> Sent: Monday, April 20, 2009 12:25 PM
>> To: dbi-users@perl.org
>> Subject: an explain plan for Oracle queries with placeholders
>>
>> I'd like to perform an EXPLAIN PLAN on a query that has question mark
>> placeholders.
>>
>> I've seen perl scripts which perform EXPLAIN PLAN commands, but the
>> queries they operate on don't have placeholders.
>>
>> Any pointers on how I would go about doing this?
>>
>> Thanks,
>> ER
>>
--
--
Johannes Gritsch
_
GNC Akademie GmbH
Nussdorfer Laende 23
1190 Wien
Austria - Europe
emailjohannes.grit...@gnc.at
web http://www.gnc.at
# Phone 0-810-820-462 zum Ortstarif innerhalb Österreichs
# Phone +43-1-3709787 from all countries
# Fax 0-810-820-GNC-99 zum Ortstarif innerhalb Österreichs
# Fax +43-1-3709787-99 from all countries
_
company details http://www.gnc.at/gnc3
registered office Austria, 1190 Vienna, Nussdorfer Laende 23
registration number 222339w
vat registration number ATU56000204
court of commercial registration Handelsgericht Wien
legal form Gesellschaft mit beschraenkter Haftung (Ltd)
Re: How to iterate through database tables
I would say if a request like this should be solved without using Perl variables the following applies: - the data model is bad - creating views is a bad idea since you would do DDL on a production system. That in return would deteriorate the execution/parse time quotient of the database instance. - using a PL/SQL routine as Scott suggests seems to me the best solution, though dynamic SQL might also become a tuning nightmare Scott Smith wrote: > I think his issue is getting list to the server side without > concatenating strings. > > You can't use bind variables as table names or column names. > > You may be able to write a stored procedure which takes a table name as > a variable, does the string concatenation on the server side, and uses > Oracle's "execute immediate" to effectively "eval" the SQL there.. > > Scott > > Dale wrote: >> On Tue, Feb 3, 2009 at 8:47 AM, Deviloper wrote: >> >> >>> Hello there! >>> >>> lets assume that one has a list of tables @db_tables. (For Example one per >>> Month) >>> Lets assume one wants to find some data in the tables. (For Example sold >>> Items.) >>> >>> select sales from $db_tables[0]; # gives all sold items in January >>> >>> >> You could use a UNION in the database to combine the common data (you can >> create a view and use that for your select): >> >> CREATE VIEW v_combined_months AS >> SELECT 'January' AS "Month",sales FROM jan_sales >> UNION ALL >> SELECT 'February' AS "Month",sales FROM feb_sales >> UNION ALL >> SELECT 'March' AS "Month",sales FROM march_sales >> [...] >> >> > -- -- Johannes Gritsch Oracle DBA and Perl afficionado Vienna, Austria
Re: Returning null from query
BCFD36 wrote: This should be simple. Either it can't be done, I'm using the wrong syntax, or using the wrong approach. I'm running a perl script that queries the db, and gets back an answer. That works just fine. However, on occasion one of the returned values is null since it is null in the data base. I tried sending a "set null null" the same way I sent the select command, but it generated an error:"missing or invalid option. Were I directly in sqlplus, I could say "set null null" and a string with "null" in it would be returned. Any suggestions? D. Scruggs Lockhkeed Martin, Sunnyvale Ca. The command "SET" is a builtin command of sqlplus, not a valid SQL command. Thus the database does not understand it. You eiher could use the SQL-function NVL to circumvent NULLs or check within Perl with 'defined'. HTH Hannes -- Johannes Gritsch www.linuxification.at
Re: DBD::Oracle Install Fails on Gentoo Linux
tom r schrieb: Installing DBD::Oracle on Gentoo Linux fails with the following error: x86_64-pc-linux-gnu-gcc: unrecognized option '-wchar-stdc++' x86_64-pc-linux-gnu-gcc: unrecognized option '-cxxlib-gcc' cc1: error: /ee/dev/bastring.h: No such file or directory I tracked this down to the Oracle XE install problem. Makefile.PL is reading $OH/rdbms/demo/demo_xe.mk which has these paragraphs at the end: ifdef BUILD_CCC296 CC=/usr/bin/g++296 CCFLAGS = -include /ee/dev/bastring.h $(CCINCLUDES) -wchar-stdc++ -DLINUX -D_GNU_SOURCE -D_REENTRANT -g endif ifdef BUILD_ICC COMPDIR=/usr/local/remote/packages/icc_remote/8.1-022 CC=$(COMPDIR)/bin/icpc CCFLAGS += -DOCCI_NO_WSTRING=1 -cxxlib-gcc endif This creates a CCFLAGS value of '-include /ee/dev/bastring.h -I../ -wchar-stdc++ -DLINUX -D_GNU_SOURCE -D_REENTRANT -g -DOCCI_NO_WSTRING=1 -cxxlib-gcc'; where '-wchar-stdc++' and '-cxxlib-gcc' are, apparently invalid options to gcc and furthermore /ee/dev/bastring.h doesn't exist. By commenting out these last two paragraphs Makefile.PL builds CCFLAGS with a value of '-I../ -DLINUX -D_GNU_SOURCE -D_REENTRANT -g' which gcc likes much better. This modification to demo_xe.mk allows DBD::Oracle to install and work properly. I don't know what BUILD_CCC296 or BUILD_ICC mean but I suppose it's a Solaris thing that doesn't work under Linux. Just a guess. In any event it would be nice if Makefile.PL took this into account. tom No, I don´t think so. Looking at the names of those variable I would assume BUILD_CCC296 to be options for GCC V2.96 (rather old, but used by Oracle V9) and BUILD_ICC to be options for the non-free Intel C-compiler for Linux (didn´t expect anybody to care about that one). Since you are using Oracle XE (definitely 10g) the first option should not be active. Hannes
Re: Regarding Perl DBI version issue.
Obviously root user gets another PATH variable than a normal user. From your output I would assume the correct perl version to use is somewhere under /usr/local (most probably /usr/local/bin). Rearrange PATH for root before calling perl should do the trick. If in doubt, do echo $PATH as normal user and set the result for root before calling perl. OTOH, why do you you have to use *ROOT* for *ORACLE* things? This is looks a little bit like a design flaw ... HTH Hannes RaviChandra Chelikam schrieb: HI When I am using the command from root user in the sunsolaris operating system. i.e perl -e 'use DBI; print $DBI::VERSION,"\n";' I am getting the following error. Can't locate DBI.pm in @INC (@INC contains: /usr/perl5/5.00503/sun4-solaris /usr/perl5/5.00503 /usr/perl5/site_perl/5.005/sun4-solaris /usr/perl5/site_perl/5.005 .) Actually we are using perl 5.6.1,but it is showing location to some other perl if we log in with root user only. If we login with some other user other than the root user,then it showing correct path i.e /usr/local/lib/perl5/site_perl/5.6.1/sun4-solaris/auto/DBI/ and getting the correct version. And when I use the command with user other than the root user, I am getting the correct DBI version For eg: perl -e 'use DBI; print $DBI::VERSION,"\n";' Output is 1.28 Could u plz help what to do for the root user, inorder to get the correct DBI version with out the error. What kind of changes should I do? Thanks & Regards Ravi Disclaimer: This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindra policy statement, you may review at http://www.techmahindra.com/Disclaimer.html";>http://www.techmahindra.com/Disclaimer.html externally and http://tim.techmahindra.com/Disclaimer.html";>http://tim.techmahindra.com/Disclaimer.html internally within Tech Mahindra.
AW: Problems building DBD-Oracle-1.16 on HP
Looks as if gcc gets some parameters it does not understand. Is +b a parameter for the native C-Compiler for HPUX? This would mean that somewhere the system still tries to use HPUX cc and not gcc. Maybe your path variable leads to this (wrong) conclusion. It still contains /opt/ansic/bin and /usr/ccs/bin before /usr/contrib/bin (the path where gcc usually resides). Just a blind guess. Hannes - Originalnachricht - Von: "Capacio, Paula J" Gesendet: Mon, 5.3.2007 20:56 An: dbi-users@perl.org Betreff: Problems building DBD-Oracle-1.16 on HP Hello, I am attempting to build DBD-Oracle-1.16 on HP running perl 5.8.0. The server has Oracle 10g installed and I can successfully connect to a remote database using SQLPlus. I have tried all the various incantations listed in the READMEs (perl Makefile.PL -p, perl Makefile.PL -nob, and perl Makefile.PL -l) without success. Below you'll find the output of perl -V, the environment variables I set, the perl Makefile.PL output and the failed make. The LD_RUN_PATH built as part of Makefile.PL and used by 'make' looks weird to me because it contains the same path twice. LD_RUN_PATH=/usr/oracle/rdbms/10.2/lib32:/usr/oracle/rdbms/10.2/rdbms/li b32 Although one of the switch incantations (can't remember which) resulted in just LD_RUN_PATH=/usr/oracle/rdbms/10.2/lib32 but the make still failed with "No such file or directory" BTW, I realize that the current versions on CPAN are: DBI-1.54 and DBD-Oracle-1.19. However for consistency sake since they are not much older, I am trying to use DBI-1.50 and DBD-Oracle1.16 because they are the same version as we have installed on Linux. Any assistance would be greatly appreciated. Paula --- OUTPUT perl -V --- [/tmp/perlDBx] # perl -V Summary of my perl5 (revision 5.0 version 8 subversion 0) configuration: Platform: osname=hpux, osvers=11.00, archname=PA-RISC1.1-thread-multi uname='hp-ux bertha b.11.00 u 9000800 136901587 unlimited-user license ' config_args='-des -Dcc=gcc -Dcf_by=ActiveState [EMAIL PROTECTED] tate.com -Uinstallusrbinperl -Ud_sigsetjmp -Dusethreads -Duseithreads -Ulocincpt h= -Uloclibpth= -Accflags=-fPIC -Dd_attribut=undef -Dcccdlflags=-fPIC -Darchname =PA-RISC1.1 -Duselargefiles -Accflags=-mpa-risc-1-1 -Dprefix=/opt/perl -Duselarg efiles' hint=recommended, useposix=true, d_sigaction=define usethreads=define use5005threads=undef useithreads=define usemultiplicity=de fine useperlio=define d_sfio=undef uselargefiles=define usesocks=undef use64bitint=undef use64bitall=undef uselongdouble=undef usemymalloc=n, bincompat5005=undef Compiler: cc='gcc', ccflags ='-D_POSIX_C_SOURCE=199506L -D_REENTRANT -D_HPUX_SOURCE -f PIC -mpa-risc-1-1 -fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=6 4', optimize='-O', cppflags='-D_HPUX_SOURCE -D_POSIX_C_SOURCE=199506L -D_REENTRANT -D_HPUX_SOUR CE -fPIC -mpa-risc-1-1 -fno-strict-aliasing' ccversion='', gccversion='3.2 20020708 (experimental)', gccosandvers='hpux11 .00' intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=4321 d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=16 ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='off_t', lseeksize =8 alignbytes=8, prototype=define Linker and Libraries: ld='gcc', ldflags ='' libpth=/lib /usr/lib /usr/ccs/lib /usr/local/lib libs=-lnsl -lnm -lndbm -lmalloc -ldld -lm -lndir -lcrypt -lsec -lpthread perllibs=-lnsl -lnm -lmalloc -ldld -lm -lndir -lcrypt -lsec -lpthread libc=/lib/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,deferr ed ' cccdlflags='-fPIC', lddlflags='-shared -static-libgcc -fPIC' Characteristics of this binary (from libperl): Compile-time options: MULTIPLICITY USE_ITHREADS USE_LARGE_FILES PERL_IMPLICIT_ CONTEXT Locally applied patches: ActivePerl Build 806 Built under hpux Compiled at May 2 2003 21:44:05 [EMAIL PROTECTED]: /opt/perl/lib/5.8.0/PA-RISC1.1-thread-multi /opt/perl/lib/5.8.0 /opt/perl/lib/site_perl/5.8.0/PA-RISC1.1-thread-multi /opt/perl/lib/site_perl/5.8.0 /opt/perl/lib/site_perl . -- ENVIRONMENT VARIABLES: -- ORACLE_BASE=/usr/oracle ORACLE_HOME=/usr/oracle/rdbms/10.2 TNS_ADMIN=/etc TWO_TASK=mp9u LD_LIBRARY_PATH=/usr/oracle/rdbms/10.2/lib LD_RUN_PATH= ORACLE_USERID=scott/tiger PATH=/usr/oracle/rdbms/10.2/bin:/usr/local/bin:/usr/sbin:/usr/bin:/opt/a nsic/bin :/usr/ccs/bin:/usr/contrib/bin:/opt/hparray/bin:/opt/nettladm/bin:/opt/u pgrade/b in:/opt/fcms/bin:/opt/resmon/bin:/opt/pd/bin:/opt/perf/bin:/usr/contrib/ bin/X11: /usr/bin/X11:/usr/sbin/diag/contrib:/opt/prm/bin:/opt/wlm/bin:/opt/aCC/b in:/opt/ gnome/bin:/opt/mozilla:/opt/wbem/bin:/opt/wbem/sbin:/opt/mx/bin:/opt/per l/bin:/o pt/langtools/bin:/opt/ignite/bin:/opt/graphics/common/
