This section contains many examples related to different configurations:
the Application Program can be executed inside the same system that hosts the Resource Manager (Oracle Database) or in a different system. The first configuration is named "local" and necessitates only the Oracle Database Server software. The second configuration is named "remote" or "network" and necessitates both the Oracle Database Server and the Oracle Instant Client software
the C Application Program can be developed using Oracle Call Interface (OCI) or using embedded SQL that must precompiled using the Oracle Pro*C precompiler. OCI, Pro*C and XA are not compatible.
Using a real Resource Manager, like a DBMS, requires some extra effort because you have to set-up the environment needed by the Resource Manager. Refer to the section called “Local configuration (Server) and OCI” for Oracle configuration.
The LIXA state server (daemon) must be started as explained in the section called “Starting the state server (lixad)”.
Prepare the client (Application Program) using the below commands (gcc command was splitted on several lines using \ to help readability, but you may use a single line):
| System configuration details: |
Local connection, Ubuntu 8.04, Oracle XE 10.2 Database Server
tiian@ubuntu:~$ mkdir tmp tiian@ubuntu:~$ cd tmp tiian@ubuntu:~/tmp$ cp /opt/lixa/share/doc/lixa-X.Y.Z/examples/example2_ora.c . tiian@ubuntu:~/tmp$ gcc example2_ora.c $(/opt/lixa/bin/lixa-config -c -f -l -d) \ > -I/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/rdbms/public \ > -L/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib -l clntsh -l nnz10 \ > -o example2_ora |
| System configuration details: |
Local connection, Ubuntu 10.04, Oracle XE 11.2 Database Server
tiian@ubuntu:~$ mkdir tmp tiian@ubuntu:~$ cd tmp tiian@ubuntu:~/tmp$ cp /opt/lixa/share/doc/lixa-X.Y.Z/examples/example2_ora.c . tiian@ubuntu:~/tmp$ gcc example2_ora.c $(/opt/lixa/bin/lixa-config -c -f -l -d) \ > -I/u01/app/oracle/product/11.2.0/xe/rdbms/public/ \ > -L/u01/app/oracle/product/11.2.0/xe/lib/ -l clntsh -l nnz11 \ > -o example2_ora |
Verify the executable produced by gcc:
tiian@ubuntu:~/tmp$ ldd example2_ora
linux-gate.so.1 => (0xb7709000)
liblixac.so.0 => /opt/lixa/lib/liblixac.so.0 (0xb76ee000)
libclntsh.so.10.1 => not found
libnnz10.so => not found
libc.so.6 => /lib/tls/i686/cmov/libc.so.6 (0xb7590000)
libgmodule-2.0.so.0 => /usr/lib/libgmodule-2.0.so.0 (0xb758c000)
libdl.so.2 => /lib/tls/i686/cmov/libdl.so.2 (0xb7588000)
libgthread-2.0.so.0 => /usr/lib/libgthread-2.0.so.0 (0xb7583000)
librt.so.1 => /lib/tls/i686/cmov/librt.so.1 (0xb7579000)
libglib-2.0.so.0 => /usr/lib/libglib-2.0.so.0 (0xb74c8000)
libxml2.so.2 => /usr/lib/libxml2.so.2 (0xb73a8000)
liblixab.so.0 => /opt/lixa/lib/liblixab.so.0 (0xb7393000)
libm.so.6 => /lib/tls/i686/cmov/libm.so.6 (0xb736e000)
libpthread.so.0 => /lib/tls/i686/cmov/libpthread.so.0 (0xb7356000)
/lib/ld-linux.so.2 (0xb770a000)
libpcre.so.3 => /usr/lib/libpcre.so.3 (0xb732e000)
libz.so.1 => /usr/lib/libz.so.1 (0xb7319000)
libuuid.so.1 => /lib/libuuid.so.1 (0xb7315000)
There are three unresolved references that can be fixed setting up the environment properly:
tiian@ubuntu:~/tmp$ echo $LD_LIBRARY_PATH tiian@ubuntu:~/tmp$ export LD_LIBRARY_PATH=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib tiian@ubuntu:~/tmp$ echo $LD_LIBRARY_PATH /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib
Check again the executable:
tiian@ubuntu:~/tmp$ ldd example2_ora
linux-gate.so.1 => (0xb76f9000)
liblixac.so.0 => /opt/lixa/lib/liblixac.so.0 (0xb76de000)
libclntsh.so.10.1 => /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/libclntsh.so.10.1 (0xb692a000)
libnnz10.so => /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/libnnz10.so (0xb6724000)
libc.so.6 => /lib/tls/i686/cmov/libc.so.6 (0xb65c7000)
libgmodule-2.0.so.0 => /usr/lib/libgmodule-2.0.so.0 (0xb65c3000)
libdl.so.2 => /lib/tls/i686/cmov/libdl.so.2 (0xb65bf000)
libgthread-2.0.so.0 => /usr/lib/libgthread-2.0.so.0 (0xb65ba000)
librt.so.1 => /lib/tls/i686/cmov/librt.so.1 (0xb65b0000)
libglib-2.0.so.0 => /usr/lib/libglib-2.0.so.0 (0xb64ff000)
libxml2.so.2 => /usr/lib/libxml2.so.2 (0xb63df000)
liblixab.so.0 => /opt/lixa/lib/liblixab.so.0 (0xb63ca000)
libm.so.6 => /lib/tls/i686/cmov/libm.so.6 (0xb63a5000)
libpthread.so.0 => /lib/tls/i686/cmov/libpthread.so.0 (0xb638d000)
libnsl.so.1 => /lib/tls/i686/cmov/libnsl.so.1 (0xb6374000)
/lib/ld-linux.so.2 (0xb76fa000)
libpcre.so.3 => /usr/lib/libpcre.so.3 (0xb634d000)
libz.so.1 => /usr/lib/libz.so.1 (0xb6338000)
libuuid.so.1 => /lib/libuuid.so.1 (0xb6334000)
Set-up the necessary environment variables. Here's an example for Oracle XE 10.2 using a local connection and the SID identifier:
| System configuration details: |
Local connection, Ubuntu 8.04, Oracle XE 10.2 Database Server
tiian@ubuntu:~/tmp$ export PATH=$PATH:$ORACLE_HOME/bin tiian@ubuntu:~/tmp$ echo $PATH /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin tiian@ubuntu:~/tmp$ export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server tiian@ubuntu:~/tmp$ echo $ORACLE_HOME /usr/lib/oracle/xe/app/oracle/product/10.2.0/server tiian@ubuntu:~/tmp$ export ORACLE_SID=XE tiian@ubuntu:~/tmp$ echo $ORACLE_SID XE tiian@ubuntu:~/tmp$ export LIXA_PROFILE=ORA_DYN tiian@ubuntu:~/tmp$ echo $LIXA_PROFILE ORA_DYN |
It is suggested to set the necessary environment variables in your
profile if you are going to execute the programs many times.
This is the list of the suggested variables:
LD_LIBRARY_PATH,
LIXA_PROFILE,
ORACLE_HOME,
ORACLE_SID.
the third and fourth variables can be
set sourcing oracle_env.sh script: it's
installed in the Oracle's bin directory or
alternatively you have to edit your own as explained above.
We set LIXA_PROFILE to value
“ORA_DYN”, looking at
/opt/lixa/etc/lixac_conf.xml:
<profile name="ORA_DYN">
<sttsrvs>
<sttsrv>local_1</sttsrv>
</sttsrvs>
<rsrmgrs>
<rsrmgr>OracleXE_dynreg</rsrmgr>
</rsrmgrs>
</profile>
the profile references the Resource Manager named “OracleXE_dynreg”, looking again at the config file:
<rsrmgr name="OracleXE_dynreg" switch_file="/opt/lixa/lib/switch_oracle_dynreg.so" xa_open_info="Oracle_XA+Acc=P/hr/hr+SesTm=30+LogDir=/tmp+threads=true+DbgFl=7+Loose_Coupling=true" xa_close_info="" />
we can discover that our application will access the Oracle
database using hr
user and writing the trace file to directory
/tmp (see LogDir)
[20]. Verify no trace file exists:
tiian@ubuntu:~/tmp$ ls -la /tmp/xa* ls: cannot access /tmp/xa*: No such file or directory
Execute the client program as shown below:
tiian@ubuntu:~/tmp$ ./example2_ora INSERT statement executed! First arg is not 'DELETE', bypassing DELETE statement...
Check the table after program execution:
tiian@ubuntu:~/tmp$ sqlplus "hr/hr" SQL*Plus: Release 10.2.0.1.0 - Production on Sun Apr 10 10:34:23 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production SQL> select * from COUNTRIES where COUNTRY_ID = 'RS'; CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- RS Repubblica San Marino 1 SQL> exit Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
The example program example2_ora inserted a
row in table COUNTRIES. Take a look to the
trace produced by the Oracle client library:
tiian@ubuntu:~/tmp$ ls -la /tmp/xa* -rw-r--r-- 1 tiian tiian 1723 2011-04-10 10:33 /tmp/xa_NULL04102011.trc tiian@ubuntu:~/tmp$ cat /tmp/xa_NULL04102011.trc ORACLE XA: Version 10.2.0.1.0. RM name = 'Oracle_XA'. 103352.5300.3057182448.0: xaoopen: xa_info=Oracle_XA+Acc=P/hr/hr+SesTm=30+LogDir=/tmp+threads=true+DbgFl=7+Loose_Coupling=true,rmid=0,flags=0x0 103352.5300.3057182448.0: xaolgn_help: version#: 169869568 banner: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production 103352.5300.3057182448.0: xaolgn: sqlxrc/sqlxss completed 103352.5300.3057182448.0: xaolgn2: return XA_OK 103352.5300.3057182448.0: xaoopen: xaolgn completed 103352.5300.3057182448.0: xaoopen: return 0 103352.5300.3057182448.0: ax_reg: xid=0x494c4158-8c6122ac9fd0477ebfe5f650e21a3539-a154ad9214d0544a3abbd33c8c2ba36f, rmid=0, flags=0x0 103352.5300.3057182448.0: OCITransStart: Attempting 103352.5300.3057182448.0: OCITransStart: Succeeded 103352.5300.3057182448.0: xaodynpo 2: rmid=0, state=3 103352.5300.3057182448.0: xaoend: xid=0x494c4158-8c6122ac9fd0477ebfe5f650e21a3539-a154ad9214d0544a3abbd33c8c2ba36f, rmid=0, flags=0x4000000 103352.5300.3057182448.0: OCITransDetach: Attempting 103352.5300.3057182448.0: OCITransDetach: Succeeded 103352.5300.3057182448.0: xaoend: return 0 103352.5300.3057182448.0: xaocommit: xid=0x494c4158-8c6122ac9fd0477ebfe5f650e21a3539-a154ad9214d0544a3abbd33c8c2ba36f, rmid=0, flags=0x40000000 103352.5300.3057182448.0: OCITransCommit: Attempting 103352.5300.3057182448.0: xaodynpo 2: rmid=0, state=1 103352.5300.3057182448.0: OCITransCommit: Succeeded 103352.5300.3057182448.0: xaocommit: rtn 0 103352.5300.3057182448.0: xaoclose: xa_info=, rmid=0, flags=0x0 103352.5300.3057182448.0: OCIServerDetach: Attempting 103352.5300.3057182448.0: OCIServerDetach: Succeeded 103352.5300.3057182448.0: xaoclose: rtn 0
The ax_reg function was called because the
dynamic registration configuration was used
(LIXA_PROFILE=ORA_DYN).
Now you can remove
the inserted record using the same program:
tiian@ubuntu:~/tmp$ ./example2_ora delete First arg is 'delete', bypassing INSERT statement... DELETE statement executed!
Check the content of the table again:
tiian@ubuntu:~/tmp$ sqlplus "hr/hr" SQL*Plus: Release 10.2.0.1.0 - Production on Sun Apr 10 10:40:25 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production SQL> select * from COUNTRIES where COUNTRY_ID = 'RS'; no rows selected SQL> exit Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
The row was deleted. Please remove the trace file before a new execution:
tiian@ubuntu:~/tmp$ rm /tmp/xa_NULL04102011.trc
Switch from dynamic registration to static registration (local connection):
tiian@ubuntu:~/tmp$ echo $LIXA_PROFILE ORA_DYN tiian@ubuntu:~/tmp$ export LIXA_PROFILE=ORA_STA tiian@ubuntu:~/tmp$ echo $LIXA_PROFILE ORA_STA
Here's the equivalent for the network connection using Oracle Instant Client:
tiian@ubuntu1404-64:/tmp$ echo $LIXA_PROFILE ORAOCI_DYN tiian@ubuntu1404-64:/tmp$ export LIXA_PROFILE=ORAOCI_STA tiian@ubuntu1404-64:/tmp$ echo $LIXA_PROFILE ORAOCI_STA
Execute the program again:
tiian@ubuntu:~/tmp$ ./example2_ora INSERT statement executed! First arg is not 'DELETE', bypassing DELETE statement... tiian@ubuntu:~/tmp$ sqlplus "hr/hr" SQL*Plus: Release 10.2.0.1.0 - Production on Sun Apr 10 10:53:05 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production SQL> select * from COUNTRIES where COUNTRY_ID = 'RS'; CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- RS Repubblica San Marino 1 SQL> exit Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Inspect the produced trace file:
tiian@ubuntu:~/tmp$ ls -la /tmp/xa_* -rw-r--r-- 1 tiian tiian 1661 2011-04-10 10:52 /tmp/xa_NULL04102011.trc tiian@ubuntu:~/tmp$ cat /tmp/xa_NULL04102011.trc ORACLE XA: Version 10.2.0.1.0. RM name = 'Oracle_XA'. 105236.5392.3057456880.0: xaoopen: xa_info=Oracle_XA+Acc=P/hr/hr+SesTm=30+LogDir=/tmp+threads=true+DbgFl=7+Loose_Coupling=true,rmid=0,flags=0x0 105236.5392.3057456880.0: xaolgn_help: version#: 169869568 banner: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production 105236.5392.3057456880.0: xaolgn: sqlxrc/sqlxss completed 105236.5392.3057456880.0: xaolgn: return XA_OK 105236.5392.3057456880.0: xaoopen: xaolgn completed 105236.5392.3057456880.0: xaoopen: return 0 105236.5392.3057456880.0: xaostart: xid=0x494c4158-6119154810784a87b0d0aa83ea497a72-55263ef9237e27f8ae34f46936ce295d, rmid=0, flags=0x0 105236.5392.3057456880.0: OCITransStart: Attempting 105236.5392.3057456880.0: OCITransStart: Succeeded 105236.5392.3057456880.0: xaostart: return XA_OK 105236.5392.3057456880.0: xaoend: xid=0x494c4158-6119154810784a87b0d0aa83ea497a72-55263ef9237e27f8ae34f46936ce295d, rmid=0, flags=0x4000000 105236.5392.3057456880.0: OCITransDetach: Attempting 105236.5392.3057456880.0: OCITransDetach: Succeeded 105236.5392.3057456880.0: xaoend: return 0 105236.5392.3057456880.0: xaocommit: xid=0x494c4158-6119154810784a87b0d0aa83ea497a72-55263ef9237e27f8ae34f46936ce295d, rmid=0, flags=0x40000000 105236.5392.3057456880.0: OCITransCommit: Attempting 105236.5392.3057456880.0: OCITransCommit: Succeeded 105236.5392.3057456880.0: xaocommit: rtn 0 105236.5392.3057456880.0: xaoclose: xa_info=, rmid=0, flags=0x0 105236.5392.3057456880.0: OCIServerDetach: Attempting 105236.5392.3057456880.0: OCIServerDetach: Succeeded 105236.5392.3057456880.0: xaoclose: rtn 0
The xa_start (xaostart) function was called
because the static registration configuration was used
(LIXA_PROFILE=ORA_STA).
Remove the inserted row again:
tiian@ubuntu:~/tmp$ ./example2_ora delete First arg is 'delete', bypassing INSERT statement... DELETE statement executed! tiian@ubuntu:~/tmp$ sqlplus "hr/hr" SQL*Plus: Release 10.2.0.1.0 - Production on Sun Apr 10 11:01:32 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production SQL> select * from COUNTRIES where COUNTRY_ID = 'RS'; no rows selected SQL> exit Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
You have successfully executed an Application Program that uses Oracle Database Server as a Resource Manager.
The LIXA state server (daemon) must be started as explained in the section called “Starting the state server (lixad)”.
Prepare the client (Application Program) using the below commands (gcc command was splitted on several lines using \ to help readability, but you may use a single line):
| System configuration details: |
Remote connection, Ubuntu 14.04, Oracle 12.1 Instant Client
tiian@ubuntu1404-64:/tmp$ cp /opt/lixa/share/doc/lixa-X.Y.Z/examples/example2_ora.c . tiian@ubuntu1404-64:/tmp$ gcc example2_ora.c \ > $(/opt/lixa/bin/lixa-config -c -f -l -d) \ > -I/opt/oracle/instantclient_12_1/sdk/include \ > -L/opt/oracle/instantclient_12_1 \ > -Wl,-rpath -Wl,/opt/oracle/instantclient_12_1 \ > -l clntsh -l nnz12 -o example2_ora |
Verify the executable produced by gcc:
tiian@ubuntu1404-64:/tmp$ ldd example2_ora linux-vdso.so.1 => (0x00007fff7649c000) liblixac.so.0 => /opt/lixa/lib/liblixac.so.0 (0x00007f32ec40f000) libclntsh.so.12.1 => /opt/oracle/instantclient_12_1/libclntsh.so.12.1 (0x00007f32e9452000) libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f32e9082000) libgmodule-2.0.so.0 => /usr/lib/x86_64-linux-gnu/libgmodule-2.0.so.0 (0x00007f32e8e7e000) libglib-2.0.so.0 => /lib/x86_64-linux-gnu/libglib-2.0.so.0 (0x00007f32e8b76000) libxml2.so.2 => /usr/lib/x86_64-linux-gnu/libxml2.so.2 (0x00007f32e880e000) liblixab.so.0 => /opt/lixa/lib/liblixab.so.0 (0x00007f32e85f5000) libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f32e83d7000) libmql1.so => /opt/oracle/instantclient_12_1/libmql1.so (0x00007f32e8160000) libipc1.so => /opt/oracle/instantclient_12_1/libipc1.so (0x00007f32e7de2000) libnnz12.so => /opt/oracle/instantclient_12_1/libnnz12.so (0x00007f32e76d8000) libons.so => /opt/oracle/instantclient_12_1/libons.so (0x00007f32e7492000) libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f32e728e000) libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f32e6f88000) libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x00007f32e6d6d000) librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f32e6b65000) /lib64/ld-linux-x86-64.so.2 (0x00007f32ec62c000) libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x00007f32e6963000) libclntshcore.so.12.1 => /opt/oracle/instantclient_12_1/libclntshcore.so.12.1 (0x00007f32e63f0000) libpcre.so.3 => /lib/x86_64-linux-gnu/libpcre.so.3 (0x00007f32e61b2000) libz.so.1 => /lib/x86_64-linux-gnu/libz.so.1 (0x00007f32e5f98000) liblzma.so.5 => /lib/x86_64-linux-gnu/liblzma.so.5 (0x00007f32e5d76000) libuuid.so.1 => /lib/x86_64-linux-gnu/libuuid.so.1 (0x00007f32e5b71000)
Set-up the necessary environment variable:
tiian@ubuntu1404-64:/tmp$ export LIXA_PROFILE=ORA_DYN tiian@ubuntu1404-64:/tmp$ echo $LIXA_PROFILE ORA_DYN
We set LIXA_PROFILE to value
“ORA_DYN”, looking at
/opt/lixa/etc/lixac_conf.xml:
<profile name="ORA_DYN">
<sttsrvs>
<sttsrv>local_1</sttsrv>
</sttsrvs>
<rsrmgrs>
<rsrmgr>OracleIC_dynreg</rsrmgr>
</rsrmgrs>
</profile>
the profile references the Resource Manager named “OracleIC_dynreg”, looking again at the config file:
<rsrmgr name="OracleIC_dynreg" switch_file="/opt/lixa/lib/switch_oracle_dynreg.so" xa_open_info="Oracle_XA+Acc=P/hr/hr+SesTm=30+LogDir=/tmp+threads=true+DbgFl=7+SqlNet=lixa_ora_db+Loose_Coupling=true" xa_close_info="" />
we can discover that our application will access the Oracle
database using hr
user and writing the trace file to directory
/tmp (see LogDir)
[21].
Execute the client program and check how the content table changes:
tiian@ubuntu1404-64:/tmp$ ./example2_ora INSERT statement executed! First arg is not 'DELETE', bypassing DELETE statement... tiian@ubuntu1404-64:/tmp$ sqlplus hr/hr@lixa_ora_db SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 18 23:41:11 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Wed Jan 18 2017 23:40:54 +01:00 Connected to: Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production SQL> select * from COUNTRIES where COUNTRY_ID = 'RS'; CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- RS Repubblica San Marino 1 SQL> exit Disconnected from Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production tiian@ubuntu1404-64:/tmp$ ./example2_ora DELETE First arg is 'DELETE', bypassing INSERT statement... DELETE statement executed! tiian@ubuntu1404-64:/tmp$ sqlplus hr/hr@lixa_ora_db SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 18 23:41:40 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Wed Jan 18 2017 23:41:35 +01:00 Connected to: Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production SQL> select * from COUNTRIES where COUNTRY_ID = 'RS'; no rows selected SQL> exit Disconnected from Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
The example program example2_ora inserted a
row in table COUNTRIES. Take a look to the
trace produced by the Oracle client library for the first
execution ("insert"):
ORACLE XA: Version 12.1.0.2.0. RM name = 'Oracle_XA'. 234322.16513.1350203008.0: xaoopen: xa_info=ORACLE_XA+Acc=P/hr/**+SesTm=30+LogDir=/tmp+threads=true+DbgFl=7+SqlNet=lixa_ora_db+Loose_Coupling=true,rmid=0,flags=0x0 234322.16513.1350203008.0: xaolgn_help: version#: 185597952 banner: Oracle Database 11g Release 11.1.0.0.0 - Production 234322.16513.1350203008.0: xaolgn: sqlxrc/sqlxss completed 234322.16513.1350203008.0: xaolgn2: return XA_OK 234322.16513.1350203008.0: xaoopen: xaolgn completed 234322.16513.1350203008.0: xaoopen: return 0 234322.16513.1350203008.0: ax_reg: xid=0x4c495841-d3c276e84a95421d95eb86c0563c5fa8-54bfbba0e21ccc50c05577130f0b2f6f, rmid=0, flags=0x0 234322.16513.1350203008.0: OCITransStart: Attempting 234322.16513.1350203008.0: OCITransStart: Succeeded 234322.16513.1350203008.0: xaodynpo 2: rmid=0, state=131 234322.16513.1350203008.0: xaoend: xid=0x4c495841-d3c276e84a95421d95eb86c0563c5fa8-54bfbba0e21ccc50c05577130f0b2f6f, rmid=0, flags=0x4000000 234322.16513.1350203008.0: OCITransDetach: Attempting 234322.16513.1350203008.0: OCITransDetach: Succeeded 234322.16513.1350203008.0: xaoend: return 0 234322.16513.1350203008.0: xaocommit: xid=0x4c495841-d3c276e84a95421d95eb86c0563c5fa8-54bfbba0e21ccc50c05577130f0b2f6f, rmid=0, flags=0x40000000 234322.16513.1350203008.0: OCITransCommit: Attempting 234322.16513.1350203008.0: xaodynpo 2: rmid=0, state=129 234322.16513.1350203008.0: OCITransCommit: Succeeded 234322.16513.1350203008.0: xaocommit: rtn 0 234322.16513.1350203008.0: xaoclose: xa_info=, rmid=0, flags=0x0 234322.16513.1350203008.0: OCIServerDetach: Attempting 234322.16513.1350203008.0: OCIServerDetach: Succeeded 234322.16513.1350203008.0: xaoclose: rtn 0
This example has been tested using Red Hat Enterprise Linux 7.3 and Oracle Instant Client 12.1
Using a remote configuration instead of a local configuration introduces two differences:
the Oracle Database Server must be configured to be accessible from another system: a listener must be configured
the Oracle Instant Client software must be installed and configured in the system that will connect to the database
The first part of the Oracle Database Server is pretty the same described in the section called “Local configuration (Server) and OCI”. All the statements remain valid for Oracle 12c Standard Edition with the exception of the paths. Here's a default path installation example:
[oracle@centos7-oracle12 ~]$ ls -la $ORACLE_HOME/rdbms/admin/xaview.sql -rw-r--r--. 1 oracle oinstall 1941 Apr 21 2011 /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/xaview.sql
Configure at least one Oracle listener as explained in the section called “Configure Oracle Listener”.
Install and configure Oracle Instant Client as explained in the section called “Install and configure Oracle Instant Client”.
If you don't like to use a lot of options on the command line,
you will have to configure the content of
pcscfg.cfg.
Here's an example for Red Hat Enterprise Linux 7.3:
[tiian@rhel73 admin]$ cat /opt/oracle/instantclient_12_1/precomp/admin/pcscfg.cfg sys_include=(/usr/include,/usr/lib/gcc/x86_64-redhat-linux/4.8.5/include,/usr/lib/gcc/x86_64-redhat-linux/4.8.2/include) include=($ORACLE_HOME/sdk/include,/opt/lixa/include) ltype=short define=__x86_64__
Especially "sys_include" and "include" need your attention.
The LIXA state server (daemon) must be started as explained in the section called “Starting the state server (lixad)”.
Prepare the client (Application Program) using the below commands (gcc command was splitted on several lines using \ to help readability, but you may use a single line):
| System configuration details: |
Remote connection, RHEL 7.3, Oracle 12.1 Instant Client
[tiian@rhel73 ~]$ export PATH=/opt/oracle/instantclient_12_1/sdk:/opt/oracle/instantclient_12_1:$PATH [tiian@rhel73 ~]$ type proc proc is /opt/oracle/instantclient_12_1/sdk/proc [tiian@rhel73 tmp]$ export LD_LIBRARY_PATH=/opt/oracle/instantclient_12_1:$LD_LIBRARY_PATH [tiian@rhel73 tmp]$ echo $LD_LIBRARY_PATH /opt/oracle/instantclient_12_1: [tiian@rhel73 tmp]$ cp /opt/lixa/share/doc/lixa-X.Y.Z/examples/example14_ora.pc . [tiian@rhel73 tmp]$ proc code=ANSI_C example14_ora.pc [tiian@rhel73 tmp]$ gcc example14_ora.c -Wno-unused-variable \ > $(/opt/lixa/bin/lixa-config -c -f -l -d) \ > -I/opt/oracle/instantclient_12_1/sdk/include \ > -L/opt/oracle/instantclient_12_1 \ > -Wl,-rpath -Wl,/opt/oracle/instantclient_12_1 \ > -l clntsh -l nnz12 -o example14_ora |
Verify the executable produced by gcc:
[tiian@rhel73 tmp]$ ldd example14_ora
linux-vdso.so.1 => (0x00007ffc78dc6000)
liblixac.so.0 => /opt/lixa/lib/liblixac.so.0 (0x00007f115d077000)
libclntsh.so.12.1 => /opt/oracle/instantclient_12_1/libclntsh.so.12.1 (0x00007f115a0b9000)
libnnz12.so => /opt/oracle/instantclient_12_1/libnnz12.so (0x00007f11599af000)
libc.so.6 => /lib64/libc.so.6 (0x00007f11595e7000)
libgmodule-2.0.so.0 => /lib64/libgmodule-2.0.so.0 (0x00007f11593e2000)
libgthread-2.0.so.0 => /lib64/libgthread-2.0.so.0 (0x00007f11591e0000)
libglib-2.0.so.0 => /lib64/libglib-2.0.so.0 (0x00007f1158ea9000)
libxml2.so.2 => /lib64/libxml2.so.2 (0x00007f1158b3e000)
liblixab.so.0 => /opt/lixa/lib/liblixab.so.0 (0x00007f1158926000)
libm.so.6 => /lib64/libm.so.6 (0x00007f1158624000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f1158407000)
libmql1.so => /opt/oracle/instantclient_12_1/libmql1.so (0x00007f1158191000)
libipc1.so => /opt/oracle/instantclient_12_1/libipc1.so (0x00007f1157e13000)
libons.so => /opt/oracle/instantclient_12_1/libons.so (0x00007f1157bcd000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f11579c9000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f11577b0000)
librt.so.1 => /lib64/librt.so.1 (0x00007f11575a7000)
/lib64/ld-linux-x86-64.so.2 (0x00007f115d292000)
libaio.so.1 => /lib64/libaio.so.1 (0x00007f11573a5000)
libclntshcore.so.12.1 => /opt/oracle/instantclient_12_1/libclntshcore.so.12.1 (0x00007f1156e33000)
libz.so.1 => /lib64/libz.so.1 (0x00007f1156c1c000)
liblzma.so.5 => /lib64/liblzma.so.5 (0x00007f11569f6000)
libuuid.so.1 => /lib64/libuuid.so.1 (0x00007f11567f1000)
Set-up the necessary environment variable:
tiian@ubuntu1404-64:/tmp$ export LIXA_PROFILE=ORA_STA tiian@ubuntu1404-64:/tmp$ echo $LIXA_PROFILE ORA_STA
In the previous example we used the dynamic XA registration, this time we are using the static one, but both can be used for OCI and Pro*C.
Execute the client program and check how the content table changes:
[tiian@rhel73 tmp]$ ./example14_ora First arg is not 'DELETE', bypassing DELETE statement... [tiian@rhel73 tmp]$ sqlplus hr/hr@lixa_ora_db SQL*Plus: Release 12.1.0.2.0 Production on Thu Jan 19 00:30:27 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Thu Jan 19 2017 00:30:11 +01:00 Connected to: Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production SQL> select * from COUNTRIES where COUNTRY_ID = 'RS'; CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- RS Repubblica San Marino 1 SQL> exit Disconnected from Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production [tiian@rhel73 tmp]$ ./example14_ora DELETE First arg is 'DELETE', bypassing INSERT statement... [tiian@rhel73 tmp]$ sqlplus hr/hr@lixa_ora_db SQL*Plus: Release 12.1.0.2.0 Production on Thu Jan 19 00:30:46 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Thu Jan 19 2017 00:30:45 +01:00 Connected to: Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production SQL> select * from COUNTRIES where COUNTRY_ID = 'RS'; no rows selected SQL> exit Disconnected from Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
The example program example14_ora inserted a
row in table COUNTRIES. Take a look to the
trace produced by the Oracle client library for the second
execution ("delete"):
ORACLE XA: Version 12.1.0.2.0. RM name = 'Oracle_XA'. 003044.3009.2262976064.0: xaoopen: xa_info=ORACLE_XA+Acc=P/hr/**+SesTm=30+LogDir=/tmp+threads=true+DbgFl=7+SqlNet=lixa_ora_db+Loose_Coupling=true,rmid=0,flags=0x0 003044.3009.2262976064.0: xaolgn_help: version#: 185597952 banner: Oracle Database 11g Release 11.1.0.0.0 - Production 003044.3009.2262976064.0: xaolgn: sqlxrc/sqlxss completed 003044.3009.2262976064.0: xaolgn: return XA_OK 003044.3009.2262976064.0: xaoopen: xaolgn completed 003044.3009.2262976064.0: xaoopen: return 0 003044.3009.2262976064.0: xaostart: xid=0x4c495841-a593cf11a3a24fcb945f6502b6f2210d-c220ba3a3bd0699d93a333c82bbac597, rmid=0, flags=0x0 003044.3009.2262976064.0: OCITransStart: Attempting 003044.3009.2262976064.0: OCITransStart: Succeeded 003044.3009.2262976064.0: xaostart: return XA_OK 003044.3009.2262976064.0: xaoend: xid=0x4c495841-a593cf11a3a24fcb945f6502b6f2210d-c220ba3a3bd0699d93a333c82bbac597, rmid=0, flags=0x4000000 003044.3009.2262976064.0: OCITransDetach: Attempting 003044.3009.2262976064.0: OCITransDetach: Succeeded 003044.3009.2262976064.0: xaoend: return 0 003044.3009.2262976064.0: xaocommit: xid=0x4c495841-a593cf11a3a24fcb945f6502b6f2210d-c220ba3a3bd0699d93a333c82bbac597, rmid=0, flags=0x40000000 003044.3009.2262976064.0: OCITransCommit: Attempting 003044.3009.2262976064.0: OCITransCommit: Succeeded 003044.3009.2262976064.0: xaocommit: rtn 0 003044.3009.2262976064.0: xaoclose: xa_info=, rmid=0, flags=0x0 003044.3009.2262976064.0: OCIServerDetach: Attempting 003044.3009.2262976064.0: OCIServerDetach: Succeeded 003044.3009.2262976064.0: xaoclose: rtn 0
[20]
The content of xa_open_info string is
described in chapter 15 "Developing Applications with Oracle XA"
of the "Oracle Database Application Developer's Guide"
manual; please refer to the documentation published by
Oracle Corporation for further details.
[21]
The content of xa_open_info string is
described in chapter "Developing Applications with Oracle XA"
of the "Oracle Database Application Developer's Guide"
manual; please refer to the documentation published by
Oracle Corporation for further details.