Question - How to get out values from a stored procedure?

[Skada13]

oracle = 0.6.3
rust = 1.84.0

Hello Kubo!

I faced a problem with executing a stored oracle procedure that pushes customers payments to our billing. The procedure is:

BEGIN
    EX_PAYMENTS_PKG.EX_PAYMENTS_CHARGE(
        vch_VC_TO_BANK          => :1,
        vch_VC_TO_ACCOUNT       => :2,
        num_N_SUM               => :3,
        vch_VC_TRANSACTION_ID   => :4,
        num_N_FORWHO_ACCOUNT_ID => :5,
        dt_D_TAKING             => :6,
        num_N_DOC_ID            => :7,  -- OUT PARAMETER (NUMBER)
        dt_D_LOAD               => :8   -- OUT PARAMETER (DATE)
        );
    COMMIT;
END;

This procedure has two out parameters - num_N_DOC_ID and dt_D_LOAD that should be returned after executing the procedure. I've binded every value during the execution include the out parameters.

My code is:

let sql = """BEGIN
    EX_PAYMENTS_PKG.EX_PAYMENTS_CHARGE(
        vch_VC_TO_BANK          => :1,
        vch_VC_TO_ACCOUNT       => :2,
        num_N_SUM               => :3,
        vch_VC_TRANSACTION_ID   => :4,
        num_N_FORWHO_ACCOUNT_ID => :5,
        dt_D_TAKING             => :6,
        num_N_DOC_ID            => :7,  -- OUT PARAMETER (NUMBER)
        dt_D_LOAD               => :8   -- OUT PARAMETER (DATE)
        );
    COMMIT;
END;""";
let mut stmt = conn.statement(&sql).build()?;
stmt.execute(&[&bank.bank,
                          &bank.account,
                          &1,
                          &transaction_id,
                          &account_id,
                          date,
                          &oracle::sql_type::OracleType::Int64,  // 7th out parameter 
                          &oracle::sql_type::OracleType::Date   // 8th out parameter
])?;
let doc_id: String = stmt.bind_value(7)?;   // trying to get 7th out parameter
println!("{}", doc_id);

After running this code I get the error "NULL value found", and nothing happens in the billing.

Could you tell me how to execute procedures like this one correctly?

[kubo]

It looks the 7th out parameter is NULL. Could you use get it as Option<String>?

let doc_id: Option<String> = stmt.bind_value(7)?;   // trying to get 7th out parameter

[Skada13]

I've just tried that, it returned Null, but that's strange because the same procedure successfully returns the both out parameters when I use Python, here's an example of my old Python code:

SQL = """BEGIN
    EX_PAYMENTS_PKG.EX_PAYMENTS_CHARGE(
        vch_VC_TO_BANK          => :to_bank,
        vch_VC_TO_ACCOUNT       => :to_account,
        num_N_SUM               => :sum,
        vch_VC_TRANSACTION_ID   => :transaction_id,
        num_N_FORWHO_ACCOUNT_ID => :account_id,
        dt_D_TAKING             => :date,
        num_N_DOC_ID            => :7,  -- OUT PARAMETER (NUMBER)
        dt_D_LOAD               => :8   -- OUT PARAMETER (DATE)
        );
    COMMIT;
END;"""

# Binding necessary out parameters
num_N_DOC_ID = self.cursor.var(str)
dt_D_LOAD = self.cursor.var(datetime)

self.cursor.execute(SQL, to_bank,
                         to_account,
                         sum,
                         transaction_id,
                          account_id,
                          date,
                          num_N_DOC_ID=num_N_DOC_ID,
                          dt_D_LOAD=dt_D_LOAD)
doc_id = num_N_DOC_ID.getvalue() 
date_load = dt_D_LOAD.getvalue()

This code works fine, doc_id and date_load successfully return after executing the procedure.

[kubo]

Could you post minimal reproducible example?

I tested the following code and it works fine.

create or replace procedure test_issue_95(num out number) is
begin
 num := 10;
end;

use oracle::Result;
use oracle::Connection;

fn main() -> Result<()> {
    let conn = Connection::connect("username", "password", "database")?;
    let sql = "begin test_issue_95(num => :1); end;";
    let mut stmt = conn.statement(&sql).build()?;
    stmt.execute(&[&oracle::sql_type::OracleType::Int64])?;
    let num: Option<String> = stmt.bind_value(1)?;
    println!("num: {num:?}");
    Ok(())
}

$ cargo run
    Finished `dev` profile [unoptimized + debuginfo] target(s) in 0.04s
     Running `target/debug/test_issue_95`
num: Some("10")

[Skada13]

Kubo, thank you for your example, I've found out the problem in my code, solved!