community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Ideas

Share your Designer product ideas - we're listening!

Support for ORACLE PLSQL Blocks in "Output Data" tool for Pre/Post create SQL

Idea: Support PLSQL Blocks  DECLARE/BEGIN/END syntax would be very helpful in the Output data tool .

This should allow either running multi-step SQL statements or calling stored procedures.

 

Rationale: Sometimes you need to run extra code or stored procedures after the data has been processed. It is also be much easier sometimes to re-use legacy code than try to recreate as a complex Alteryx macro with a bunch of R code.

The tool already allows calling procedures in SQL Server, but not supporting this in ORACLE is a big challenge for us.

5 Comments
Meteoroid

I would add that this should be considered for all the In-Database tools and the Write Data In-DB tool as well.

 

This is a challenge for my organization as well. I use primarily In-Database tools in exclusively Oracle databases, and not being able to execute PL SQL or call stored procedures keeps Alteryx from being my go-to tool, because it means I can't run an entire process automatically. I now have to split my steps into "before" and "after" workflows, and have a user run code manually in the database in between workflows. So much for convenience and automation.

 

Many community users have noted the workaround to deconstruct the procedure code so Alteryx can use it, but from an enterprise standpoint, sometimes there are security or other reasons for using stored procedures, and deconstructing the code isn't a viable option.

Alteryx Partner

Yes

Atom
Amen. I spent much of my weekend working around the fact that Alteryx doesn't conveniently support invoking stored procs.
Asteroid

I second everyone above here. We do need this functionality at the least for taking backup of table dynamically on day to day basis with timestamp appended to the backup table name. Something like this as an example:

 

declare
l_tablename varchar2(30) := 'ABC_' || to_char(systimestamp,'MMDDYYYYHHMISS')
begin
execute immediate 'create table ' || l_tablename || ' (col1 integer)';
end;
/

 

P.S. Well, I guess we can still call a procedure. Never tried though. 😛

Alteryx
Alteryx

Hi All, 

 

As of 2018.3, you can now run stored procedures for Oracle in the pre/post sql sections of the input and output tools: 

 

3-12-2019 9-47-06 AM.png