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.
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()
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 have the 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.
Error: "system.data.oracle client requires 8.1.7 or greater"
Please bear in mind this error is not related to Alteryx but to Oracle. This error message is returned from the Oracle Database, therefore please check with your DBA about the configuration and set up of your machine and server before reaching out to email@example.com. However, we are always happy to help so here are a few troubleshooting tips!
Which database connection did you use?
If you tried using the ODBC connection, try using the OCI (New Oracle Connection). If you do not know what the TNS server name it can be found in the TNSnames.ORA file. Your DBA will also know this.
What permissions do you have?
Go to the Oracle Client folder.
2- Right Click on the folder.
3- On security Tab, Add “Authenticated Users” and give this account Read & Execute permission.
4- Apply this security for all folders, Subfolders and Files (IMPORTANT).
5- Don’t Forget to REBOOT your Machine; if you forgot to do this you will still face the same problem unless you restart your machine.
*Before changing any permission settings please check with your IT
How many instances of Oracle do you have installed?
Oracle may find it difficult to navigate and select the correct version if it has more than one version installed. If you only need one of them it is better just to remove them all and reinstall the one that is needed.
Do you have the correct drivers installed?
One way to check to see if you have the right drivers installed is to set up the DSN connection through Alteryx, navigate to the ODBC admin and see if any drivers populate for either the 32 or 64 bit connections.
Do you have one of the supported client versions?
Please see the Technical Specifications
Please bear in mind often this error is not related to Alteryx but to Oracle. This error message is returned from the Oracle Datbase side and therefore please check with your DBA about the configuration and set up of your client before reaching out to Alteryx Support
Client Service Support Engineer
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.
Have you ever wanted to enrich your marketing data through improved data blending and analytics. Now is your chance by connecting to Oracle Eloqua through Alteryx!
*This current connection is facilitated by a third party ODBC driver from DataDirect Connectors. DataDirect has a 30 day trial whereby the ODBC driver can be downloaded for free. This ODBC driver has not been certified by Alteryx and thus is not officially supported.
Setup an account on data direct: https://www.progress.com/odbc/oracle-eloqua
Follow the instructions on the site to download and install the driver.
The link will take you to the screen in the below screenshot.
Please click on the ‘Windows x64’ link in the ODBC drivers section.
This will prompt the download onto your machine, if you receive a security warning click on the on the driver download arrow on the right hand side and select ‘Keep’. *Please check with your IT/ Security protocol prior to doing this step.
Once downloaded click on the DataDirect Cloud Driver install.
This will open a new window, click next until you reach the below screenshot. In this window check ‘Create Default Data Source’, this will create a user DSN account in your ODBC admin automatically within Alteryx.
Click ‘next’, ‘Install’ and ‘Done’ on the following windows.
You can then open Alteryx and drag in an Input tool.
Click on the input tool and dropdown arrow to the database connection folder. Depending on the bit version of your machine, you can either select ‘Database Connection’ for x64 bit or 32 bit Database Connection for x32 bit.
In the next window select ‘New ODBC Connection’ (Below screenshot).
You should then see the Data Source Name ‘DataDirect Cloud 2.0 (User)’
Click on the ODBC Admin button, which will open the ODBC Data Source Administrator. Highlight the ‘DataDirect Cloud 2.0’ Name and the hit Configure.
In the General Tab enter the following information (You should only have to add the Database Name. This would have been created in the Data Direct account).
You can then press ‘Test connect’ which will prompt the ‘Logon to Cloud’ window. In this window enter the ‘data direct cloud username’ and Password. Hit OK after you have done this. You should see ‘Connection established!’
On the security tab back in the ODBC Cloud Driver Setup, please enter in the Authentication section ‘Data Direct Cloud User Name’ as the username. You can leave the logon domain and Data source authentication blank.
If you then click ‘Apply’ then ‘OK’ on the ODBC Cloud Driver Setup
You should still see the original ODBC Connection with the data source as ‘DataDirect Cloud 2.0 (User)’. In this windows just type in your Password for DataDirect, you can leave the username blank (we included this in the security tab in a previous step).
This should then prompt you to ‘Choose Table or Specify Query’ within the visual Query Builder and now you can use Alteryx to make the most of your Eloqua Database!!!
This directory was generated by the Alteryx install script, and contains an instance of the Oracle Instant Client. Oracle Instant Client is a collection of installed Oracle Database libraries for connecting Alteryx Designer to local or remote Oracle Databases. There’s no need for tnsnames.ora file.