CODEWITHSUNDEEP
c#oracle-database
Flin
Flin

Reputation: 47

C# calling Oracle Function - Error: "Invalid operation on null data"

I'm getting an error "Invalid operation on null data" when my C# code is calling Oracle Function. This happens only if no data is found. If data is found and function returns a value, then everything works ok. I'm a little confused, as - to my understanding at least - function should return 100 if no data found (see function exception).

Oracle Function:

create or replace FUNCTION F_SCO_DPD
(
    p_tip IN NUMBER,
    p_dav IN VARCHAR2
)
RETURN NUMBER 
IS
    sco NUMBER;
BEGIN
    SELECT max(score) keep(dense_rank first order by vrednost)
        INTO sco
        FROM sco_sif_score
        WHERE sif_kat = 11
        AND tip_pod = p_tip
        AND vrednost >= (SELECT a.dpd
                         FROM sco_dpd a                                      
                         WHERE a.par_davcna = p_dav);
    RETURN sco;
EXCEPTION
        WHEN NO_DATA_FOUND 
        THEN 
             RETURN 100;   
END F_SCO_DPD;

C# Code:

using (OracleCommand cmd = new OracleCommand())
{
    cmd.Connection = conn;
    cmd.CommandText = "F_SCO_DPD";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new OracleParameter("p_tip", Podjetje.TipSub));
    cmd.Parameters.Add(new OracleParameter("p_dav", Podjetje.Davcna));
    cmd.Parameters.Add(new OracleParameter("sco", OracleDbType.Decimal, ParameterDirection.ReturnValue));
    cmd.BindByName = true;

    cmd.ExecuteScalar();

    Score.ScoDpd = (int)(OracleDecimal)cmd.Parameters["sco"].Value;                                    
}

Upvotes: 1

Views: 3104

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270001

You are running an aggregation function. max(score) keep (dense_rank first order by vrednost) is as much an aggregation function as max(score).

That means that your query is an aggregation query with no GROUP BY. All such queries return exactly 1 row. If no rows match the WHERE clause, then the value is NULL.

So, the exception is never triggered. Instead, check if the returned value is NULL.

The resulting code is:

create or replace FUNCTION F_SCO_DPD
(
    p_tip IN NUMBER,
    p_dav IN VARCHAR2
)
RETURN NUMBER 
IS
    v_sco NUMBER;
BEGIN
    SELECT max(score) keep (dense_rank first order by vrednost)
    INTO v_sco
    FROM sco_sif_score
    WHERE sif_kat = 11 AND
          tip_pod = p_tip AND
          vrednost >= (SELECT a.dpd
                       FROM sco_dpd a                                      
                       WHERE a.par_davcna = p_dav
                      );

    RETURN COALESCE(v_sco, 100);
END F_SCO_DPD;

Upvotes: 5

Related Questions