AnySQL.net English

    I have wrote several DBA utilities, in Windows, it can run under 8i/9i/10g client, but in Linux/Unix, it require the Oracle client version library on which compiled when run. Finally I found a solution.

    Found a development machine, we will modify the script "genclntsh" under $ORACLE_HOME/bin first as following:

#
# Library names and locations
CLNT_NAM=clntsh                          # (short) library name
CLNT_VER=9.0                            # library version number
CLNT_LNK=lib${CLNT_NAM}.so              # name of symlink to library
CLNT_LIB=${CLNT_LNK}.${CLNT_VER}        # actual library file name
LIB_DIR=${ORACLE_HOME}/${LIB}           # lib. destination directory

#
# Library names and locations
CLNT_NAM=clntsh                         # (short) library name
CLNT_VER=9.0                            # library version number
CLNT_LIB=lib${CLNT_NAM}.so              # name of symlink to library
CLNT_LNK=${CLNT_LNK}.${CLNT_VER}        # actual library file name
LIB_DIR=${ORACLE_HOME}/${LIB}           # lib. destination directory

    Second, run genclntsh (For 32 bit client library on 64Bit unix, add command option "-32"), do not run this on server host with database running:

    Third, We now can use gcc to compile the OCI program as following:

gcc -o ocidemo.bin ocidemo.c -I${ORACLE_HOME}/rdbms/demo -L${ORACLE_HOME}/lib -lclntsh -Wl,-Bdynamic

    Forth, Write a shell executable to run the OCI program, for example (ocidemo.bin):

#!/bin/sh

if [ "A${ORACLE_HOME}A" = "AA" ]; then
   echo "ORACLE_HOME environment variable not setted."
   exit
fi

if [ "A${LD_LIBRARY_PATH}A" = "AA" ];then
   LD_LIBRARY_PATH=/lib:/usr/lib
fi

if [ -d ${ORACLE_HOME}/lib32 ]; then
   LD_LIBRARY_PATH=${ORACLE_HOME}/lib32:${LD_LIBRARY_PATH}
else
   LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
fi

export LD_LIBRARY_PATH

ocidemo.bin $*

    I have test this method on RedHat Linux and Solaris, so all my OCI based utilities are ok to run under 8i/9i/10g version client.

    Usually we need to recompile the perl DBD-Oracle when we change the Oracle client version, else it may not work properly. If you have multiple version of Oracle on your server, and you have some scripts wrote in perl need to run on all the databases, you need to use a specific version of Oracle client, or install different binary of perl and compile a version specific DBD-Oracle for each perl binary copy. Why? because when you compile you DBD-Oracle, then generated module file will find version specific Oracle client library file, use ldd to verify it as following:

$ldd ./lib/site_perl/5.8.5/sun4-solaris-64/auto/DBD/Oracle/Oracle.so
        libclntsh.so.9.0 =>      /export/home/oracle/products/9201/lib/libclntsh.so.9.0
        libnsl.so.1 =>   /usr/lib/64/libnsl.so.1
        libsocket.so.1 =>        /usr/lib/64/libsocket.so.1
        ......

    But there is always a symbo-link "libclntsh.so" which point to the version specific library file "libclntsh.so.ver", if we can make the perl DBD-Oracle module file find only the symbo-link name, it make work under different version of Oracle client without recompile. All of my OCI utility work under this way, they can be run under 8i/9i/10g client without recompile, use ldd to check one of my free utility "ociuldr.bin", as following:

$ldd ociuldr.bin
        libm.so.1 =>     /usr/lib/libm.so.1
        libclntsh.so =>  /export/home/oracle/products/9201/lib32/libclntsh.so
        ......

    And I try the method which I used to compile my OCI utility, first download the DBD-Oracle source code, and the we could start compile the DBD-Oracle.

    Perl have 32 Bits and 64 Bits version, so DBD-Oracle also have 32 Bits and 64 Bits version. Extract the source and then run the following command to generate the makefile:

32Bit: perl Makefile.PL -r=build32
64Bit: perl64 Makefile.pl -r=build64

    Now we run the make command, and find out where the generated module (Oracle.so) located:

$find ./ -name Oracle.so
./blib/arch/auto/DBD/Oracle/Oracle.so

    If the downloaded source's version is the same with currently used, you could just replace the module file. If you want to make the module work under 8i/9i/10g all without recompile, please compile at 10g client, and set the "otp_V" of DBD-Oracle to 8 (You could edit the generated Makefile). Now you can test under 8i/9i/10g with a demo perl program, the only thing you need to do is setting correctly the LD_LIBRARY_PATH or LIBPATH (aix) environment variable. As following:

32Bit
8i:${ORACLE_HOME}/lib
9i:${ORACLE_HOME}/lib32
10g${ORACLE_HOME}/lib32

64Bit
8i:${ORACLE_HOME}/lib64
9i:${ORACLE_HOME}/lib
10g${ORACLE_HOME}/lib

    I have successfully compile it and test it under 8i/9i/10g, and waiting for 11g environment to test.

    The latest Perl DBI support array operation, use an array as a bind value when operating with database. From the database layer, it's not a new feature, like Oracle Pro*C & OCI interface. After use of the array interface of Perl, I have suggestion to add a new API interface to Perl DBI. Usually the Perl DBI will return the result based on row as following:

{
  ROW1  {col1, col2, col3, col4},
  ROW2  {col1, col2, col3, col4},
  .....
  ROWn  {col1, col2, col3, col4}
}

    But when binding, we need to bind a column based array :

$sth->bind_param_array( COL1={row1, row2, ... rown});
$sth->bind_param_array( COL2={row1, row2, ... rown});
.....
$sth->bind_param_array( COLn={row1, row2, ... rown});

    So we need do convertion in program, from the database interface layer, when using array fetch, the return result is actually column based, as following :

COL1={ROW1, ROW2, ..., ROWn}
COL2={ROW1, ROW2, ..., ROWn}
......
COLn={ROW1, ROW2, ..., ROWn}

    Perl do the column-row conversion for us, and we do the row-column conversion for array binding. Why not introduce an interface to return column based array in DBI for performance improve?