This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Unable to find the dll: "OCI.dll" Within Alteryx this error will be prompted due to a mismatch between 32/64 bit drivers and the Alteryx bit version. This error may also appear if you do not have the drivers installed. One way to troubleshoot this is to find out what drivers you have installed! You can use an input tool in the Alteryx Designer, go to Other Databases and select either ODBC/OleDB (64-bit) or 32-Bit Database Connections -> ODBC/OleDB. In the next pop out window it will populate with the available drivers for those connections. If you have no drivers in this pop out window this means you do not havethe correct drivers for 32 bit or 64 bit connection dependent upon which one you chose. Links to drivers are available from within individual data source pages here. This will take you away from the Alteryx website and onto the driver provider’s pages. Please consult with your IT to find the correct drivers for your environment.
Importing Thai data from Oracle Database using Oracle ODBC driver is displaying the Thai characters as garbled characters in Alteryx. This can be addressed by setting a parameter in the driver to unconditionally change the data type to support Unicode.
Alteryx allows you to connect to many different types of data sources. One type of data source you can connect to is a database. Examples of databases are SQL Server, Oracle, Teradata, and MongoDB; amongst many others. There are several connection methods to connect to database sources including ODBC, OleDB, or natively.
Characters that are not on a standard English keyboard may need translation into Unicode or a language-specific code page for Designer and database drivers to read them correctly.
Characters with incorrect encoding will often appear as boxes or question marks in the Designer Results screen and error messages.
Unicode characters take more bytes than English ASCII characters. Changing the column type and increasing the column size may be needed. In Designer, the column size is the number of characters, not the number of bytes.
Issue Users can experience the below error message in Alteryx Designer OCIEnvCreate Error: No error handle available to report exact OCI_ERROR Environment Alteryx Designer Drivers : Oracle Instant Client Diagnosis This error will occur on different occasions When attempting to connect to Oracle using the Instant client When attempting to publish workflows using the Instant Client Cause This issue is caused by any of the following : The Oracle client software has been installed in a version < 8.1.7 The ORACLE_HOME variable on the machine is pointing to the wrong directory There is a Run.. As User on the Gallery and it doesn't have the Oracle client installed and/or the appropriate rights on the Oracle Instant Client i nstallation folder For each of these causes there is a solution outlined below. Solution A Download the client directly from Oracle ( Instant Client Downloads for Microsoft Windows (x64) 64-bit ) Follow the installation instructions provided by them at the bottom of the page Restart Designer Solution B Locate the ORACLE_HOME folder on the machine, this is typically : the "Ora92" folder under the "Oracle" folder (i.e. D:\Oracle\Ora92) in Oracle9iR2 or the "Client_1" folder under the "Oracle\product\10.1.0" folder (i.e. D:\Oracle\product\10.1.0\Client_1) in Oracle10gR1 or the "Db_1" folder under the "Oracle\product\10.1.0" folder (i.e. D:\Oracle\product\10.1.0\Db_1) in Oracle10gR1 Locate the ORACLE_HOME environment variable in the registry editor of the machine If this variable it doesn't match the path, change it to the right one Restart Designer Solution C Log on to Windows as either : a user with Administrator privileges the Run As.. User set in the Alteryx System Settings the Service Account behind Alteryx Service Start Windows File Explorer and navigate to the ORACLE_HOME folder. Choose properties on the ORACLE_HOME folder. Click the Security tab of the Properties window. Click on Authenticated Users item in the Name list. Un-check the Read and Execute box in the Permissions list under the Allow column. Re-check the Read and Execute box under the Allow column. Click the Advanced button : in the Permission Entries verify that Authenticated Users are listed with permission: Read & Execute, and Apply To: This folder, subfolders and files. If not, edit that line and make sure that Apply To drop-down box is set to This folder, subfolders and files. This should already be set properly but it is important that you verify it. Click the OK button until you close out all of the security properties windows. The cursor may present the hour glass for a few seconds as it applies the permissions you just changed to all subfolders and files. Reboot, to assure that the changes have taken effect. Re-execute the application and it should now work. Additional Resources Database Issues – Working with Alteryx Customer Support Engineers (CSEs) Too much connection data for an OCI driver may cause an OCIEnvCreate Error As always, don't hesitate to contact us over at Customer Support if you run into any trouble.
How To: Add Exception Handling to Oracle Pre or Post SQL
Exception handling may be needed so that a scheduled workflow continues even if a pre or post Oracle SQL statement has an error.
Since PL/SQL currently is not supported in the SQL Editor, you will need to create a stored procedure for exception handling and call the stored procedure in the pre or post SQL section.
This example shows how to create a stored procedure that handles exceptions for a delete table statement when the table was previously removed and does not exist. (Oracle currently does not have an IF EXISTS option.)
Adding a parameter for the table name is recommended so that one stored procedure can be reused for any table.
Note: You will need to create procedure permission on the Oracle database, contact a DBA if you are lacking this permission.
Use this format in your SQL editor for the stored procedure. Notice a space is added at the end of the SQL command ‘DROP TABLE ’ followed by || for concatenation and the parameter name used as a place holder for the table name.
Once the stored procedure exists on the database, you can reference it in the Pre SQL or Post SQL sections of the Input Data or Output Data Tool. Click on the Ellipsis button to open the section.
On the Stored Procedures tab, just highlight the stored procedure name on the left, and then in the Value text box, type in the table name surrounded by single quotes.
Alternately, you can call the stored procedure from the SQL Editor tab, using this format:
Input Data Tool
Output Data Tool
Too much connection data for an OCI driver may cause an OCIEnvCreate Error
An OCI error may occur when running a workflow from a network location with many OCI inputs: OCIEnvCreate Error: No error handle available to report exact OCI_ERROR.
A Designer workflow with Input Data tools having OCI connections to an Oracle database.
Product - Alteryx Designer downloads.alteryx.com Product - Oracle Instant Client for validated versions see: Supported Data Sources Oracle download site: Oracle Instant Client downloads
Decrease the total connection data that OCI driver in the Oracle Client has to process for the workflow. If the workflow is accessed from a network drive, try putting the workflow in a folder close to the root of the driver, rather than in a long folder directory, or move the workflow to your workstation instead of a network directory.
If there are many OCI connections in the workflow, see if the same data could be imported with fewer connections.
The OCI driver is recording the network location of the workflow. Also, if the workflow contains multiple OCI inputs, the Oracle Client OCI driver appears to compile all of them together to create the workflow connections.
A maximum limit of connection data for an OCI driver can be reached In testing, we found that a maximum limit of connection data could be reached, and then the error would occur. The workflow would run fine with multiple OCI connections if the workflow was located on the workstation. However, when running the same workflow from a network drive, the length of the folder path would determine whether or not it would error out. Similarly, adding or removing more OCI inputs would have the same effect. A limit would be reached, and an error would occur beyond that limit.
Oracle Call Interface Programmer's Guide OCIEnvCreate()