For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
Hello All,
Does anybody have any idea how to do this? I am using Apex version 5 with Oracle 11.2.
Thanks.
Stored procedures are implemented starting with ROracle 1.3.1. This example does the following:
1. Creates a PL/SQL Procedure with one IN/OUT Parameter.
2. Executes the procedure.
3. Drops the procedure.
# Load the ROracle Library.
library(ROracle)
# Establish the connection with Oracle Database.
drv <- dbDriver("Oracle")
con <- dbConnect(drv, "username", "password")
# Define the data frame/attributes for capturing the results.
temp_output <-data.frame(emp_no = as.numeric(123), emp_name = as.character(NA), stringsAsFactors = FALSE)
attr(temp_output$emp_no, "ora.parameter_name") <- "emp_no";
attr(temp_output$emp_no, "ora.parameter_mode") <- "IN";
attr(temp_output$emp_name, "ora.parameter_name") <- "emp_name";
attr(temp_output$emp_name, "ora.parameter_mode") <- "OUT";
# Display the data frame.
temp_output
# Create a simple IN/OUT pl/sql procedure test_procedure1.
#INTEGER IN, VARCHAR2 OUT
dbGetQuery(con,"CREATE PROCEDURE test_procedure1
(emp_no IN INTEGER, emp_name OUT VARCHAR2)
IS
BEGIN
SELECT first_name INTO emp_name FROM employees WHERE employee_id=emp_no;
END;");
# Exeucte the procedure.
oracleProc(con,'BEGIN test_procedure1(:emp_no,:emp_name);END;',temp_output)
# Drop the procedure test_procedure1.
dbGetQuery(con,"DROP PROCEDURE test_procedure1")
# Disconnect
dbDisconnect(con)
dbUnloadDriver(drv)
Sherry
Hi @"Sherry Lamonica-Oracle",
thanks for the detailed instructions. I noticed just one point: the output parameter is not transfered to temp_output, instead I called
res <- oracleProc(con,'BEGIN test_procedure1(:emp_no,:emp_name);END;',temp_output)
and the the dataframe "res" containes the output.
Yours, user8632123.