Examples with Oracle Database Server

This section contains many examples related to different configurations:

An example with Oracle using local configuration

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)”.

Build the client program

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)
	  

Set-up LIXA environment

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.

Important

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.

Some checks before program execution

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
	    

Program execution (dynamic registration)

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
	  

Program execution (static registration)

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.

An example with remote configuration (Instant Client) and OCI

The LIXA state server (daemon) must be started as explained in the section called “Starting the state server (lixad)”.

Build the client program

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 LIXA environment

Set-up the necessary environment variable:

tiian@ubuntu1404-64:/tmp$ export LIXA_PROFILE=ORA_DYN
tiian@ubuntu1404-64:/tmp$ echo $LIXA_PROFILE
ORA_DYN
	  

Some checks before program execution

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

Program execution (dynamic registration)

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
	  

An example with Remote configuration (Instant Client) and Pro*C

Note

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

Set-up the Oracle environment (server side)

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 Oracle Listener

Configure at least one Oracle listener as explained in the section called “Configure Oracle Listener”.

Install and configure Oracle Instant Client

Install and configure Oracle Instant Client as explained in the section called “Install and configure Oracle Instant Client”.

Configure Oracle Pro*C precompiler

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)”.

Build the client program

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 LIXA environment

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.

Program execution (static registration)

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.