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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
New Data Science Blog

Check out the latest post: All Models Are Wrong

READ MORE
Announcement | We'll be doing maintenance between pm 4- 6pm MT, which may impact your experience. Thanks for your patience as we work on improving the community!

Alteryx Workflow to use ROracle to call stored procedure load data into Oracle tables

Highlighted

I have successfully created an Alteryx workflow to include a R Tool, to use ROracle to call an Oracle stored procedure to load data into an Oracle table.  Here is my workflow:

R_work_flow.png 

Here is the R script I wrote in the configuration of R Tool:


library(ROracle)

 

iris_data = read.Alteryx("#1", mode="data.frame")

drv <- dbDriver("Oracle")

con_diadev1 <- dbConnect(drv, "sa", "password", dbname = "LABS")

attr(iris_data$Sepal.Length, "ora.parameter_name") <- "sepal_length";
attr(iris_data$Sepal.Length, "ora.parameter_mode") <- "IN";

attr(iris_data$Sepal.Width, "ora.parameter_name") <- "sepal_width";
attr(iris_data$Sepal.Width, "ora.parameter_mode") <- "IN";

attr(iris_data$Petal.Length, "ora.parameter_name") <- "petal_length";
attr(iris_data$Petal.Length, "ora.parameter_mode") <- "IN";

attr(iris_data$Petal.Width, "ora.parameter_name") <- "petal_width";
attr(iris_data$Petal.Width, "ora.parameter_mode") <- "IN";

attr(iris_data$Species, "ora.parameter_name") <- "iris_specy_name";
attr(iris_data$Species, "ora.parameter_mode") <- "IN";

proc_statement <- "BEGIN LOAD_IRISE_DATA(:sepal_length, :sepal_width, :petal_length, :petal_width, :iris_specy_name);END;"

rs <- oracleProc(con_diadev1, proc_statement, iris_data)

dbCommit(con_diadev1)

dbDisconnect(con_diadev1)

 

 

Here is my stored procedure:

 

PROCEDURE LOAD_IRIS_DATA
(
i_sepal_length IN NUMBER,
i_sepal_width IN NUMBER,
i_petal_length IN NUMBER,
i_petal_width IN NUMBER,
i_iris_specy_name IN VARCHAR2
)
AS

l_specy_name VARCHAR2(32) := NULL;

BEGIN

IF i_iris_specy_name IS NOT NULL THEN
l_specy_name := UPPER(TRIM(i_iris_specy_name));

IF l_specy_name = 'NA' THEN

l_specy_name := NULL;

END IF;

END IF;

INSERT INTO TEMP_IRIS_LAB
(
TIL_IRIS_SAMPLE_ID,
TIL_SEPAL_LENGTH,
TIL_SEPAL_WIDTH,
TIL_PETAL_LENGTH,
TIL_PETAL_WIDTH,
TIL_IRIS_SPECY_NAME
)
VALUES
(
TIL_IRIS_SAMPLE_ID.NEXTVAL,
i_sepal_length,
i_sepal_width,
i_petal_length,
i_petal_width,
l_specy_name
);

COMMIT;

END LOAD_IRIS_DATA;

 

 

Through this solution, you can gain granular control on how to load data into Oracle table through the combination of R script and PL-SQL code in stored procedure. This granular control sometimes is very important if you don't like the data import out-of-box options provided by Output Data tool.  Hope this solution can help you!  Thanks.

 

Labels