NULLs as Statement Arguments

There are several, sometimes complementary, ways to pass nulls into and receive them from statements.

Empty Strings Are Nulls

This is idiosyncratic Oracle's way to treat empty strings as nulls.

Example

    let stmt = session.prepare("
        SELECT Nvl(:VAL,'nil') FROM dual
    ")?;
    if let Some(row) = stmt.query_single("")? {
        let val: Option<&str> = row.get(0)?;

        assert!(val.is_some());
        assert_eq!(val.unwrap(), "nil");
    }

When used as an OUT or an INOUT arguments a String or a Varchar does not even need to be wrapped into Option as its length by itself indicates whether Oracle sees it as null or returned its value as null.

Example

    let stmt = session.prepare("
    BEGIN
        IF :VAL IS NULL THEN
            :VAL := 'nil';
        ELSE
            :VAL := NULL;
        END IF;
    END;
    ")?;
    // allocate space for future output
    let mut val = String::with_capacity(4);

    stmt.execute(&mut val)?;
    assert!(!stmt.is_null("VAL")?);
    assert_eq!(val, "nil");

    stmt.execute(&mut val)?;
    assert!(stmt.is_null("VAL")?);
    assert_eq!(val, "");

Using Option to Represent Null

Option allows the maximum flexibility in representing input and output null values.

Example

    let stmt = session.prepare("
    BEGIN
        IF :VAL IS NULL THEN
            :VAL := 42;
        ELSE
            :VAL := NULL;
        END IF;
    END;
    ")?;
    let mut val : Option<i32> = None;

    stmt.execute(&mut val)?;
    assert_eq!(val, Some(42));

    stmt.execute(&mut val)?;
    assert!(val.is_none());

Unfortunately, there are a few cases, specifically with INOUT parameters, when an Option cannot represent arguments that are null values as inputs and concrete values on output. To support these Sibyl offers Nvl type.

Using Nvl to Represent Null

Nvl wraps a value that would provide a storage for an output value, but it binds it to the parameter placeholder as null.

Example

    let stmt = session.prepare("
    BEGIN
        IF :VAL IS NULL THEN
            :VAL := Utl_Raw.Cast_To_Raw('Hello, World!');
        END IF;
    END;
    ")?;
    let mut buf = [0; 16];
    let mut val = Nvl::new(buf.as_mut_slice());

    stmt.execute(&mut val)?;
    assert!(!stmt.is_null("VAL")?);
    assert!(val.as_ref().is_some());
    assert_eq!(
        val.as_ref().unwrap(),
        &[0x48u8, 0x65, 0x6C, 0x6C, 0x6F, 0x2C, 0x20, 0x57, 0x6F, 0x72, 0x6C, 0x64, 0x21, 0x00, 0x00, 0x00]
        // note the "trailing" initial zeroes are not overwritten by output --------------^^^^--^^^^--^^^^
    );
    let output_len = stmt.len_of("VAL")?;
    let output = &val.as_ref().unwrap()[0..output_len];
    assert_eq!(output, &[0x48u8, 0x65, 0x6C, 0x6C, 0x6F, 0x2C, 0x20, 0x57, 0x6F, 0x72, 0x6C, 0x64, 0x21]);

Similarly INOUT Interval must be wrapped in Nvl if the input value is null while output is expected to be an actual interval.