Karun Dutt and I managed to get DBD::Oracle 1.21 to install on a 64-bit Linux OS against the Oracle 11 full client. Here’s what we did.
As root, we downloaded DBD::Oracle from CPAN.
cpan> get DBD::Oracle
…
[/perl]
We replaced the distribution makefile with: https://svn.perl.org/modules/dbd-oracle/trunk/Makefile.PL (this is the latest Makefile.PL).
[bash light=”true”] # cd /root/.cpan/build/DBD-Oracle-1.21# export ORACLE_HOME=<actual value of Oracle Home>
# export ORACLE_SID=<actual value of ORACLE_SID>
# export ORACLE_USERID=<a working ORACLE_USERID>
# export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME
# perl Makefile.PL
…
# make
…
# make test
…
# make install
…
[/bash]
It works!
17 Comments. Leave new
Hi,
Does the latest DBD::Oracle have features like batching and Oracle types support features similar to the jdbc thin driver provided by oracle.
thanks and Regards
not sure what exactly you mean by batching and Oracle type support.
Do you have any examples?
Thanks for your response.
1) I am referring to the facility provided by oracle jdbc thin driver to batch the insert/update operations so that roundtrips to the server is reduced.
eg: One could set the batch parameter on the connection or on a specific statement object/handle to a value say 10. Then irrespective of the # of calls to execute a update/insert on the statement handle, every 10 update/insert requests get batched and sent to DB server to execute. This reduces the round trips to server which normally happens for every insert/update that gets sent to server to execute each time on execute call.
I am not aware how this works in DBD::Oracle.
2) How can one retrieve pl/sql Table type or bind such things with DBD::Oracle.
JPublisher provided by oracle maps between oracle types and jdbc types easily.
Thanks and regards
For #1 I think you are refering to the OCI Array_interface and yes DBD::Oracle supports it (since 1.18)
check out this link
https://search.cpan.org/~timb/DBI-1.604/DBI.pm#bind_param_array
As for #2 I think we support is as well check out these links
https://search.cpan.org/~pythian/DBD-Oracle-1.21/Oracle.pm#Returning_A_Recordset
https://search.cpan.org/~pythian/DBD-Oracle-1.21/Oracle.pm#Binding_Cursors
Thanks John for your responses.
1) I had a look at the bind_param_array and execute_array interfaces added from 1.18 of DBD::Oracle.
Though it helps in array binding and helps it is not equivalent to the batching facility in java driver.With jdbc one can transparently batch inserts and updates to be sent to rdbms server in one shot. inserts and updates are done normally but driver internally batches them together like one unit of work.
Anyway i am happy that the array interface helps in binding array of values for inserts from 1.18.
Thank you very much for patching Makefile.PL!
It’s very easy now to install DBD::Oracle with 11g.
Laks Said
‘Though it helps in array binding and helps it is not equivalent to the batching facility in java driver.’
Hi Laks I was curious about this so I had a look under the hood of the JDBC interface and which calls it was using in OCI to accomplish the task.
Well to my surprise both JDBC and DBD::Oracle use almost the exactly the same code under the hood so I think it is a case of ‘We do it this way any you do it the wrong way’:);).
In JDBC you
1) prepare a statement (con.prepareStatement)
2) bind values to the statement (stmt.setString(2, “bla”))
3) add the statement to a batch (stmt.addBatch())
4) repeat 2-3 above and then
5) execute your batch. (stmt.executeBatch())
in DBD:Oracle
1) prepare the statement ($dbh->prepare)
2) bind all the values to the statement ($sth->bind_param_array)
3) execute the statement. ($sth->execute_array)
In both JDBC and DBD::Oracle only 1 round trip call to the execute is made in OCI.
So me thinks it is a case here of ‘You say ‘potato’ and I say ‘potato’.
cheers
John,
I think Laks is saying ‘potato’ and you’re saying “pineapple”.
The array interface is only for batching re-use of one handle for multiple uses, saving round trips. The batch interface lets you batch multiple statements, including different ones, into one database call.
No?
Paul
Actully ‘No’ is correct.
The JDBC interface does let you ‘batch’ multiple statments with one execute but this is just hiding the fact that one execute is fired for ecach of them.
From the JDBC docs
“The batch update facility is used with a PreparedStatement to associate multiple sets of input parameter values with a single PreparedStatement object.”
so this
PreparedStatement stmt = con.prepareStatement(
“INSERT INTO employees VALUES (?, ?)”);
stmt.setInt(1, 2000);
stmt.setString(2, “Kelly Kaufmann”);
stmt.addBatch();
stmt.setInt(1, 3000);
stmt.setString(2, “Bill Barnes”);
stmt.addBatch();
// submit the batch for execution
int[] updateCounts = stmt.executeBatch();
will do 1 OCIExecute .
but this
Statement stmt = con.createStatement();
stmt.addBatch(“INSERT INTO employees VALUES (1000, ?,?)”);
stmt.setInt(1, 2000);
stmt.setString(2, “Kelly Kaufmann”);
stmt.addBatch(“INSERT INTO departments VALUES (?, ?’)”);
stmt.setInt(1, 260);
stmt.setString(2, “Shoe”);
stmt.addBatch(“INSERT INTO emp_dept VALUES (?, ?)”);
stmt.setInt(1, 1000);
stmt.setInt(2, 260);
int[] updateCounts = stmt.executeBatch();
will do 3 OCIExecute command one for each SQL. So in this case there are 3 round trips to the server rather than just one for the last example.
Excellent info, John, thanks.
Paul
alex, the link to the revised Makefile.PL appears not to work. Is there an updated link available or can you upload the Makefile.PL somewhere here.
Thanks
After at least 20 hours of working on this (over two weeks), DBD Oracle installed now on AIX 5.3 + 11g Client + DBD::Oracle 1.19 + DBI 1.609.
The key problem was that 11g Client does not have the .mk files that the perl modules usually look for. I’m also curious if perl or Oracle 11g Client is in 32-bit, and if that has any negative impact.
See below for more details. Keep in mind, I’m not a perl developer, I only try to install the modules for our app. So, much of my explanation below is just from deduction. If someone has better input, please feel free to add.
– – –
Key changes:
• DBD::Oracle 1.19
• Use 11g Client’s .mk file demo_rdbms32.mk
What’s the story?
The DBD Modules have three key pieces
A) List of Oracle .mk files to look for (defined in Makefile.PL > mk_oci)
B) New features
C) Battery of tests
DBD::Oracle versions 1.19, 1.21, and 1.23 look for files a certain set of Oracle files (demo_xe.mk, oracle.mk, demo_rdbms.mk, ins_rdbms.mk,etc.). However, Oracle 11g Client install apparently does not have these files. The special Makefile.PL has some extra logic. If perl is 64-bit, then look for those usual set of files; otherwise, look for a single file, demo_rdbms32.mk. And the 11g Client has this file. You can specify this file as an argument, and that solves the problem too.
Why not 1.21? The “make test” only passes 96% of the tests
Why not 1.23? The “make test” only passes 6% of the tests
DBD 1.19 passes all the tests.
Steps
export ORACLE_HOME=
export ORACLE_SID=
export ORACLE_USERID=user/[email protected]$ORACLE_SID
export PERLLIB=/usr/local/lib
export LIBPATH=$ORACLE_HOME/lib32
perl Makefile.PL -m $ORACLE_HOME/rdbms/demo/demo_rdbms32.mk
make
make test
Good Demonstration , I was getting error in finding the LIB and it got resolved by setting PERLLIB environment variable .
By the way, here were the errors I was getting prior to specifying the demo_rdbms32.mk file.
xlc: 1501-228 (W) input file /lib32/crt0_64.o not found
make: 1254-004 The error code from the last command is 252.
Stop.
AIX is always a bugger with anything to do with Perl and C libs
See this Blog entry for a similar story
https://www.pythian.com/news/6087/dbdoracle-on-aix-5-1/
sounds the same as yours
God bless you! I downloaded DBD::Oracle, copied in your Makefile.PL, and it worked. Thank you, thank you, thank you!
Very nice work. FYI – your procedure seems to work on later versions of DBD::Oracle s well (at least on perl 5.8.8/64 bit RHEL5 with Oracle 11G it did)
-M