cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed

How to configure SAS/ACCESS Interface to ODBC on Linux with an ODBC driver from Oracle

Started ‎03-11-2016 by
Modified ‎03-11-2016 by
Views 9,464

Overview

This article is part of a series.  See Using SAS/ACCESS Interface to ODBC on UNIX Platforms for a listing of all articles in this series.

 

For important considerations and useful tools, see the “Quick Reference” section of the related article, How to configure SAS/ACCESS Interface to ODBC on UNIX Platforms.

 

See “Example” in this article for details about tool use.

 

Requirements

For this scenario, here is what you must already have before you can use SAS/ACCESS Interface to ODBC on Linux:

  • ODBC driver from Oracle
  • the latest version of the unixODBC driver manager

 

ODBC driver manager

SAS first looks for an ODBC driver manager when you submit SAS/ACCESS Interface to ODBC code on Linux.  The ODBC driver manager is always named LIBODBC.SO on Linux, no matter who provides the driver.

 

Environment variables

SAS finds the LIBODBC.SO ODBC driver manager by searching the directories that are specified in the LD_LIBRARY_PATH environment variable setting on Linux.

 

SAS uses the first 64-bit LIBODBC.SO component that it finds. Depending on your particular ODBC client configuration on Linux, the ODBC driver manager references either an ODBC.INI file or a combination of ODBC.INI and ODBCINST.INI files.  You can set environment variables such as ODBCINI and

 

Connections

After the ODBC driver manager finds the designated INI files, it references the stanza for the data source name (DSN) that you have specified in your SAS/ACCESS Interface to ODBC code. SAS/ACCESS uses this code to obtain connection specifics for the particular database to which you want to connect. For example, this code specifies the ODBC driver to use, the server where the database resides, the port, and the database name.

 

You can also use a DSN-less connection. In this case, you provide all connection specifics for the database in your SAS/ACCESS Interface to ODBC code instead of than relying on INI files for this information.

 

In a single SAS session on a UNIX platform, SAS on Linux can use only a single ODBC driver manager.  You must therefore find a single ODBC driver manager that works with all ODBC drivers for the ODBC data sources to which you need to connect when using SAS/ACCESS Interface to ODBC.

 

unixODBC driver managers

When you use DBMS vendor-provided or freeware ODBC drivers, you typically pair them with a freeware unixODBC driver manager.  A unixODBC driver manager works with most nonproprietary Linux ODBC drivers.

 

It is recommended that you use the most current version of the unixODBC driver manager.  Download a zipped tar file, unixODBC-x.x.x.tar.gz, for unixODBC from the unixODBC project home page.  (x.x refers to the most recent subrelease.)

 

When you use SAS/ACCESS Interface to ODBC with a unixODBC driver manager, you must use the most current version. Most Linux environments already have a unixODBC driver manager in place in a global location such as /usr/lib64.  However, it is typically a very old version such as Version 2.2.14.  SAS does not support using SAS/ACCESS Interface to ODBC with such old versions of the unixODBC driver manager.

 

Driver vendors and combinations

It is preferable to use an ODBC driver from an ODBC driver vendor such as Progress Software’s DataDirect because it includes a proprietary ODBC driver manager with one or more of the ODBC drivers. 

 

It is possible to have a combination of proprietary and freeware ODBC drivers in place. In such cases, it is sometimes necessary to use a proprietary ODBC driver manager with the proprietary ODBC driver with which it came and also use it with freeware ODBC drivers. You can also use a freeware unixODBC driver manager with both proprietary and freeware ODBC drivers.

 

Specific Oracle information

  • When using SAS/ACCESS Interface to ODBC with an ODBC driver from Oracle, you must have a current version of the driver. Oracle driver bugs were identified in the past when testing SAS/ACCESS Interface to ODBC on UNIX platforms with Oracle ODBC drivers.  Oracle has since fixed these in Oracle 12 and through a fixpack for Oracle 11.2.
  • Located in the $ORACLE_HOME/lib directory, the name of the Oracle ODBC driver is LIBSQORA.SO.x, where xx.x refers to the version number.
  • Oracle DSN stanzas in the ODBC.INI file cross-reference serviceable entries in the Oracle TNSNAMES.ORA file through the ServerName DSN option. This is how connections are picked up that are specific to the Oracle databases to which you need access.
  • You can use the Oracle SQLPLUS query tool to test the soundness of entries in the Oracle TNSNAMES.ORA file that is referenced by Oracle DSNs in the ODBC.INI file.
  • In addition to including the directory containing the Oracle ODBC driver in your LD_LIBRARY_PATH setting, you must also set the ORACLE_HOME and TNS_ADMIN environment variables. Set the TNS_ADMIN environment variable to the directory where the Oracle TNSNAMES.ORA file resides.  You must have a serviceable TNSNAMES.ORA file in place, and it must contain entries for the Oracle databases that you need to access.

 

Example

Here are the specific considerations for this example.

 

  • When you use a unixODBC driver manager, it is best to place a global ODBC.INI file in the unixODBC etc
  • Set Oracle and ODBC environment variables in the SASENV_LOCAL file in the !SASROOT/bin
  • A Linux startup script is used to set the needed Oracle and ODBC environment variables. Normally, the best place to set these in the SASENV_LOCAL file in the !SASROOT/bin directory because it ensures that they are set for whoever starts SAS, no matter how it is started.

 

To be sure all needed ODBC environment variables are set for the SAS session, submit this SAS code.

 

 

%macro doit(command);
  filename p pipe &command lrecl=32767;
  data _null_;                                                                 
  infile p; 
  input; 
  put _infile_; 
  run;                                                                         
%mend;

option LS=256;
%doit("set");

 

 

The example uses these tools:

 

  • ISQL
  • FILE
  • LDD
  • ODBCINST
  • SQLPLUS

 

Here is the log that shows how to get all of this working.

 

 

[sastac1@cumulus 7611203346_unixODBC_with_oracle12_driver]$ uname -a
Linux cumulus.unx.sas.com 2.6.32-131.6.1.el6.x86_64 #1 SMP Mon Jun 20 14:15:38 EDT 2011 
   x86_64 x86_64 x86_64 GNU/Linux
[sastac1@cumulus 7611203346_unixODBC_with_oracle12_driver]$ pwd
/saswork/sasswl/7611203346_unixODBC_with_oracle12_driver
[sastac1@cumulus 7611203346_unixODBC_with_oracle12_driver]$ ls
odbc.ini  startup_script

[sastac1@cumulus 7611203346_unixODBC_with_oracle12_driver]$ cat startup_script
export ODBCHOME=/saswork/sasswl/unixODBC2.3.2
export ODBCINI=/saswork/sasswl/7611203346_unixODBC_with_oracle12_driver/odbc.ini
export ORACLE_HOME=/TECHDBI/oracle/12c/product/12.1.0/client_1
export TNS_ADMIN=/etc
export LD_LIBRARY_PATH=$ODBCHOME/lib:$ORACLE_HOME/lib

[sastac1@cumulus 7611203346_unixODBC_with_oracle12_driver]$ . ./startup_script
[sastac1@cumulus 7611203346_unixODBC_with_oracle12_driver]$ echo $ODBCHOME
/saswork/sasswl/unixODBC2.3.2
[sastac1@cumulus 7611203346_unixODBC_with_oracle12_driver]$ echo $ODBCINI
/saswork/sasswl/7611203346_unixODBC_with_oracle12_driver/odbc.ini
[sastac1@cumulus 7611203346_unixODBC_with_oracle12_driver]$ echo $ORACLE_HOME
/TECHDBI/oracle/12c/product/12.1.0/client_1
[sastac1@cumulus 7611203346_unixODBC_with_oracle12_driver]$ echo $LD_LIBRARY_PATH
/saswork/sasswl/unixODBC2.3.2/lib:/TECHDBI/oracle/12c/product/12.1.0/client_1/lib
[sastac1@cumulus 7611203346_unixODBC_with_oracle12_driver]$ cat odbc.ini
[ODBC Data Sources]
Oracle12 = Oracle 12.1 ODBC driver

[Oracle12]
Driver=/TECHDBI/oracle/12c/product/12.1.0/client_1/lib/libsqora.so.12.1
Description=Oracle 12.1 ODBC driver
ServerName=ORA12EP

[sastac1@cumulus 7611203346_unixODBC_with_oracle12_driver]$ cd $ODBCHOME
[sastac1@cumulus unixODBC2.3.2]$ pwd
/saswork/sasswl/unixODBC2.3.2
[sastac1@cumulus unixODBC2.3.2]$ ls
bin  etc  include  lib  share
[sastac1@cumulus unixODBC2.3.2]$ cd lib
[sastac1@cumulus lib]$ ls -l
total 2836
-rwxr-xr-x 1 sastac1 techsup     985 Mar 31  2014 libodbccr.la
lrwxrwxrwx 1 sastac1 techsup      18 Mar 31  2014 libodbccr.so -> libodbccr.so.2.0.0
lrwxrwxrwx 1 sastac1 techsup      18 Mar 31  2014 libodbccr.so.2 -> libodbccr.so.2.0.0
-rwxr-xr-x 1 sastac1 techsup  518134 Mar 31  2014 libodbccr.so.2.0.0
-rwxr-xr-x 1 sastac1 techsup     997 Mar 31  2014 libodbcinst.la
lrwxrwxrwx 1 sastac1 techsup      20 Mar 31  2014 libodbcinst.so -> libodbcinst.so.2.0.0
lrwxrwxrwx 1 sastac1 techsup      20 Apr  7  2014 libodbcinst.so.1 -> libodbcinst.so.2.0.0
lrwxrwxrwx 1 sastac1 techsup      20 Mar 31  2014 libodbcinst.so.2 -> libodbcinst.so.2.0.0
-rwxr-xr-x 1 sastac1 techsup  502443 Mar 31  2014 libodbcinst.so.2.0.0
-rwxr-xr-x 1 sastac1 techsup     973 Mar 31  2014 libodbc.la
lrwxrwxrwx 1 sastac1 techsup      16 Mar 31  2014 libodbc.so -> libodbc.so.2.0.0
lrwxrwxrwx 1 sastac1 techsup      16 Mar 31  2014 libodbc.so.2 -> libodbc.so.2.0.0
-rwxr-xr-x 1 sastac1 techsup 1866716 Mar 31  2014 libodbc.so.2.0.0
-rwxr-xr-x 1 sastac1 techsup 1866716 Mar 31  2014 libodbc.so.2.0.0
[sastac1@cumulus lib]$ ldd libodbc.so
        linux-vdso.so.1 =>  (0x00007fff47bee000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f030755d000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f030733f000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f0306fab000)
        /lib64/ld-linux-x86-64.so.2 (0x0000003408c00000)
[sastac1@cumulus lib]$ cd ..
[sastac1@cumulus unixODBC2.3.2]$ pwd
/saswork/sasswl/unixODBC2.3.2
[sastac1@cumulus unixODBC2.3.2]$ ls
bin  etc  include  lib  share
[sastac1@cumulus unixODBC2.3.2]$ cd bin
[sastac1@cumulus bin]$ ./odbcinst -j
unixODBC 2.3.2
DRIVERS............: /saswork/sasswl/unixODBC2.3.2/etc/odbcinst.ini
SYSTEM DATA SOURCES: /saswork/sasswl/unixODBC2.3.2/etc/odbc.ini
FILE DATA SOURCES..: /saswork/sasswl/unixODBC2.3.2/etc/ODBCDataSources
USER DATA SOURCES..: /saswork/sasswl/7611203346_unixODBC_with_oracle12_driver/odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

[sastac1@cumulus bin]$ ./isql -v Oracle12 scott tiger
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from emp
+-----------+---------+-------------+-----------+------------+
| ID        | NAME    | DEPT        | SALARY    | HIREDON    |
+-----------+---------+-------------+-----------+------------+
| 100       | Thomas  | Sales       | 5000      |            |
| 200       | Jason   | Technology  | 5500      |            |
| 300       | Mayla   | Technology  | 7000      |            |
| 400       | Nisha   | Marketing   | 9500      |            |
| 500       | Randy   | Technology  | 6000      |            |
| 501       | Ritu    | Accounting  | 5400      |            |
+-----------+---------+-------------+-----------+------------+
SQLRowCount returns -1
6 rows fetched
SQL>
[sastac1@cumulus bin]$ cd /TECHDBI/oracle/12c/product/12.1.0/client_1/lib
[sastac1@cumulus lib]$ ls -l libsq* libcln*
lrwxrwxrwx 1 oracle dba       69 Jun 21  2013 libclntshcore.so -> /TECHDBI/oracle/12c/product/12.1.0/client_1/lib/libclntshcore.so.12.1
-rwxr-xr-x 1 oracle dba  6750183 Jun 21  2013 libclntshcore.so.12.1
lrwxrwxrwx 1 oracle dba       65 Jun 21  2013 libclntsh.so -> /TECHDBI/oracle/12c/product/12.1.0/client_1/lib/libclntsh.so.12.1
lrwxrwxrwx 1 oracle dba       12 Jun 21  2013 libclntsh.so.10.1 -> libclntsh.so
lrwxrwxrwx 1 oracle dba       12 Jun 21  2013 libclntsh.so.11.1 -> libclntsh.so
-rwxr-xr-x 1 oracle dba 55402705 Jun 21  2013 libclntsh.so.12.1
-rw-r--r-- 1 oracle dba        0 Feb  1  2013 libclntst12.a
-rw-r--r-- 1 oracle dba  1172076 Jan 30  2013 libsql12.a
-rw-r--r-- 1 oracle dba  2220318 Jan 14  2013 libsqlplus.a
-rw-r--r-- 1 oracle dba  1535672 Jan 14  2013 libsqlplus.so
-rw-r--r-- 1 oracle dba  4307172 Jan 30  2013 libsqora.so.12.1
[sastac1@cumulus lib]$ file libsqora.so.12.1
libsqora.so.12.1: ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamically linked, not stripped
[sastac1@cumulus lib]$ ldd libsqora.so.12.1
ldd: warning: you do not have execution permission for `./libsqora.so.12.1'
        linux-vdso.so.1 =>  (0x00007fffab7ff000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f591dfea000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f591dd65000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f591db48000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f591d92f000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f591d726000)
        libclntsh.so.12.1 => /TECHDBI/oracle/12c/product/12.1.0/client_1/lib/libclntsh.so.12.1 (0x00007f591aa43000)
        libodbcinst.so.2 => /saswork/sasswl/unixODBC2.3.2/lib/libodbcinst.so.2 (0x00007f591a82c000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f591a497000)
        /lib64/ld-linux-x86-64.so.2 (0x0000003408c00000)
        libnnz12.so => /TECHDBI/oracle/12c/product/12.1.0/client_1/lib/libnnz12.so (0x00007f5919d81000)
        libons.so => /TECHDBI/oracle/12c/product/12.1.0/client_1/lib/libons.so (0x00007f5919b3c000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007f591993b000)
        libclntshcore.so.12.1 => /TECHDBI/oracle/12c/product/12.1.0/client_1/lib/libclntshcore.so.12.1 (0x00007f59193eb000)
[sastac1@cumulus lib]$ echo $TNS_ADMIN
/etc
[sastac1@cumulus lib]$ cat /etc/tnsnames.ora
ORA12EP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orarac12c)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora12ep)
    )
  )

ORA12C =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lax94t03.unx.sas.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ora12c)
    )
  )

ORA11G =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oraclev11.unx.sas.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ora11g)
    )
  )

ORAUTF8 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbivmlnx4.unx.sas.com)(PORT=1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orautf8)
    )
  )

[sastac1@cumulus lib]$ cd ..
[sastac1@cumulus client_1]$ ls
assistants   dc_ocm         inventory  network      ord      rdbms         ucp
bin          deinstall      javavm     nls          oui      relnotes      usm
cdata        diagnostics    jdbc       odbc         owm      root.sh       utl
cfgtoollogs  dmu            jdk        olap         perl     slax          wwg
clone        has            jlib       OPatch       plsql    sqldeveloper  xdk
crs          hs             jpub       opmn         precomp  sqlj
css          install        ldap       oracore      QOpatch  sqlplus
cv           instantclient  lib        oraInst.loc  racg     srvm
[sastac1@cumulus client_1]$ cd bin
[sastac1@cumulus bin]$ ls sqlplus
sqlplus
[sastac1@cumulus bin]$ ./sqlplus scott/tiger@ORA12C

SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 13 14:56:37 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Last Successful login time: Fri Jan 30 2015 10:03:24 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from emp
  2  ;

EMPNO  ENAME   JOB       MGR   HIREDATE   SAL   COMM    DEPTNO
------ ------- --------- ----- ---------  ----- ------  ------
7369   SMITH   CLERK     7902  17-DEC-80   800            20
7499   ALLEN   SALESMAN  7698  20-FEB-81  1600   300      30
7521   WARD    SALESMAN  7698  22-FEB-81  1250   500      30

EMPNO  ENAME   JOB       MGR   HIREDATE   SAL   COMM    DEPTNO
------ ------- --------- ----- ---------  ----- ------  ------
7566   JONES   MANAGER   7839  02-APR-81  2975            20
7654   MARTIN  SALESMAN  7698  28-SEP-81  1250  1400      30
7698   BLAKE   MANAGER   7839  01-MAY-81  2850            30

EMPNO  ENAME   JOB       MGR   HIREDATE   SAL   COMM    DEPTNO
------ ------- --------- ----- ---------  ----- ------  ------
7782   CLARK   MANAGER   7839  09-JUN-81  2450            10
7788   SCOTT   ANALYST   7566  19-APR-87  3000            20
7839   KING    PRESIDENT       17-NOV-81  5000            10

EMPNO  ENAME   JOB       MGR   HIREDATE   SAL   COMM    DEPTNO
------ ------- --------- ----- ---------  ----- ------  ------
7844   TURNER  SALESMAN  7698  08-SEP-81  1500     0      30
7876   ADAMS   CLERK     7788  23-MAY-87  1100            20
7900   JAMES   CLERK     7698  03-DEC-81   950            30

EMPNO  ENAME   JOB       MGR   HIREDATE   SAL   COMM    DEPTNO
------ ------- --------- ----- ---------  ----- ------  ------
7902   FORD    ANALYST   7566  03-DEC-81  3000            20
7934   MILLER  CLERK     7782  23-JAN-82  1300            10

14 rows selected.
SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[sastac1@cumulus bin]$ cd /saswork/sasswl/7611203346_unixODBC_with_oracle12_driver
[sastac1@cumulus 7611203346_unixODBC_with_oracle12_driver]$ ls
odbc.ini  startup_script
[sastac1@cumulus 7611203346_unixODBC_with_oracle12_driver]$ ln -s /TECH/GOLDEN/RC/LAX.V9.4.M2.09FY6M/install/SASFoundation/9.4/sas sas
[sastac1@cumulus 7611203346_unixODBC_with_oracle12_driver]$ ls -l
total 8
-rw-r--r-- 1 sastac1 techsup 474 Sep  3 14:48 odbc.ini
lrwxrwxrwx 1 sastac1 techsup  64 Feb 13 15:04 sas -> /TECH/GOLDEN/RC/LAX.V9.4.M2.09FY6M/install/SASFoundation/9.4/sas
-rw-r--r-- 1 sastac1 techsup 271 Sep  1 14:10 startup_script
[sastac1@cumulus 7611203346_unixODBC_with_oracle12_driver]$ ldd /TECH/GOLDEN/RC/LAX.V9.4.M2.09FY6M/install/SASFoundation/9.4/sasexe/sasodb
/TECH/GOLDEN/RC/LAX.V9.4.M2.09FY6M/install/SASFoundation/9.4/sasexe/sasodb: /saswork/sasswl/unixODBC2.3.2/lib/libodbc.so: no version information available (required by /TECH/GOLDEN/RC/LAX.V9.4.M2.09FY6M/install/SASFoundation/9.4/sasexe/sasodb)
        linux-vdso.so.1 =>  (0x00007ffffc9ff000)
        libodbc.so => /saswork/sasswl/unixODBC2.3.2/lib/libodbc.so (0x00007f7d1554d000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f7d15321000)
        libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f7d150ea000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f7d14ee6000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f7d14c61000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f7d148cd000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f7d146b7000)
        /lib64/ld-linux-x86-64.so.2 (0x0000003408c00000)
        libfreebl3.so => /lib64/libfreebl3.so (0x00007f7d14454000)
[sastac1@cumulus 7611203346_unixODBC_with_oracle12_driver]$ ./sas -nodms

NOTE: Copyright (c) 2002-2012 by SAS Institute Inc., Cary, NC, USA.
NOTE: SAS (r) Proprietary Software 9.4 (TS1M2)
      Licensed to 9.4 M2 14w32 - SAS Studio, Site 70068118.
NOTE: This session is executing on the Linux 2.6.32-131.6.1.el6.x86_64 (LIN
      X64) platform.

.
.  (skipping some output)
.

NOTE: SAS initialization used:
      real time           0.17 seconds
      cpu time            0.02 seconds

  1? libname oratest1 odbc user=scott pw=tiger dsn="Oracle12";

NOTE: Libref ORATEST1 was successfully assigned as follows:
      Engine:        ODBC
      Physical Name: Oracle12

2? proc datasets library=oratest1;

NOTE: Due to the PRESERVE_TAB_NAMES=NO libname option setting, 6 table(s)
      have not been displayed/returned.

Directory

Libref         ORATEST1
Engine         ODBC
Physical Name  Oracle12
Schema/Owner   SCOTT


                                          DBMS
                                  Member  Member
#  Name                           Type    Type

  1  AD_ACCOUNT                    DATA    TABLE
  2  ALPHBET                       DATA    TABLE
  3  ALSUBE                        DATA    TABLE
  4  ALSUBE2                       DATA    TABLE
  5  APOLD                         DATA    TABLE
  6  BD001D14                      DATA    TABLE
  7  CHANGE                        DATA    TABLE
  8  CLASS                         DATA    TABLE
  9  CWDBINARY                     DATA    TABLE
 10  D1                            DATA    TABLE
 11  D11                           DATA    TABLE
 12  D12                           DATA    TABLE
 13  D13                           DATA    TABLE
 14  D14                           DATA    TABLE
 15  D15                           DATA    TABLE
 16  D16                           DATA    TABLE
 17  D2                            DATA    TABLE
 18  D2TIME                        DATA    TABLE
 19  D3                            DATA    TABLE
 20  D4                            DATA    TABLE
 21  D5                            DATA    TABLE
 22  D6                            DATA    TABLE
 23  DATAFB1                       DATA    TABLE
 24  DATAFB2                       DATA    TABLE
 25  DEPT                          DATA    TABLE
 26  DTSX                          DATA    TABLE
 27  EMP                           DATA    TABLE
 28  EMPLOYEE                      DATA    TABLE
 29  FOO                           DATA    TABLE
 30  HASH                          DATA    TABLE
 31  HITER                         DATA    TABLE
 32  ICU                           DATA    TABLE
 33  ID                            DATA    TABLE
 34  INCOMES_T                     DATA    TABLE
 35  INDS                          DATA    TABLE
 36  INP                           DATA    TABLE
 37  INTRTREE                      DATA    TABLE
 38  INVESTMENT                    DATA    TABLE
 39  LZHANG1                       DATA    TABLE
 40  MASTER                        DATA    TABLE
 41  MATH_P                        DATA    TABLE
 42  MYDAT                         DATA    TABLE
 43  MYDATA                        DATA    TABLE
 44  MYTEST                        DATA    TABLE
 45  NEWVC                         DATA    TABLE
 46  NEWVC_COPY                    DATA    TABLE
 47  NEWVC_COPY2                   DATA    TABLE
 48  NEWVC_COPY3                   DATA    TABLE
 49  NEWVC_COPY4                   DATA    TABLE
 50  ONE                           DATA    TABLE
 51  OUTDATA                       DATA    TABLE
 52  OUTDATA1                      DATA    TABLE
 53  OUTDATA2                      DATA    TABLE
 54  P                             DATA    TABLE
 55  PACKAGE_A                     DATA    TABLE
 56  PACKAGE_B                     DATA    TABLE
 57  PKG                           DATA    TABLE
 58  PKGA                          DATA    TABLE
 59  PKGTEST                       DATA    TABLE
 60  PKGTEST2                      DATA    TABLE
 61  PPLOUT08                      DATA    TABLE
 62  PPOUTA                        DATA    TABLE
 63  PPOUTB                        DATA    TABLE
 64  RAW12CTAB_COPY                DATA    TABLE
 65  RAWTAB                        DATA    TABLE
 66  RBUFFTEST1                    DATA    TABLE
 67  RESULTS1_T                    DATA    TABLE
 68  RESULTS2_T                    DATA    TABLE
 69  ROWNUM_ORDER_TEST             DATA    TABLE
 70  SASDS_RAW                     DATA    TABLE
 71  SAS_WF_OPERAND                DATA    TABLE
 72  SCORE_T                       DATA    TABLE
 73  SDKTXC                        DATA    TABLE
 74  SWITCH1                       DATA    TABLE
 75  T1                            DATA    TABLE
 76  T4_ONE                        DATA    TABLE
 77  TD                            DATA    TABLE
 78  TEMP                          DATA    TABLE
 79  TEST1                         DATA    TABLE
 80  TEST1094095_AS_DOMAINS        DATA    TABLE
 81  TEST1094095_AS_LOGINS         DATA    TABLE
 82  TEST1094095_META_DOMAINS      DATA    TABLE
 83  TEST1094095_META_LOGINS       DATA    TABLE
 84  TEST1094095_X_AS_LOGINS_N     DATA    TABLE
 85  TEST1094095_X_DOMAIN_MAP      DATA    TABLE
 86  TEST1094095_X_DOMAIN_MAP_ALL  DATA    TABLE
 87  TEST1094095_X_LOGIN_MAP       DATA    TABLE
 88  TEST1094095_X_MS_LOGINS_N     DATA    TABLE
 89  TEST1094095_X_USER_MAP        DATA    TABLE
 90  TEST11                        DATA    TABLE
 91  TEST111                       DATA    TABLE
 92  TESTDATA                      DATA    TABLE
 93  TESTID                        DATA    TABLE
 94  TESTIT                        DATA    TABLE
 95  TESTTAB                       DATA    TABLE
 96  TESTTABLE                     DATA    TABLE
 97  TH                            DATA    TABLE
 98  THD                           DATA    TABLE
 99  THR                           DATA    TABLE
100  TMP                           DATA    TABLE
101  TSDAT30525                    DATA    TABLE
102  TSTGBY                        DATA    TABLE
103  TSTINFORMAT                   DATA    TABLE
104  T_ONE                         DATA    TABLE
105  VARNAME                       DATA    TABLE
106  VXD_VARCHAR                   DATA    TABLE
107  VXD_WBMFIQ8                   DATA    TABLE
108  WBMFIQ8                       DATA    TABLE
109  WBMFIQ82                      DATA    TABLE
110  WQFSCFP                       DATA    TABLE
111  WQFSCFP2                      DATA    TABLE
112  X                             DATA    TABLE
113  XYZZY                         DATA    TABLE
114  Y                             DATA    TABLE
115  ZHANG2                        DATA    TABLE

  3? quit;

NOTE: PROCEDURE DATASETS used (Total process time):
      real time           33.51 seconds
      cpu time            0.04 seconds

  4? data work.test1;
  5? set oratest1.emp;
  6? run;

NOTE: There were 6 observations read from the data set ORATEST1.EMP.
NOTE: The data set WORK.TEST1 has 6 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           10.75 seconds
      cpu time            0.01 seconds

  7? proc print data=work.test1;
  8? run;

                                The SAS System                               1
                                               17:03 Friday, February 13, 2015

Obs     ID    NAME          DEPT               SALARY                 HIREDON

 1     100    Thomas        Sales               5000                        .
 2     200    Jason         Technology          5500                        .
 3     300    Mayla         Technology          7000                        .
 4     400    Nisha         Marketing           9500                        .
 5     500    Randy         Technology          6000                        .
 6     501    Ritu          Accounting          5400                        .

NOTE: There were 6 observations read from the data set WORK.TEST1.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           2.42 seconds
      cpu time            0.00 seconds

  9? endsas;

NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414
NOTE: The SAS System used:
      real time           3:34.45
      cpu time            0.11 seconds

[sastac1@cumulus 7611203346_unixODBC_with_oracle12_driver]$

 

 

0 Likes
Comments
SDV
‎04-25-2017 05:53 PM
‎04-25-2017 05:53 PM

We have unixODC configured to Connect to Oracle 11g and have successfully tested connecting to our Oracle database using isql.  We are able to select rows from tables successfully.

 

We have SAS/Access to ODBC licensed, we added the setting of environmental variables to the sasenv_local file and have confirmed they are set correctly.  We can successfully issue a libname statement and then run:

 

proc datasets library=MyOraODBC;      * where MyOraODBC is the libref for our ODBC library;

run;

 

But, when we try to access a table in the library we get an error message that the table does not exist even though it was listed as a table by proc datasets, and we were able to query the table from isql.

 

Do you have any ideas or suggestions on where to look for the problem?

 

Thanks!

0 Likes
karthic2612
‎08-11-2018 12:01 AM
‎08-11-2018 12:01 AM
Please give me some explanation for How to configure SAS/ACCESS Interface to ODBC on Linux with an ODBC driver from Teradata also.
0 Likes
nishanth
‎09-06-2018 05:16 PM
‎09-06-2018 05:16 PM

Hi Karthic,

 

I work for Progress DataDirect. As noted in the article above, you can use the DataDirect drivers. Here is a link: https://www.progress.com/odbc/teradata . Let me know if you have any questions.

0 Likes
2015eva
‎06-18-2022 01:26 PM
‎06-18-2022 01:26 PM

Please give me some explanation for How to configure SAS/ACCESS Interface to ODBC on Linux with an ODBC driver from Oracle also. (Viya 4)

0 Likes
Version history
Last update:
‎03-11-2016 09:34 AM
Updated by:
Community Manager
Contributors

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Labels
Article Tags