Issues with Oracle stored procedure and dynamic input tool

[gianip]

Hi Alteryx community

What I want to do:

I have a workflow which is identifying values which then have to be stored to an Oracle db table using a stored procedure. So I have to call the stored procedure 1 or x times depending on how many records have to be added to the db.

I tried to use the dynamic input tool to connect to the db --> schema --> view. This works

Here my 1st question:

The view on the db that I have access to is not showing all fields that I am calling on the stored procedure.
is this a problem? Do I have to have a view which is showing the same fields?

[danilang]

Hi @gianip 

in order to call your final stored procedure, you need to be able to pass values for all the required fields, either by reading them from somewhere or generating them in the workflow itself.   If the view that you're reading form doesn't return all the fields that you need to call the SP, you'll need to generate the missing values or find another source. 

Another point to note.  The SP that you call has to return a record set or alteryx will throw the "No columns returned" error.  the final statement in the stored procedure has to be some kind of select statement.

Dan 

[gianip]

Hi Dan

Thanks for quick answer.

I'm generating / collecting the values used as parameters on the stored procedure in the workflow.

But then I want call the stored procedure for all the records, so in a loop, but I'm struggling with that. I tried by using the dynamic input tool.

I connect to the DB with OleDB and then I have to select a view (I only have access to one view on that schema which the stored procedures are stored, but this view doesn't contain same fields).

I added the stored procedure as PRE-SQL statement and then I select SQL Update stored procedure but there it doesn't show any parameters.
What am I doing wrong?

[danilang]

Hi @gianip 

Since you're using Pre SQL to call the SP, the only way to change it is to use a batch macro .  Pass the list of pre SQL statements into the control parameter of the batch macro.  Inside the batch macro connect the control parameter to the Dynamic input and set the Action tool to update the pre SQL statement on each iteration.

Check out the Macro Interactive Lessons to learn how to create macros and configure the interface tools 

Dan

[gianip]

Hi Dan

Again thanks for helping me on this. Very appreciated. Maybe I explained it not good enough. I think the one with building the macro should be clear.

But i'm struggling already when calling the stored procedure. Let's take a step back:

Let's say I only have one record with 3 values / columns like coming from the workflow:

value_1	value_2	value_3
1111	2222	3333

Now, i have to call the Oracle stored procedure which is called:
IFC_ALTERYX.IFC_ALTERYX_PG.SET_COMPONENT_PARAMETER('value_1','value_2','value_3')

I have to call it by using my 3 values from the workflow as parameters in the stored procedure. Which tool do I have to use and how do I have to configure?