Alteryx Workflow with Oracle database

[anupgupta12]

Hi There,

I have to build a workflow with oracle database SQL query. I am connected to Database. I have a dataset which is input for SQL query in database. How do I connect my input to database query?

Process flow example:-

<Input file> - <Connect to database > - <Run Query with input file> - <Write output >

[BrandonB]

You may want to look at the In-DB functionalities if you are using local sources in conjunction with your database tables. The data stream in functionality allows you to stream local data up to a temporary table. 

Alternatively, you may want to look at the Dynamic Input In-DB tool if you are looking to dynamically swap out parts of a where clause. 

[john_watkins]

The In-DB Tools that stream data to the database and output tables require create table permissions.  Once you are "In-DB" you are semi-stuck there with those tools only until you create a final output that can be streamed back to Alteryx or saved off as a table in your Oracle database.    We have done this on our team when we may have a specific list of part numbers or ID's over the 4000 limit in the IN clause.  

A general cookie cutter approach if you must use In-Db:

1. Create the dataset you need to "merge" with your Oracle data using normal Alteryx tools.

2. Use the "Data Stream In" tool from the In-Db toolset which allows you to create a temp or actual table within oracle. (Requires Create/Drop table)

3. Use a Connect InDB tool for the query that comes from Oracle.

4. Use Join/Formula/Select In-DB tools from there to work with the data.   There are no auto-browse tools, so if you want to see output remember to add them where necessary.

5. IF you need to take the data back to the normal Alteryx toolset, use the Data Stream Out.