Install DBD::Oracle on 64-bit Linux and Oracle 11g

May 13, 2008 / By John Scoles

Tags:

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.

# perl -MCPAN -eshell
cpan> get DBD::Oracle
...

We replaced the distribution makefile with: http://svn.perl.org/modules/dbd-oracle/trunk/Makefile.PL (this is the latest Makefile.PL).

# 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
...

It works!

16 Responses to “Install DBD::Oracle on 64-bit Linux and Oracle 11g”

  • Laks says:

    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

  • John Scoles says:

    not sure what exactly you mean by batching and Oracle type support.

    Do you have any examples?

  • Laks says:

    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

  • John Scoles says:

    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
    http://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
    http://search.cpan.org/~pythian/DBD-Oracle-1.21/Oracle.pm#Returning_A_Recordset
    http://search.cpan.org/~pythian/DBD-Oracle-1.21/Oracle.pm#Binding_Cursors

  • Laks says:

    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.

  • Sergey says:

    Thank you very much for patching Makefile.PL!
    It’s very easy now to install DBD::Oracle with 11g.

  • John Scoles says:

    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

  • Paul Vallee says:

    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

  • John Scoles says:

    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.

  • Paul Vallee says:

    Excellent info, John, thanks.
    Paul

  • John Danilson says:

    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

  • perl daze says:

    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/password@$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

  • perl daze says:

    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.

  • Al says:

    God bless you! I downloaded DBD::Oracle, copied in your Makefile.PL, and it worked. Thank you, thank you, thank you!

  • Matt says:

    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

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>