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

[jpspears]

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.

[hflinn]

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.

[r4upadhye]

Yes

[kirby900]

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

[timewaste]

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. :P

[HenrietteH]

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: 

[PraphulSamavedam]

Do we have this feature in In-DataBase connections mostly in Write-inDB,either previous or post writing to the database? 
Eagerly waiting for reply as we require such a functionality on a regular basis.