Close menu

Using SQL/R ODBC with Oracle® Heterogeneous Services

The following notes briefly describe an example setup for using SQL/R ODBC with Oracle® Heterogeneous Services to allow an Oracle database server accessing data in an Eloquence database.

The example is based on Oracle 12c running in an x86_64 Oracle Linux 6.5 VM
(see http://www.oracle.com/technetwork/community/developer-vm/index.html)

The basic steps are:

  1. set up SQL/R ODBC (odbc.cfg and odbc.dsn)
  2. set up unixODBC (odbcinst.ini and odbc.ini) using legacy64 version
  3. set up Oracle dg4odbc (initsqlr.ora, listener.ora, tnsnames.ora)
  4. create and use database link in sqlplus

1. set up SQL/R ODBC (odbc.cfg and odbc.dsn)

Install SQL/R if not already present 
# wget http://www.marxmeier.com/download/sqlr/A0300/linux/SQLR-A0309-1.x86_64.rpm

# rpm -ihv SQLR-A0309-1.x86_64.rpm

# useradd --system --user-group --create-home sqlr
Adjust /etc/opt/sqlr2/odbc.cfg 
 Security = db
 Service = sqlrodbc
 HttpService = 8004
 LogFile = /home/sqlr/odbc.log
 LogFlags = *1E2
Adjust /etc/opt/sqlr2/odbc.dsn 
 [ToyDB]
 Database   = example.marxmeier.com:8102/toydb
 UserList   = public
 RowidName  = RecNo
Start SQL/R service 
 # service sqlr2 start    # or "/etc/init.d/sqlr2 start

2. set up unixODBC (odbcinst.ini and odbc.ini) using legacy version

Install older unixODBC driver manager as Oracle dg4odbc seems to use BUILD_LEGACY_64_BIT_MODE. 
# pkg_site=http://public-yum.oracle.com/repo/OracleLinux/OL5/latest/x86_64/getPackage

# wget $pkg_site/unixODBC-2.2.11-10.el5.x86_64.rpm
# wget $pkg_site/unixODBC-libs-2.2.11-10.el5.x86_64.rpm

# rpm -ihv unixODBC-libs-2.2.11-10.el5.x86_64.rpm unixODBC-2.2.11-10.el5.x86_64.rpm

# ll /usr/lib64/libodbc.so*
lrwxrwxrwx. 1 root root     16 May 14 10:01 /usr/lib64/libodbc.so -> libodbc.so.1.0.0
lrwxrwxrwx. 1 root root     16 May 14 10:01 /usr/lib64/libodbc.so.1 -> libodbc.so.1.0.0
-rwxr-xr-x. 1 root root 409128 Dec 20  2011 /usr/lib64/libodbc.so.1.0.0

# odbcinst -j
unixODBC 2.2.11
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /root/.odbc.ini
Adjust /etc/odbcinst.ini 
 [SQLR]
 Description   = Legacy64 mode ODBC for SQLR
 Driver        = /opt/sqlr2/lib/libsqlrodbc.so
 #Driver64     = /opt/sqlr2/lib64/libsqlrodbc64.so
 Driver64      = /opt/sqlr2/lib64/libsqlrodbc.so
Adjust /etc/odbc.ini 
 [ToyDB]
 Driver     = SQLR
 Servername = localhost
 Database   = toydb
Test Eloquence access with unixODBC isql first 
# isql toydb

SQL> select * from products
+-----------+-----------------+--------------------+-------------+------------+------------+
| PRODUCT_NO| PRODUCT_NAME    | PRICE              | PRODUCT_LINE| QUANTITY   | RECNO      |
+-----------+-----------------+--------------------+-------------+------------+------------+
| A00003    | POKER DICE SET  | 125                | 10          | 500        | 1          |
| A00008    | POSTER PAINTS   | 95                 | 30          | 1250       | 2          |
| A00009    | COLOURING BOOK  | 65                 | 30          | 1000       | 3          |
| A00001    | PACK OF CARDS   | 75                 | 10          | 1500       | 4          |
| A00010    | ERASER GIFT SET | 185                | 30          | 1500       | 5          |
| A00002    | LUDO SET        | 1250               | 10          | 750        | 6          |
| A00005    | 15" PINK RABBIT | 1745               | 20          | 200        | 7          |
| A00007    | SET OF CRAYONS  | 175                | 30          | 500        | 8          |
| A00004    | 12" TEDDY BEAR  | 1525               | 20          | 250        | 9          |
| A00006    | SET OF PANDAS   | 2500               | 20          | 150        | 10         |
+-----------+-----------------+--------------------+-------------+------------+------------+
SQLRowCount returns -1
10 rows fetched

SQL>

3. set up Oracle dg4odbc (initsqlr.ora, listener.ora, tnsnames.ora)

Create $ORACLE_HOME/hs/admin/initsqlr.ora based on $ORACLE_HOME/hs/admin/initdg4odbc.ora 
 #
 # HS init parameters
 #
 HS_FDS_CONNECT_INFO = ToyDB
 # HS_FDS_TRACE_LEVEL = DEBUG
 HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
 HS_FDS_SQLLEN_INTERPRETATION = 32
 # HS_LANGUAGE = ENGLISH_AMERICA.WE8ISO8859P1
 HS_LANGUAGE = ENGLISH_AMERICA.US7ASCII
 HS_NLS_NCHAR = UCS2

 #
 # ODBC specific environment variables
 #
 set ODBCINI=/etc/odbc.ini

 #
 # Environment variables required for the non-Oracle system
 #
 #set SQLR_LOG_FILE=/tmp/sqlr.log
 #set SQLR_LOG_FLAGS=*3
Adjust $ORACLE_HOME/network/admin/listener.ora (adapt below example to your dir names) 
SID_LIST_LISTENER=
   (SID_LIST=
      (SID_DESC=
         (SID_NAME=sqlr)
         (ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
         (PROGRAM=dg4odbc)
         (ENVS=LD_LIBRARY_PATH=/opt/sqlr2/lib64:/u01/app/oracle/product/12.1.0/dbhome_1/lib)
      )
   )
Adjust $ORACLE_HOME/network/admin/tnsnames.ora 
sqlr =
   (DESCRIPTION=
      (ADDRESS= (PROTOCOL=TCP) (HOST=localhost) (PORT=1521))
      (CONNECT_DATA= (SID=sqlr))
      (HS=OK))
Restart listener to activate changes 
$ lsnrctl stop
$ lsnrctl start
$ lsnrctl status
 ...
Service "sqlr" has 1 instance(s).
  Instance "sqlr", status UNKNOWN, has 1 handler(s) for this service...
 ...

4. create and use database link in sqlplus

$ sqlplus sys as sysdba

> CREATE PUBLIC DATABASE LINK toydb CONNECT TO "public" IDENTIFIED BY "secret" USING 'sqlr';

Database link created.

> select product_no,product_name,price from products@toydb;

PRODUCT_NO         PRODUCT_NAME                                          PRICE
------------------ ------------------------------------------------ ----------
A00003             POKER DICE SET                                          125
A00008             POSTER PAINTS                                            95
A00009             COLOURING BOOK                                           65
A00001             PACK OF CARDS                                            75
A00010             ERASER GIFT SET                                         185
A00002             LUDO SET                                               1250
A00005             15" PINK RABBIT                                        1745
A00007             SET OF CRAYONS                                          175
A00004             12" TEDDY BEAR                                         1525
A00006             SET OF PANDAS                                          2500

10 rows selected.

Helpful resources