odbc oracle notes

ODBC Oracle Notes

Summary for users

Use #:quirks (no-c-bigint)

No parameter types (unknown) -- #:strict-parameter-types? #t gives all params type varchar.

Notes to self

Fetching DECIMAL/NUMERIC fields works using SQL_ARD_TYPE.

---

Setting up a testing environment

Whenever using ODBC driver:

• export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib (or 12.2, for newer version)

Whenever using oracle software directly, must set ORACLE_HOME env var:

• export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe

Setup for Centos 7.4

• yum install bc nmap-ncat
• run rpm on downloaded file
• Post-install configuration: as root /etc/init.d/oracle-xe configure
  • 8080, 1521, (standard password, but letters only)

Installation automatically creates database XE, user system, password entered in installation

• connect with .../sqlplus system@XE
• https://stackoverflow.com/questions/9534136/how-to-create-a-new-database-after-initally-installing-oracle-database-11g-expre

Create a new user:

• https://stackoverflow.com/questions/27357493/how-to-create-new-database-in-oracle-11g-express-edition
• create user ryan identified by password; -- note, password not quoted!
• grant dba, resource, connect to ryan;

Misc other links:

• https://www.linkedin.com/pulse/simplified-oracle-11g-database-command-line-linux-basic-nelson
• https://www.thegeekstuff.com/2017/01/create-database-oracle/comment-page-1/
• http://www.oracle.com/technetwork/developer-tools/apex/documentation/doc-093691.html
• https://docs.oracle.com/cd/E17781_01/admin.112/e18585/toc.htm#XEGSG101
• http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r1/prod/install/dbinst/dbinst.htm
• https://docs.oracle.com/cd/E18283_01/server.112/e10839/app_odbc.htm

Setup 12c

Failed because of X11 issues.

Reference

• https://www.howtoforge.com/tutorial/how-to-install-oracle-database-12c-on-centos-7/
• https://danielwestermann.com/2013/08/11/a-simple-script-to-automate-the-oracle-12c-setup/

ODBC Configuration

References:

• Client libs: http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html
• ODBC: http://www.oracle.com/technetwork/database/features/oci/odbc-ic-releasenotes-094306.html

Issue: cannot load driver library

• error: odbc-connect: [unixODBC][Driver Manager]Can't open lib '/usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1' : file not found; but it's there!
• discovered not executable, made executable, still didn't work
• searched: https://community.oracle.com/thread/2594875, https://www.centos.org/forums/viewtopic.php?t=58056
• unixODBC pkg doesn't provide /usr/lib64/libodbcinst.so.1 (it provides .so.2); so create soft link; fixed!

Now the following works:

• (odbc-driver-connect "DRIVER=Oracle11g;DBQ=localhost:1521/XE;UID=ryan;PWD=XXXX")
• refs: https://stackoverflow.com/questions/11098289/, https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-Oracle-from-RHEL-or-Centos
• but how to set up a DSN?

Issue: cannot connect with ODBC DSN

• fix from http://dbtricks.com/?p=55
  • set "TNS Service Name" to localhost/XE -- (maybe in general host[:port][/service_name], port default is 1521)
• The odbc.ini field corresponding to "TNS Service Name" is apparently ServerName
  • https://stackoverflow.com/questions/30537722/how-do-you-configure-unix-odbc-on-a-linux-server
  • https://stackoverflow.com/questions/36499998/connecting-oracle-with-unixodbc-tnsnet-service-name-is-incorrectly-specified
  • https://gerardnico.com/db/oracle/odbc_linux

Issue: tests fail with "[unixODBC][Oracle][ODBC][Ora]ORA-12516: TNS:listener could not find available handler with matching protocol stack"

• symptom of too many concurrent connections: https://community.oracle.com/thread/362226
• fix: increase processes; see http://oraegy.blogspot.cz/2012/11/how-to-increase-processes.html

---

Issues

Issue: SQL_C_[SU]BIGINT not supported

• https://docs.oracle.com/database/121/UNXAR/app_odbc.htm#UNXAR014
• added no-c-bigint quirk to work around

Issue: TIME type broken

• (prepare c "select time '12:34:56' as thing from dual") raises error: "bytes->string/utf-8: ending index is out of range; ending index: 11040..."
• SQLDescribeCol returns garbage size for column name, garbage typeid (varies run to run)

Issue: some tests failed because Oracle sometimes converts Integer (values/fields) to Decimal (and then Real?)

• https://stackoverflow.com/questions/24023318/oracle-returns-decimal-for-a-certain-number4-column
• https://stackoverflow.com/questions/1801608/select-1-from-in-oracle-returns-a-decimal-type
• adapted test code in a few places

Issue: numeric roundtrip test fails because parameter sent as SQL_C_DOUBLE.

• FIXME: add option to send exact as numeric (if exact? if ...?)

Issue: DATE type returned as sql-timestamp

• https://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLSPG004

Issue: sometimes quitting interactive racket hangs when connection is open

• the following interactions seem to reliably produce a hang-on-exit:

  > (begin (require db) (define c (dsn-connect 'ora)))
  > (define p (prepare c "select cast(17 as integer) from dual"))
  > (define p2 (prepare c "select cast(17 as integer) from dual"))
  

• seems to require at least two statements prepared (?)

• doesn't hang if only one statement allocated

• doesn't hang if connection disconnected (explicitly or via custodian shutdown)

• doesn't hang if statements freed explicitly with (send p finalize #t)

• gdb backtrace

  #0  0x00007f07354b5945 in pthread_cond_wait@@GLIBC_2.3.2 ()    from /lib64/libpthread.so.0
  #1  0x00007f0723691057 in SltsPrWrite ()    from /usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1
  #2  0x00007f0725a7ccdb in bccFreeProcess ()   from /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
  #3  0x00007f0725a8cb11 in _LibProc ()   from /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
  #4  0x00007f0725a8cce5 in finiSqora ()   from /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
  #5  0x00007f0725a24d12 in __do_global_dtors_aux ()   from /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
  #6  0x00007fffa665c480 in ?? () 
  #7  0x00007f0725a8cdb1 in _fini ()   from /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1