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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

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

Highlighted
Meteoroid

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