What is the difference between an oci and an odbc connection?

[jaipersr]

Hi does anyone know the differenece between an oci and odbc connection and when to use one versus the other?

[BrandonB]

OCI is a connection type specific to Oracle. This article does a good job explaining all of the different methods and how to configure each: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-to-Connect-to-an-Oracle-Databas... 

[SeanAdams]

Only two things to add to @BrandonB 's response are transportability and speed:

Transport:

two versions of ODBC - DSN and DSN-less (DSN = Data Source Name - it's a shortcut on your computer to the details of the database).    You can tell the difference when you look at the connection string - some will say odbc:dsn=myconnection

If you use a DSN connection then this will only run on your computer, or any computers that you explicitly set up an ODBC DSN - i.e. it's a bit of a pain.

OCI Connections and DSN-less ODBC are fully transportable (meaning that you can send the Alteryx canvas to someone else and as long as they have the drivers installed they can run them).    To run on the server, you need to use either OCI or DSN-less.

Speed:

In the old days, ODBC was slower - and database vendors recommended using their proprietary connections (like Native MS SQL; or OCI for Oracle) - but ODBC has been incrementally improved over time and has become almost equivalent in both speed and functionality in most ways.

How to pick:

We've found OCI to be super easy to use for Oracle DBs - for any DB other than Oracle work with ODBC or native drivers.