Forum Stats

  • 3,760,221 Users
  • 2,251,664 Discussions
  • 7,871,026 Comments

Discussions

ROracle, oracleProc, return value or output parameter handling

BG4GRAPH
BG4GRAPH Member Posts: 125 Bronze Badge
edited Jun 29, 2017 2:42AM in R Technologies

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.

Best Answer

  • Sherry Lamonica-Oracle
    Sherry Lamonica-Oracle Posts: 437 Employee
    edited Jun 27, 2017 12:43PM Accepted Answer

    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

Answers

  • Sherry Lamonica-Oracle
    Sherry Lamonica-Oracle Posts: 437 Employee
    edited Jun 27, 2017 12:43PM Accepted Answer

    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
    BG4GRAPH Member Posts: 125 Bronze Badge
    edited Jun 29, 2017 2:42AM

    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.

This discussion has been closed.