ROracle, oracleProc, return value or output parameter handling

BG4GRAPH

Hi,

@Mautro Pagano was informed in his post a year ago about the calling of procedures not being implemented in ROracle 1.2.2. It seems that ROracle 1.3.1 has some more functionality; I was at least able to call anonymous blocks.

However, I would need to pass return values or output parameters. Is this implemented? Could you give syntax examples?
I would be much obliged!

Yours, user8632123.

Sherry Lamonica-Oracle

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)

# Display the data frame.

temp_output

# Drop the procedure test_procedure1.

dbGetQuery(con,"DROP PROCEDURE test_procedure1")

# Disconnect

dbDisconnect(con)

dbUnloadDriver(drv)

Sherry

Sherry Lamonica-Oracle

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)

# Display the data frame.

temp_output

# Drop the procedure test_procedure1.

dbGetQuery(con,"DROP PROCEDURE test_procedure1")

# Disconnect

dbDisconnect(con)

dbUnloadDriver(drv)

Sherry

BG4GRAPH

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.