Oracle
Version:
2023.1
Last modified: May 17, 2023
| Connection Type | ODBC, OLEDB, and OCI (64-bit) |
| Type of Support | Read & Write; In-Database |
| Validated On | Database Version: 12c and 19c |
Driver Details
Bulk write support is available with 64-bit connections for standard and in-database workflows.
In-Database processing requires 64-bit database drivers.
OCI basic package must be installed in order for the Simba ODBC driver to work.
Alteryx Tools Used to Connect
Standard Workflow Processing
- Version 10.02.00.01 64-bit OLEDB provider had issues with FIXED DECIMAL. Alteryx recommends using the 11g or newer driver.
- Oracle Connection uses the Oracle Call Interface (OCI) library to connect to the Oracle database. This library is part of the Client software installed with the database.
- Oracle OLEDB is not supported in the In-Database tools. Use ODBC or OCI.
- Support only for username and password authentication.
Stored Procedures
Alteryx supports multi-line, multiple SQL statements for Oracle Stored Procedures. The Alteryx engine is not used when executing a stored procedure. This execution happens entirely on the database server.
Access Stored Procedures
- Drag the Input, Output, or Run Command tool onto the Alteryx Designer canvas.
- Select the Connect a File or Database menu, and then select the Oracle database connection.
- In the Input Data Configuration window, select Pre SQL Statement or Post SQL Statement. You can enter one or more statements in the SQL Editor tab, or you can select from a list of Stored Procedures.
- In the Configure Pre SQL Statements or Configure Post SQL Statements window and Stored Procedures tab, view the Parameter, Datatype, and then enter the Value for each stored procedure parameter for OCl, ODBC, and OLEDB connection. Select the desired Stored Procedure and select OK.
- Selecting a stored procedure overwrites what is in the field. Stored procedures cannot be accessed for an Oracle connection from the Table or Query file format options menu.
- Only stored procedures with IN parameters are listed.
- Oracle data types may differ in how they map to Alteryx, depending on whether the connection is OLI, ODBC, or OLEDB.
- String parameters must have single quotes around the entered value.
- The SQL syntax for calling the stored procedure in Oracle should be in the format of CALL "sproc_name" (optional parameter1, optional parameter2, ...).
- When used in an Input, the Value must represent a value within the database.
- You can select whether you want the SQL Editor or Stored Procedures tab to open by default. The default tab is Stored Procedures if there is no text in the SQL Editor tab.
Unsupported Data Types
The following data types are unsupported and cannot be entered into the Stored Procedures Parameter value field:
- Binary types: RAW, LONG RAW, and BLOB
If your stored procedure contains an unsupported data type, then:
- Designer sets the data type to unsupported.
- The value column in the data grid becomes read-only and the OK button disabled, indicating that the stored procedure can't be run.
Limitations
Database Version: 10g and 11g
- Maximum Columns In Table: 1000
- Maximum Column Name Length: 30 characters
- Maximum Row Size: 4GB (does not include LOB size)
- Variable length strings may return NULL instead of Empty.
ODBC (Client Versions: 10.02.00.03 (Vista), 10.02.00.01 (32- and 64-bit), 11.01.00.06 (32- and 64-bit)), 11.01.00.06 (32- and 64-bit))
- All versions–INT64 is transported as a Fixed Numeric. SQL_C_BIGINT is not supported.
- Double is transported as BINARY_DOUBLE.
- Float is transported as BINARY_FLOAT.
OLEDB (Client Versions: 10.02.00.03 (Vista), 10.02.00.01 (32- and 64-bit), 11.01.00.06 (32- and 64-bit))
- 10.02.00.01 64 bit–issues with FIXED DECIMAL
- Double is transported as BINARY_DOUBLE.
- Float is transported as BINARY_FLOAT.
Oracle Call Interface (Client Versions: 10.02.00.03 (Vista), 10.02.00.01 (32- and 64-bit), 11.01.00.06 (32- and 64-bit))
- Int64 is transported as a Double.
- Bool is transported as a String(1).
- DateTime is transported as a String(19).
- Double is transported as BINARY_DOUBLE.
- Float is transported as BINARY_FLOAT.
Spatial data retrieval supported only with the OCI Driver.
Retrieval of Spatial data is unavailable using the ODBC and OleDB driver. To use spatial data with Oracle database, connect using the OCI driver.
Known Issues
- A single workflow with ODBC connections cannot contain either OleDB or OCI connections using the same driver.
- OCI is its own API that’s separate from ODBC and uses specific call to the connector so the behavior may not match with ODBC.
- If you are connecting to an Oracle database that contains Unicode® encoded data, select Force SQL WChar Support in the Input Data Tool Configuration window. Character columns are then treated as SQL_WCHAR, SQL_WVARCHAR, or SQL_WLONGVARCHAR.
- Connection to Oracle database fails with Connection Error when the password contains or starts with special characters. Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character.
To avoid the error when making the connection, enclose the password in double-quotes.