Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here

Dynamic Input tool - Oracle Stored Procedures

Highlighted
8 - Asteroid

Hello Peers,

 

Is there a way to use Dynamic Input tool for updating parameters in stored procedures using Oracle connection? I haven't found an exact solution in our community yet. Please advise.

 

Alteryx Use Case.png

 

 

Highlighted
9 - Comet

Hi @timewaste

You can use the dynamic input to pass params to the stored proc which is a query.

If you want to update anything in the pre or post sql statement, then this can be done with a batch macro, by using a control parameter and action tool to update the pre/post sql.

Attached a screenshot below to get you started. Hope this helps.

 

Cheers,

Brindha

 

Capture.PNG

Highlighted
8 - Asteroid

 

@brindhan Thanks for the suggestion. But here's the issue I see. As you know, there are two parts to it. One of them is changing the variables in PreSQL procedure dynamically, then run it and second part is modify the where clause and run the select statement. 

Now the issue I face is shown in below screenshot. I am not sure how to change the first part. Please advise.

 

Alteryx Use Case.png

 

 

Highlighted
9 - Comet

Hi @timewaste

The value 306 will be the value passed in through the control parameter.

In the presql config- in replace specific string, retain only the variable you want to change(ie only 302, instead of "call procedure....")

This value then gets passed dynamically through the control parameter of the batch macro.

 

Cheers,

Brindha

 Capture2.PNG

Highlighted
8 - Asteroid

@brindhan Sure, I did try the same yet nothing happens. See below configuration for reference. Can you please help me figure this out? Can you send me an example workflow that succeeds on your end? Thanks!

Alteryx Use Case.png


Highlighted
9 - Comet

Hi @timewaste

Apologies for the delay, here's your workflow. Since I do not have oracle, I used a different syntax, but the concept is the same and you should be able to configure it to your requirement.

Hope this helps. Cheers!

B

 

Highlighted
9 - Comet

Hi @timewaste

Do let us know if the workflow helps.

And if yes, please do mark it as Solution for everyone's benefit. 

 

Cheers!

 

 

 

Highlighted
8 - Asteroid

@brindhan Thank you very much for the workflow yet it doesn't work for me. I am guessing it has to do with Oracle in particular. Let me attach my workflow for your reference in case of wrong configuration.

 

Thanks.

Labels