Calling stored procedures with UDTs as parameters

Question

I wonder if it is possible to call an Oracle stored procedure that has both in and out UDT (user-defined type) parameters?

Here's the example I've been working on:
I have 5 structs corresponding to 5 UDTs in my Oracle database:

struct ASO_BOOKABLE {
    aso_id: String,
    forg_types: FORGTYPE,
}

struct FORGTYPE {
    values: Vec<i8>,
}

struct BOOKABLETYPE {
    bookables: BOOKABLETABTYPE,
    STATUS: String, 
}

struct BOOKABLETABTYPE {
    values: Vec<BOOKABLE>,
}

struct BOOKABLE {
    ifi_id: String,
    head_id: String,
    forgtet_id: String,
}

As you can see, there is a lot of nestling of UDTs and even nested tables. I want to call my stored procedure that takes an ASO_BOOKABLE object as IN parameter and has an OUT parameter of BOOKABLETYPE.

My hunch is that I should use something like this:

let rows = connection.query_as(
    "CALL myStoredProcedure($1, NULL)",
    &[&ASO_BOOKABLE as &(dyn ToSql)],
)?;

I'm not sure if this is the right way and I also have a bit of a difficult time writing the FromSql and ToSql trait implementations for my structs. Can someone point me in the right direction please?

Answer 1

Here is example code.

use oracle::sql_type::{Collection, FromSql, Object, OracleType};
use oracle::{Connection, Result, SqlValue};

// Implement to_oracle_object functions for IN parameters.
// The ToSql trait is available but it is complex for Object types.

impl ASO_BOOKABLE {
    fn to_oracle_object(&self, conn: &Connection) -> Result<Object> {
        // create a new object.
        let obj_type = conn.object_type("ASO_BOOKABLE")?;
        let mut obj = obj_type.new_object()?;
        // set Oracle object members.
        obj.set("ASO_ID", &self.aso_id)?;
        obj.set("FORG_TYPES", &self.forg_types.to_oracle_object(conn)?)?;
        Ok(obj)
    }
}

impl FORGTYPE {
    fn to_oracle_object(&self, conn: &Connection) -> Result<Collection> {
        // Create a new collection
        let coll_type = conn.object_type("FORGTYPE")?;
        let mut coll = coll_type.new_collection()?;
        // set collection elements
        for i in &self.values {
            coll.push(i)?;
        }
        Ok(coll)
    }
}

// Implement FromSql for OUT parameters

impl FromSql for BOOKABLETYPE {
    fn from_sql(val: &SqlValue) -> Result<BOOKABLETYPE> {
        let obj = val.get::<Object>()?;
        Ok(BOOKABLETYPE {
            bookables: obj.get("BOOKABLES")?,
            STATUS: obj.get("STATUS")?,
        })
    }
}

impl FromSql for BOOKABLETABTYPE {
    fn from_sql(val: &SqlValue) -> Result<BOOKABLETABTYPE> {
        let coll = val.get::<Collection>()?;
        Ok(BOOKABLETABTYPE {
            values: coll.values().collect::<Result<Vec<_>>>()?,
        })
    }
}

impl FromSql for BOOKABLE {
    fn from_sql(val: &SqlValue) -> Result<BOOKABLE> {
        let obj = val.get::<Object>()?;
        Ok(BOOKABLE {
            ifi_id: obj.get("IFI_ID")?,
            head_id: obj.get("HEAD_ID")?,
            forgtet_id: obj.get("FORGTET_ID")?,
        })
    }
}

// Call myStoredProcedure
fn call_my_stored_procedure(conn: Connection, aso_bookable: &ASO_BOOKABLE) -> Result<BOOKABLETYPE> {
    // Create an Object bound to the first parameter.
    let aso_bookable_obj = aso_bookable.to_oracle_object(&conn)?;
    // Create an OracleType to tell the type of the second parameter.
    // NULL is set to the parameter before the statement is executed.
    let bookabletype_oracle_type = OracleType::Object(conn.object_type("BOOKABLETYPE")?);
    // Execute
    let stmt = conn.execute(
        "BEGIN myStoredProcedure(:1, :2); END;",
        &[&aso_bookable_obj, &bookabletype_oracle_type],
    )?;
    // Get the OUT parameter
    Ok(stmt.bind_value(2)?)
}